Re: NULLS LAST performance

On Wed, Feb 23, 2011 at 1:27 PM, Mathieu De Zutter <mathieu(at)dezutter(dot)org> wrote:
> Hi all,
> Running PostgreSQL 8.4.7 (backport package from Debian Lenny).
> I have some queries that are based on views, and an engine adds a few
> clauses (like NULLS LAST). One of these queries has a performance problem.
> The simplified form is this:
> shs=# explain analyze select * from performance e JOIN part v ON
> v.performance_id = e.id order by e.creation_date desc limit 10;
>
>  QUERY PLAN
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  Limit  (cost=0.00..4.25 rows=10 width=312) (actual time=0.078..0.147
> rows=10 loops=1)
>    ->  Nested Loop  (cost=0.00..62180.28 rows=146294 width=312) (actual
> time=0.078..0.145 rows=10 loops=1)
>          ->  Index Scan Backward using performance_create_idx on performance
> e  (cost=0.00..12049.21 rows=145379 width=247) (actual time=0.051..0.087
> rows=10 loops=1)
>          ->  Index Scan using part_performance_idx on part v
>  (cost=0.00..0.33 rows=1 width=65) (actual time=0.005..0.005 rows=1
> loops=10)
>                Index Cond: (v.performance_id = e.id)
>  Total runtime: 0.205 ms
> creation_date is declared as NOT NULL, and since it's a inner join,
> creation_date can never be null in this query. I'd think that if I add NULLS
> LAST, it wouldn't have any effect.
> However, the query with NULLS LAST (as generated by the engine):
> shs=# explain analyze select * from performance e JOIN part v ON
> v.performance_id = e.id order by e.creation_date desc nulls last limit 10;

hm, creation date being NOT NULL is not applied in that sense. maybe
it could be logically (I'm not sure). Your best bet is to probably to
get the engine to knock off the nulls last stuff, but if you can't,
you can always do this:

create index performance_creation_date_desc_idx on
performance(creation_date desc nulls last);

which will index optimize your sql. Interesting that 'null last'
fools disallows index usage even when the index was created with
nullls last as the default.

merlin