Indexing on function expression

Without index:

explain analyze 
select * 
from withdrawn_spent ws 
join account_category ac using(account_category_id) 
where md5(ac.account_category_full_description) = '845653b0977ca959df6a206b349f88be'

Nested Loop  (cost=9.34..847.37 rows=280 width=706) (actual time=0.655..4.406 rows=1298 loops=1)
  ->  Seq Scan on account_category ac  (cost=0.00..14.54 rows=2 width=214) (actual time=0.037..0.933 rows=1 loops=1)
        Filter: (md5((account_category_full_description)::text) = '845653b0977ca959df6a206b349f88be'::text)
  ->  Bitmap Heap Scan on withdrawn_spent ws  (cost=9.34..414.66 rows=140 width=531) (actual time=0.608..1.756 rows=1298 loops=1)
        Recheck Cond: ((ws.account_category_id)::text = (ac.account_category_id)::text)
        ->  Bitmap Index Scan on fki_withdrawn_spent__account_category_id  (cost=0.00..9.31 rows=140 width=0) (actual time=0.491..0.491 rows=1298 loops=1)
              Index Cond: ((ws.account_category_id)::text = (ac.account_category_id)::text)
Total runtime: 4.663 ms

Create Index:

create index ix_account_category__description on account_category (md5(account_category_full_description))

Execute this again:

explain analyze 
select * 
from withdrawn_spent ws 
join account_category ac using(account_category_id) 
where md5(ac.account_category_full_description) = '845653b0977ca959df6a206b349f88be'

Nested Loop  (cost=9.35..845.11 rows=280 width=706) (actual time=0.629..3.330 rows=1298 loops=1)
  ->  Index Scan using ix_account_category__description on account_category ac  (cost=0.00..12.29 rows=2 width=214) (actual time=0.013..0.016 rows=1 loops=1)
        Index Cond: (md5((account_category_full_description)::text) = '845653b0977ca959df6a206b349f88be'::text)
  ->  Bitmap Heap Scan on withdrawn_spent ws  (cost=9.34..414.66 rows=140 width=531) (actual time=0.605..1.636 rows=1298 loops=1)
        Recheck Cond: ((ws.account_category_id)::text = (ac.account_category_id)::text)
        ->  Bitmap Index Scan on fki_withdrawn_spent__account_category_id  (cost=0.00..9.31 rows=140 width=0) (actual time=0.488..0.488 rows=1298 loops=1)
              Index Cond: ((ws.account_category_id)::text = (ac.account_category_id)::text)
Total runtime: 3.585 ms

3.585 ms vs 4.663 ms

Index Scan vs Sequential Scan

Function-based index FTW! :-)

Postgresql's FIND_IN_SET

FIND_IN_SET is a nifty Mysql function for finding the value's index from a given set, where the given set is a dynamic set of values.

We could use this function when we want to sort the list based on a given set of filter

Test data:

create table x_n as select x.y from (values(9),(13),(15),(30),(27),(63)) as x(y)

Test query:

select * from x_n 
where y in (15,9,13,27)
order by find_in_set(y, '15,9,13,27') 

Output:

15
9
13
27

Here's the find_in_set function for Postgres:

create function find_in_set(n int, s text) returns bigint as
$$
select z.row_number
from
(
    select row_number() over(), y.x 
    from (select unnest(('{' || $2 || '}')::int[]) as x) as y
) as z
where z.x = $1
$$ language sql;

Here's the function for pre-8.4:

create function find_in_set(n int, s text) returns bigint as
$$
declare a int[];
begin

    a = ('{' || $2 || '}')::int[];

    return (
        select i 
        from 
        generate_series(1, array_upper(a, 1)) as gs(i)
        where a[i] = $1 );

end;
$$ language 'plpgsql';

Postgres array

select * from company where company_code in ('103','001')

select * from company where company_code = any(array['103','001'])

select * from company where company_code = any( regexp_split_to_array('103,001', ',') )

-- very neat, no need to to split string to array, can cast string directly to array, as long there's curly bracket inside
select * from company where company_code = any('{103,001}'::text[])

Nested function/subroutine in C#

This is how we simulate nested function in C# :

Debugging Form_Load while inside VS IDE is impossible

private void Form1_Load(object sender, EventArgs e)
{
 int n = 0;
 int i = 7 / n; // this fail silently while inside Visual Studio
}

private void button1_Click(object sender, EventArgs e)
{
 int n = 0;
 int i = 7 / n;
}

Unboxing, Casting

int totalPage = (int)(long)cmdCount.ExecuteScalar();

That is not double casting. The first(read from right to left) cast, is actually unboxing, we unbox long from object, then cast the long type to int type

Unboxing shares the same syntax of casting

Proper indexing for (hidden) tuples

If you have a query like this:

select * from zd
where z in
(select z from zh where y = 'A' and y = zd.y)

It is recommended to index on both y,z fields of both zh and zd tables

create index x_zh on zh(y,z)
create index x_zd on zd(y,z)