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
