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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
