Date: Wed, 26 Apr 2017 11:00:52 -0400 (EDT)
From: Gerardo Herzig <gherzig@fmed.uba.ar>
To: Alessandro Ferrucci <alessandroferrucci@gmail.com>
Cc: pgsql-performance@postgresql.org
Message-ID: <1003768222.200870.1493218852934.JavaMail.root@fmed.uba.ar>
In-Reply-To: 
 <CAMgh6pKk+hdnjaWxJ5mr0MpmbK-i-UHq35QB2C9LorXRNUkuEw@mail.gmail.com>
Subject: Re: Slow query with 3 table joins
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Some other approaches you could try:

1) What about an hashed index? You could make=20
CREATE INDEX ON FIELD (unit_id, hashtext(field_name))

and changing your query accordingly....

"....where hashtext(FIELD.FIELD_NAME)=3Dhashtext('SHEETS_PRESENT') ...."

2) Partitioning (not native yet, but can be simulated through inheritance),=
 like in
https://www.postgresql.org/docs/current/static/ddl-partitioning.html
This could work well if you have a sort of limited different values in FIEL=
D.FIELD_NAME

Gerardo

----- Mensaje original -----
> De: "Alessandro Ferrucci" <alessandroferrucci@gmail.com>
> Para: pgsql-performance@postgresql.org
> Enviados: Mi=C3=A9rcoles, 26 de Abril 2017 0:19:37
> Asunto: Re: [PERFORM] Slow query with 3 table joins
>=20
>=20
>=20
> After about 40 inutes the slow query finally finished and the result
> of the EXPLAIN plan can be found here:
>=20
>=20
> https://explain.depesz.com/s/BX22
>=20
>=20
> Thanks,
> Alessandro Ferrucci
>=20
>=20
> On Tue, Apr 25, 2017 at 11:10 PM, Alessandro Ferrucci <
> alessandroferrucci@gmail.com > wrote:
>=20
>=20
>=20
>=20
> Hello - I am migrating a current system to PostgreSQL and I am having
> an issue with a relatively straightforward query being extremely
> slow.
>=20
>=20
> The following are the definitions of the tables:
>=20
>=20
> CREATE TABLE popt_2017.unit
> (
> id serial NOT NULL,
> unit_id text,
> batch_id text,
> create_date timestamp without time zone DEFAULT now(),
> update_date timestamp without time zone,
> CONSTRAINT unit_pkey PRIMARY KEY (id)
> )
> WITH (
> OIDS=3DFALSE
> );
>=20
>=20
> CREATE TABLE popt_2017.field
> (
> id serial NOT NULL,
> unit_id integer,
> subunit_data_id integer,
> field_name character varying(50),
> page_id character varying(20),
> page_type character varying(20),
> batch_id character varying(20),
> file_name character varying(20),
> data_concept integer,
> "GROUP" integer,
> omr_group integer,
> pres integer,
> reg_data text,
> ocr_conf text,
> ocr_dict text,
> ocr_phon text,
> create_date timestamp without time zone DEFAULT now(),
> update_date timestamp without time zone,
> CONSTRAINT field_pkey PRIMARY KEY (id),
> CONSTRAINT field_subunit_data_id_fkey FOREIGN KEY (subunit_data_id)
> REFERENCES popt_2017.subunit (id) MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> CONSTRAINT field_unit_id_fk FOREIGN KEY (unit_id)
> REFERENCES popt_2017.unit (id) MATCH FULL
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> CONSTRAINT field_unit_id_fkey FOREIGN KEY (unit_id)
> REFERENCES popt_2017.unit (id) MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION
> )
> WITH (
> OIDS=3DFALSE
> );
>=20
>=20
> CREATE TABLE popt_2017.answer
> (
> id serial NOT NULL,
> field_id integer,
> ans_status integer,
> ans text,
> luggage text,
> arec text,
> kfi_partition integer,
> final boolean,
> length integer,
> create_date timestamp without time zone DEFAULT now(),
> update_date timestamp without time zone,
> CONSTRAINT answer_pkey PRIMARY KEY (id),
> CONSTRAINT answer_field_id_fk FOREIGN KEY (field_id)
> REFERENCES popt_2017.field (id) MATCH FULL
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> CONSTRAINT answer_field_id_fkey FOREIGN KEY (field_id)
> REFERENCES popt_2017.field (id) MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION
> )
> WITH (
> OIDS=3DFALSE
> );
>=20
>=20
> Below are the index definitions for those tables:
>=20
>=20
> UNIT:
> CREATE UNIQUE INDEX unit_pkey ON unit USING btree (id);
> CREATE INDEX unit_unit_id_idx ON unit USING btree (unit_id);
>=20
>=20
> FIELD:
> CREATE UNIQUE INDEX field_pkey ON field USING btree (id)
> CREATE INDEX field_unit_id_idx ON field USING btree (unit_id)
> CREATE INDEX field_subunit_id_idx ON field USING btree
> (subunit_data_id)
> CREATE INDEX field_field_name_idx ON field USING btree (field_name)
>=20
>=20
> ANSWER:
> CREATE UNIQUE INDEX answer_pkey ON answer USING btree (id)
> CREATE INDEX answer_field_id_idx ON answer USING btree (field_id)
> CREATE INDEX answer_ans_idx ON answer USING btree (ans)
>=20
>=20
> The tables each have the following number of rows:
>=20
>=20
> UNIT: 10,315
> FIELD: 139,397,965
> ANSWER: 3,463,300
>=20
>=20
> The query in question is:
>=20
>=20
> SELECT
> UNIT.ID AS UNIT_ID,
> UNIT.UNIT_ID AS UNIT_UNIT_ID,
> UNIT.BATCH_ID AS UNIT_BATCH_ID,
> UNIT.CREATE_DATE AS UNIT_CREATE_DATE,
> UNIT.UPDATE_DATE AS UNIT_UPDATE_DATE
> FROM
> UNIT, FIELD, ANSWER
> WHERE
> UNIT.ID =3DFIELD.UNIT_ID AND
> FIELD.ID =3DANSWER.FIELD_ID AND
> FIELD.FIELD_NAME=3D'SHEETS_PRESENT' AND
> ANSWER.ANS=3D'2';
>=20
>=20
> I attempted to run an EXPLAIN (ANALYZE,BUFFERS) and the query has
> been running for 32 minutes now, So I won't be able to post the
> results (as I've never been able to get the query to actually
> finish.
>=20
>=20
> But, if I remove the join to UNIT (and just join FIELD and ANSWER)
> the resulting query is sufficiently fast, (the first time it ran in
> roughly 3 seconds), the query as such is:
>=20
>=20
> SELECT * FROM
> ANSWER, FIELD
> WHERE
> FIELD.ID =3DANSWER.FIELD_ID AND
> FIELD.FIELD_NAME=3D'SHEETS_PRESENT' AND
> ANSWER.ANS=3D'2';
>=20
>=20
> The EXPLAIN ( ANALYZE, BUFFERS ) output of that query can be found
> here https://explain.depesz.com/s/ueJq
>=20
>=20
> These tables are static for now, so they do not get DELETEs or
> INSERTS at all and I have run VACUUM ANALYZE on all the affected
> tables.
>=20
>=20
> I'm running PostgreSQL PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu,
> compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit
>=20
>=20
> I'm running this on RHEL 6.9
>=20
>=20
> On a server with 32 GB of ram, 2 CPUs.
>=20
>=20
> The following are the changes to postgresql.conf that I have made:
>=20
>=20
> shared_buffers =3D 7871MB
> effective_cache_size =3D 23611MB
> work_mem =3D 1000MB
> maintenance_work_mem =3D 2048MB
>=20
>=20
> I have not changed the autovacuum settings, but since the tables are
> static for now and I've already ran VACUUM that should not have any
> effect.
>=20
>=20
> Any assistance that could be provided is greatly appreciated.
>=20
>=20
> Thank you,
> Alessandro Ferrucci
>=20
>=20
>=20
>=20
>=20
>=20
>=20
>=20
>=20
>=20
>=20
> --
>=20
> Signed,
> Alessandro Ferrucci


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