Search Postgresql Archives

Query slower if i add an additional order parameter

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

 



Hi,

postgresql 8.4 (tuned, analyzed, and so on)

we had trouble with one query executing too slow. After checking out some alternatives we encountered that dropping a rather useless second parameter on "order by" the execution time dropped dramatically.

This is our original query with 2 order parameters:

select * from Forum
where id=33591
order by datum desc, id
limit 11;

This is the "explain analyze" output:

QUERY PLAN
----------------------------------
 Limit
 (cost=23478.37..23478.39 rows=11 width=229)
 (actual time=31.830..31.834 rows=11 loops=1)
   ->  Sort
       (cost=23478.37..23516.27 rows=15163 width=229)
       (actual time=31.828..31.830 rows=11 loops=1)
         Sort Key: datum, id
         Sort Method:  top-N heapsort  Memory: 29kB
         ->  Bitmap Heap Scan on forum
             (cost=235.88..23140.27 rows=15163 width=229)
             (actual time=5.444..23.409 rows=15173 loops=1)
               Recheck Cond: (id = 33591)
               ->  Bitmap Index Scan on ix_id_datum
                   (cost=0.00..232.08 rows=15163 width=0)
                   (actual time=3.290..3.290 rows=15173 loops=1)
                     Index Cond: (id = 33591)
 Total runtime: 31.887 ms

The second parameter is not really needed. It is just given to be sure we get all records in a predefined order. But it happened only once in a few years seeing the same timestamp in two records. So we tried to drop this parameter (never thought it would change anything)

This is our query with only 1 order parameter

select * from Forum
where id=33591
order by datum desc
limit 11;

This is the "explain analyze" output:
QUERY PLAN
-------------------------------------
 Limit
 (cost=0.00..20.66 rows=11 width=229)
 (actual time=0.039..0.065 rows=11 loops=1)
   ->  Index Scan Backward using ix_forum_id_datum on forum
       (cost=0.00..28482.92 rows=15163 width=229)
       (actual time=0.038..0.061 rows=11 loops=1)
         Index Cond: (id = 33591)
 Total runtime: 0.098 ms
(4 rows)


we did an "ANALYZE" before and executed this query many times to be sure that disk cache is in place.

the second query is 300 times faster!!

So I do not have a question, because for me its fine, running this query without ordering by "id". I just would like to help making postgresql even better. I think postgresql could be smart enough to take a closer look at the query and optimize its execution plan. If this "problem" is already known i apologize, but I searched the mailing list and didn't found any mails regarding this topic. Hard to believe i am the first hit by this.

kind regards
Janning

PS: Postgresql is so great! Thank you all!

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux