Re: Adding an "and is not null" on an indexed field slows the query down immensely.

2011/1/15 pasman pasmański <pasman.p@gmail.com>:
> Try :
> order by index_delta+1 desc
>

I have attached the explain analyze for that below why does this
return instantly?




Limit  (cost=29910.05..29910.07 rows=10 width=1880) (actual
time=42.563..42.563 rows=0 loops=1)
   ->  Sort  (cost=29910.05..29916.65 rows=2642 width=1880) (actual
time=42.558..42.558 rows=0 loops=1)
         Sort Key: ((consolidated_urls.index_delta + 1::double precision))
         Sort Method:  quicksort  Memory: 17kB
         ->  Nested Loop  (cost=105.30..29852.95 rows=2642 width=1880)
(actual time=10.428..10.428 rows=0 loops=1)
               ->  Bitmap Heap Scan on topical_urls
(cost=105.30..7494.33 rows=2642 width=4) (actual time=10.424..10.424
rows=0 loops=1)
                     Recheck Cond: (domain_id = 157)
                     Filter: (NOT hidden)
                     ->  Bitmap Index Scan on
index_topical_urls_on_domain_id_and_consolidated_url_id
(cost=0.00..104.64 rows=2643 width=0) (actual time=10.419..10.419
rows=0 loops=1)
                           Index Cond: (domain_id = 157)
               ->  Index Scan using consolidated_urls_pkey on
consolidated_urls  (cost=0.00..8.45 rows=1 width=1880) (never
executed)
                     Index Cond: (consolidated_urls.id =
topical_urls.consolidated_url_id)
                     Filter: (consolidated_urls.index_delta IS NOT NULL)
 Total runtime: 42.932 ms
(14 rows)