From: Gerardo Herzig <gherzig@fmed.uba.ar>
To: Alessandro Ferrucci <alessandroferrucci@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow query with 3 table joins
Date: Wed, 26 Apr 2017 08:27:19 -0400 (EDT)
Message-ID: <141643966.192926.1493209639777.JavaMail.root@fmed.uba.ar> (raw)
In-Reply-To: <CAMgh6pKk+hdnjaWxJ5mr0MpmbK-i-UHq35QB2C9LorXRNUkuEw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
----- Mensaje original -----
> De: "Alessandro Ferrucci" <alessandroferrucci@gmail.com>
> Para: pgsql-performance@postgresql.org
> Enviados: Miércoles, 26 de Abril 2017 0:19:37
> Asunto: Re: [PERFORM] Slow query with 3 table joins
>
>
>
> After about 40 inutes the slow query finally finished and the result
> of the EXPLAIN plan can be found here:
>
>
> https://explain.depesz.com/s/BX22
>
>
> Thanks,
> Alessandro Ferrucci
1) Looking at the "Rows removed by filter" in that explain, looks like a selectivity issue: Many (many many) rows are fetched, just to be rejected later.
I think you can try a partial index on ''field (unit_id) where field_name="SHEETS_PRESENT"'', if it is practical to you.
See https://www.postgresql.org/docs/current/static/indexes-partial.html for a good read about partial indexes.
2) 9.2 is a pretty old version of PG. If you are migrating yet, you should consider a more recent version
HTH
Gerardo
--
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: gherzig@fmed.uba.ar, alessandroferrucci@gmail.com
Subject: Re: Slow query with 3 table joins
In-Reply-To: <141643966.192926.1493209639777.JavaMail.root@fmed.uba.ar>
* 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