Andreas Joseph Krogh-2 wrote > Hi all. Running version: on=> select version(); > version > > ------------------------------------------------------------------------------------------------------------ > PostgreSQL 9.3.2 on x86_64-unknown-linux-gnu, compiled by gcc > (Ubuntu/Linaro > 4.6.3-1ubuntu5) 4.6.3, 64-bit
9.3.2 is not release-worthy.... > Bad: > Index Scan Backward using origo_email_delivery_received_idx on > origo_email_delivery del (cost=0.42..1102717.48 rows=354038 width=98) > (actual time=0.017..309196.670 rows=354296 loops=1) > >>>Add 4 new records<< > > Good (-ish): > Index Scan Backward using origo_email_delivery_received_idx on > origo_email_delivery del (cost=0.42..1102717.48 rows=354038 width=98) > (actual time=0.019..2431.773 rows=354300 loops=1) The plans appear to be basically identical - and the queries/data as well aside from the addition of 4 more unmatched records. The difference between the two is likely attributable to system load variations combined with the effect of caching after running the query the first (slow) time. Doing OFFSET/LIMIT pagination can be problematic so I'd be curious what would happen if you got rid of it. In this specific case the result set is only 75 with 101 allowed anyway. The left joins seem to be marginal so I'd toss those out and optimize the inner joins and, more likely, the correlated subqueries in the select list. You need to avoid nested looping over 300,000+ records somehow - though I'm not going to be that helpful in the actual how part... Note that in the inner-most loop the actual time for the cached data is half of the non-cached data. While both are quite small (0.002/0.004) the 300,000+ loops do add up. The same likely applies to the other planning nodes but I didn't dig that deep. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Query-performing-very-bad-and-sometimes-good-tp5813831p5813847.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance