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

2011/1/15 pasman pasmański <pasman(dot)p(at)gmail(dot)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)