Date: Tue, 5 Aug 2014 17:16:30 -0700 (PDT)
From: David G Johnston <david.g.johnston@gmail.com>
To: pgsql-performance@postgresql.org
Message-ID: <1407284190063-5813847.post@n5.nabble.com>
In-Reply-To: <VisenaEmail.24.fd3487f9d1329566.147a8117731@tc7-on>
References: <VisenaEmail.24.fd3487f9d1329566.147a8117731@tc7-on>
Subject: Re: Query performing very bad and sometimes good
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Andreas Joseph Krogh-2 wrote
> Hi all. =C2=A0 Running version: on=3D> select version();
>  =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ve=
rsion
>=20=20
> -------------------------------------------------------------------------=
-----------------------------------
>  =C2=A0PostgreSQL 9.3.2 on x86_64-unknown-linux-gnu, compiled by gcc
> (Ubuntu/Linaro=20
> 4.6.3-1ubuntu5) 4.6.3, 64-bit =C2=A0 =C2=A0=20

9.3.2 is not release-worthy....


> Bad:
> Index Scan Backward using origo_email_delivery_received_idx on
> origo_email_delivery del=C2=A0(cost=3D0.42..1102717.48 rows=3D354038 widt=
h=3D98)
> (actual time=3D0.017..309196.670 rows=3D354296 loops=3D1)
>=20
>>>Add 4 new records<<
>=20
> Good (-ish):
> Index Scan Backward using origo_email_delivery_received_idx on
> origo_email_delivery del (cost=3D0.42..1102717.48 rows=3D354038 width=3D9=
8)
> (actual time=3D0.019..2431.773 rows=3D354300 loops=3D1)

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.=
=20
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.


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance