> On Wednesday 08 June 2011 17:39, Claudio Freire wrote: >> Of course optimally executing a plan with limit is a lot different >> than one without. > > I imagined that limit just cuts out a slice of the query results. > If it can find 80000 rows in 0.5 seconds then I would have thought that > returning just the first 100 of them should be just as easy. But that's exactly the problem with LIMIT clause. The planner considers two choices - index scan with this estimate Index Scan using tdiag_pkey on tdiag (cost=0.00..19114765.76 rows=1141019 width=114) and bitmap index scan with this estimate Bitmap Heap Scan on tdiag (cost=25763.48..638085.13 rows=1141019 width=114) and says - hey, the index scan has much lower starting cost, and I'm using limit so it's much better! Let's use index scan. But then it finds out it needs to scan most of the table and that ruins the performance. Have you tried to create a composite index on those two columns? Not sure if that helps but I'd try that. Tomas -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
