Re: Suboptimal execution plan for simple query

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