NULLS LAST performance

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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;
                                                                             QUERY PLAN                                                                             
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=25773.76..25773.79 rows=10 width=312) (actual time=492.959..492.963 rows=10 loops=1)
   ->  Sort  (cost=25773.76..26139.50 rows=146294 width=312) (actual time=492.958..492.962 rows=10 loops=1)
         Sort Key: e.creation_date
         Sort Method:  top-N heapsort  Memory: 27kB
         ->  Merge Join  (cost=1.27..22612.40 rows=146294 width=312) (actual time=0.064..367.160 rows=146294 loops=1)
               Merge Cond: (e.id = v.performance_id)
               ->  Index Scan using performance_pkey on performance e  (cost=0.00..11989.20 rows=145379 width=247) (actual time=0.035..160.838 rows=145379 loops=1)
               ->  Index Scan using part_performance_idx on part v  (cost=0.00..8432.35 rows=146294 width=65) (actual time=0.025..91.084 rows=146294 loops=1)
 Total runtime: 493.062 ms

Both tables have around 150k rows as you can read from the last plan.

Table performance:

                                      Table "public.performance"
     Column      |           Type           |                        Modifiers                         
-----------------+--------------------------+----------------------------------------------------------
 created_by      | integer                  | not null
 creation_date   | timestamp with time zone | not null
 comments        | text                     | 
 owned_by        | integer                  | not null
 id              | integer                  | not null default nextval('performance_id_seq'::regclass)
 title           | text                     | 
 title_          | text                     | 
 performer_id    | integer                  | 
 first_medium_id | integer                  | 
 vperf_id        | integer                  | 
 perf_date       | partial_date             | 
 bonustrack      | boolean                  | not null default false
 type_id         | integer                  | not null
 instrumental    | boolean                  | not null default false
 init_rev_level  | smallint                 | not null default 1
 curr_rev_level  | smallint                 | not null default 1
 revision_date   | timestamp with time zone | 
 revised_by      | integer                  | 
 object_type     | text                     | not null default 'performance'::text
 editor_note     | text                     | 
 active          | boolean                  | not null default true
Indexes:
    "performance_pkey" PRIMARY KEY, btree (id)
    "performance_create_idx" btree (creation_date)
    "performance_medium_idx" btree (first_medium_id)
    "performance_own_idx" btree (owned_by)
    "performance_performer_idx" btree (performer_id)

Table part:

                                      Table "public.part"
     Column     |           Type           |                     Modifiers                     
----------------+--------------------------+---------------------------------------------------
 created_by     | integer                  | not null
 creation_date  | timestamp with time zone | 
 comments       | text                     | 
 owned_by       | integer                  | not null
 id             | integer                  | not null default nextval('part_id_seq'::regclass)
 work_id        | integer                  | not null
 performance_id | integer                  | not null
Indexes:
    "part_pkey" PRIMARY KEY, btree (id)
    "part_own_idx" btree (owned_by)
    "part_performance_idx" btree (performance_id)
    "part_work_idx" btree (work_id)

Please advise!

Thanks.

Kind regards,

Mathieu

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux