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

I think this is a planner's bug. Can you send these explains to pgsql-bugs ?

On 1/15/11, Tim Uckun <timuckun(at)gmail(dot)com> wrote:
> 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)
>

--
Sent from my mobile device

------------
pasman