From: | "Alexander Staubo" <alex(at)purefiction(dot)net> |
---|---|
To: | "Postgresql General" <pgsql-general(at)postgresql(dot)org> |
Subject: | Selecting max(pk) is slow on empty set |
Date: | 2008-01-22 11:19:06 |
Message-ID: | 88daf38c0801220319q68dd6c50y6555378ee26932e9@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
This is on a fresh pg_restore copy that I have additionally vacuumed
and analyzed. These queries, on a table containing 2.8 million rows,
are very fast:
# select count(*) from user_messages where user_id = 13604;
count
-------
0
(1 row)
Time: 0.604 ms
# select * from user_messages where user_id = 13604;
id | user_id | sender_id | sent_at | dismissed_at | message
----+---------+-----------+---------+--------------+---------
(0 rows)
Time: 0.678 ms
But doing a max() on this empty set takes a long time to run:
# explain analyze select max(id) from user_messages where user_id = 13604;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=633.19..633.20 rows=1 width=0) (actual
time=339160.704..339160.704 rows=1 loops=1)
InitPlan
-> Limit (cost=0.00..633.19 rows=1 width=4) (actual
time=339160.700..339160.700 rows=0 loops=1)
-> Index Scan Backward using user_messages_pkey on
user_messages (cost=0.00..633188.12 rows=1000 width=4) (actual
time=339160.697..339160 Filter: ((id IS NOT NULL) AND
(user_id = 13604))
Total runtime: 339160.770 ms
(6 rows)
Note that it's using the correct index -- user_messages_pkey is on the
id attribute. (Why rows=1000 here?)
PostgreSQL 8.2.5 on Linux and OS X Leopard.
Alexander.
From | Date | Subject | |
---|---|---|---|
Next Message | cinu | 2008-01-22 11:52:08 | Errors with run_build.pl - 8.3RC2 |
Previous Message | Pavel Stehule | 2008-01-22 08:38:29 | Re: Stored procedures when and how: was: Sun acquires MySQL |