From: David G Johnston <david.g.johnston@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Query performing very bad and sometimes good
Date: Tue, 5 Aug 2014 17:16:30 -0700 (PDT)
Message-ID: <1407284190063-5813847.post@n5.nabble.com> (raw)
In-Reply-To: <VisenaEmail.24.fd3487f9d1329566.147a8117731@tc7-on>
References: <VisenaEmail.24.fd3487f9d1329566.147a8117731@tc7-on>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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-tp5813831p58138...
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: david.g.johnston@gmail.com
Subject: Re: Query performing very bad and sometimes good
In-Reply-To: <1407284190063-5813847.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox