Hi! In preparation for my upcoming upgrade to PostgreSQL 8.3.5, I have taken the opportunity to try this scenario on a test machine with the latest PostgreSQL version. Unfortunately the result remains the same, though this database has been just reloaded from a dump and vacuum analyzed. select version() outputs "PostgreSQL 8.3.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21)".
Kind regards Markus Just for reference: > Now I've got this simple query > > SELECT image_id > FROM image_relation > WHERE entity_id = 69560::integer > ORDER BY last_updated DESC > LIMIT 1; > > which currently runs for something around 600ms. Here's the explain > analyze output: > > "Limit (cost=0.00..144.78 rows=1 width=12) (actual > time=599.745..599.747 rows=1 loops=1)" " -> Index Scan Backward > using idx_image_relation_last_updated on image_relation > (cost=0.00..39525.70 rows=273 width=12) (actual time=599.741..599.741 > rows=1 loops=1)" " Filter: (entity_id = 69560)" "Total > runtime: 599.825 ms" > SELECT image_id > FROM image_relation > WHERE entity_id = 69560 > AND entity_id = entity_id > ORDER BY last_updated DESC > LIMIT 1 > > "Limit (cost=881.82..881.82 rows=1 width=12) (actual > time=0.097..0.099 rows=1 loops=1)" " -> Sort (cost=881.82..881.82 > rows=1 width=12) (actual time=0.094..0.094 rows=1 loops=1)" " > Sort Key: last_updated" " -> Index Scan using > idx_image_relation_entity_id on image_relation (cost=0.00..881.81 > rows=1 width=12) (actual time=0.063..0.075 rows=3 loops=1)" " > Index Cond: (entity_id = 69560)" " Filter: (entity_id = > entity_id)" "Total runtime: 0.128 ms" Computec Media AG Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818) Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Rainer Rosenbusch Vorsitzender des Aufsichtsrates: Jürg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
