Re: Suboptimal execution plan for simple query

On Wed, Nov 12, 2008 at 04:15:23PM +0100, Markus Wollny wrote:
> 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"

The database would appear to be thinking that it's better off running
through time backwards to find the entry than searching for the entry
directly. This is normally because each entry_id has several rows and
running through time would end up doing less work (especially as it
wouldn't need to sort the results afterwards).

You may have some luck with increasing the statistics target on the
entry_id and last_updated columns and re-ANALYZING the table. Then
again, the fact that it thinks it's only going to get a single row
back when it searches for the entity_id suggests that it's all a bit
confused!

Sam