Thread: Help understanding indexes, explain, and optimizing a query
Hi everyone,
I'm experimenting with PostgreSQL, but since I'm no expert DBA, I'm
experiencing some performance issues.
Please take a look at the following query:
SELECT
/*groups."name" AS t2_r1,
groups."id" AS t2_r3,
groups."user_id" AS t2_r0,
groups."pretty_url" AS t2_r2,
locations."postal_code" AS t0_r6,
locations."pretty_url" AS t0_r7,
locations."id" AS t0_r8,
locations."colony_id" AS t0_r0,
locations."user_id" AS t0_r1,
locations."group_id" AS t0_r2,
locations."distinction" AS t0_r3,
locations."street" AS t0_r4,
locations."street_2" AS t0_r5,
schools."updated" AS t1_r10,
schools."level_id" AS t1_r4,
schools."pretty_url" AS t1_r11,
schools."user_id" AS t1_r5,
schools."id" AS t1_r12,
schools."type_id" AS t1_r6,
schools."distinction" AS t1_r7,
schools."cct" AS t1_r8,
schools."created_on" AS t1_r9,
schools."location_id" AS t1_r0,
schools."service_id" AS t1_r1,
schools."sustentation_id" AS t1_r2,
schools."dependency_id" AS t1_r3*/
groups.*,
locations.*,
schools.*
FROM locations
LEFT OUTER JOIN groups ON groups.id = locations.group_id
LEFT OUTER JOIN schools ON schools.location_id = locations.id
WHERE (colony_id = 71501)
ORDER BY groups.name, locations.distinction, schools.distinction
As you can see, I've commented out some parts. I did that as an
experiment, and it improved the query by 2x. I really don't understand
how is that possible... I also tried changing the second join to an
INNER join, and that improves it a little bit also.
Anyway, the main culprit seems to be that second join. Here's the output
from EXPLAIN:
Sort (cost=94315.15..94318.02 rows=1149 width=852)
Sort Key: groups.name, locations.distinction, schools.distinction
-> Merge Left Join (cost=93091.96..94256.74 rows=1149 width=852)
Merge Cond: ("outer".id = "inner".location_id)
-> Sort (cost=4058.07..4060.94 rows=1148 width=646)
Sort Key: locations.id
-> Hash Left Join (cost=1.01..3999.72 rows=1148 width=646)
Hash Cond: ("outer".group_id = "inner".id)
-> Index Scan using locations_colony_id on
locations (cost=0.00..3992.91 rows=1148 width=452)
Index Cond: (colony_id = 71501)
-> Hash (cost=1.01..1.01 rows=1 width=194)
-> Seq Scan on groups (cost=0.00..1.01
rows=1 width=194)
-> Sort (cost=89033.90..89607.67 rows=229510 width=206)
Sort Key: schools.location_id
-> Seq Scan on schools (cost=0.00..5478.10 rows=229510
width=206)
I don't completely understand what that output means, but it would seem
that the first join costs about 4000, but if I remove that join from the
query, the performance difference is negligible. So as I said, it seems
the problem is the join on the schools table.
I hope it's ok for me to post the relevant tables here, so here they are
(I removed some constraints and indexes that aren't relevant to the
query above):
CREATE TABLE groups
(
user_id int4 NOT NULL,
name varchar(50) NOT NULL,
pretty_url varchar(50) NOT NULL,
id serial NOT NULL,
CONSTRAINT groups_pk PRIMARY KEY (id),
)
CREATE TABLE locations
(
colony_id int4 NOT NULL,
user_id int4 NOT NULL,
group_id int4 NOT NULL,
distinction varchar(60) NOT NULL,
street varchar(60) NOT NULL,
street_2 varchar(50) NOT NULL,
postal_code varchar(5) NOT NULL,
pretty_url varchar(60) NOT NULL,
id serial NOT NULL,
CONSTRAINT locations_pk PRIMARY KEY (id),
CONSTRAINT colony FOREIGN KEY (colony_id)
REFERENCES colonies (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT "group" FOREIGN KEY (group_id)
REFERENCES groups (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
)
CREATE INDEX locations_fki_colony
ON locations
USING btree
(colony_id);
CREATE INDEX locations_fki_group
ON locations
USING btree
(group_id);
CREATE TABLE schools
(
location_id int4 NOT NULL,
service_id int4 NOT NULL,
sustentation_id int4 NOT NULL,
dependency_id int4 NOT NULL,
level_id int4 NOT NULL,
user_id int4 NOT NULL,
type_id int4 NOT NULL,
distinction varchar(25) NOT NULL,
cct varchar(20) NOT NULL,
created_on timestamp(0) NOT NULL,
updated timestamp(0),
pretty_url varchar(25) NOT NULL,
id serial NOT NULL,
CONSTRAINT schools_pk PRIMARY KEY (id),
CONSTRAINT "location" FOREIGN KEY (location_id)
REFERENCES locations (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
)
CREATE INDEX schools_fki_location
ON schools
USING btree
(location_id);
So I'm wondering what I'm doing wrong. I migrated this database from
MySQL, and on there it ran pretty fast.
Kind regards,
Ivan V.
i.v.r. wrote:
> Hi everyone,
>
> I'm experimenting with PostgreSQL, but since I'm no expert DBA, I'm
> experiencing some performance issues.
>
> Please take a look at the following query:
>
> SELECT
> /*groups."name" AS t2_r1,
> groups."id" AS t2_r3,
> groups."user_id" AS t2_r0,
> groups."pretty_url" AS t2_r2,
> locations."postal_code" AS t0_r6,
> locations."pretty_url" AS t0_r7,
> locations."id" AS t0_r8,
> locations."colony_id" AS t0_r0,
> locations."user_id" AS t0_r1,
> locations."group_id" AS t0_r2,
> locations."distinction" AS t0_r3,
> locations."street" AS t0_r4,
> locations."street_2" AS t0_r5,
> schools."updated" AS t1_r10,
> schools."level_id" AS t1_r4,
> schools."pretty_url" AS t1_r11,
> schools."user_id" AS t1_r5,
> schools."id" AS t1_r12,
> schools."type_id" AS t1_r6,
> schools."distinction" AS t1_r7,
> schools."cct" AS t1_r8,
> schools."created_on" AS t1_r9,
> schools."location_id" AS t1_r0,
> schools."service_id" AS t1_r1,
> schools."sustentation_id" AS t1_r2,
> schools."dependency_id" AS t1_r3*/
> groups.*,
> locations.*,
> schools.*
> FROM locations
> LEFT OUTER JOIN groups ON groups.id = locations.group_id
> LEFT OUTER JOIN schools ON schools.location_id = locations.id
> WHERE (colony_id = 71501)
> ORDER BY groups.name, locations.distinction, schools.distinction
>
> As you can see, I've commented out some parts. I did that as an
> experiment, and it improved the query by 2x. I really don't understand
> how is that possible... I also tried changing the second join to an
> INNER join, and that improves it a little bit also.
>
> Anyway, the main culprit seems to be that second join. Here's the output
> from EXPLAIN:
>
> Sort (cost=94315.15..94318.02 rows=1149 width=852)
> Sort Key: groups.name, locations.distinction, schools.distinction
> -> Merge Left Join (cost=93091.96..94256.74 rows=1149 width=852)
> Merge Cond: ("outer".id = "inner".location_id)
> -> Sort (cost=4058.07..4060.94 rows=1148 width=646)
> Sort Key: locations.id
> -> Hash Left Join (cost=1.01..3999.72 rows=1148 width=646)
> Hash Cond: ("outer".group_id = "inner".id)
> -> Index Scan using locations_colony_id on
> locations (cost=0.00..3992.91 rows=1148 width=452)
> Index Cond: (colony_id = 71501)
> -> Hash (cost=1.01..1.01 rows=1 width=194)
> -> Seq Scan on groups (cost=0.00..1.01
> rows=1 width=194)
> -> Sort (cost=89033.90..89607.67 rows=229510 width=206)
> Sort Key: schools.location_id
> -> Seq Scan on schools (cost=0.00..5478.10 rows=229510
> width=206)
>
> I don't completely understand what that output means, but it would seem
> that the first join costs about 4000, but if I remove that join from the
> query, the performance difference is negligible. So as I said, it seems
> the problem is the join on the schools table.
>
> I hope it's ok for me to post the relevant tables here, so here they are
> (I removed some constraints and indexes that aren't relevant to the
> query above):
>
> CREATE TABLE groups
> (
> user_id int4 NOT NULL,
> name varchar(50) NOT NULL,
> pretty_url varchar(50) NOT NULL,
> id serial NOT NULL,
> CONSTRAINT groups_pk PRIMARY KEY (id),
> )
>
> CREATE TABLE locations
> (
> colony_id int4 NOT NULL,
> user_id int4 NOT NULL,
> group_id int4 NOT NULL,
> distinction varchar(60) NOT NULL,
> street varchar(60) NOT NULL,
> street_2 varchar(50) NOT NULL,
> postal_code varchar(5) NOT NULL,
> pretty_url varchar(60) NOT NULL,
> id serial NOT NULL,
> CONSTRAINT locations_pk PRIMARY KEY (id),
> CONSTRAINT colony FOREIGN KEY (colony_id)
> REFERENCES colonies (id) MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> CONSTRAINT "group" FOREIGN KEY (group_id)
> REFERENCES groups (id) MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> )
> CREATE INDEX locations_fki_colony
> ON locations
> USING btree
> (colony_id);
> CREATE INDEX locations_fki_group
> ON locations
> USING btree
> (group_id);
>
> CREATE TABLE schools
> (
> location_id int4 NOT NULL,
> service_id int4 NOT NULL,
> sustentation_id int4 NOT NULL,
> dependency_id int4 NOT NULL,
> level_id int4 NOT NULL,
> user_id int4 NOT NULL,
> type_id int4 NOT NULL,
> distinction varchar(25) NOT NULL,
> cct varchar(20) NOT NULL,
> created_on timestamp(0) NOT NULL,
> updated timestamp(0),
> pretty_url varchar(25) NOT NULL,
> id serial NOT NULL,
> CONSTRAINT schools_pk PRIMARY KEY (id),
> CONSTRAINT "location" FOREIGN KEY (location_id)
> REFERENCES locations (id) MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> )
> CREATE INDEX schools_fki_location
> ON schools
> USING btree
> (location_id);
>
> So I'm wondering what I'm doing wrong. I migrated this database from
> MySQL, and on there it ran pretty fast.
Have you done an 'analyze' or 'vacuum analyze' over these tables?
A left outer join gets *everything* from the second table:
> LEFT OUTER JOIN groups ON groups.id = locations.group_id
> LEFT OUTER JOIN schools ON schools.location_id = locations.id
So they will load everything from groups and schools. Maybe they should
be left join's not left outer joins?
--
Postgresql & php tutorials
http://www.designmagick.com/
Chris escribió: > Have you done an 'analyze' or 'vacuum analyze' over these tables? > > A left outer join gets *everything* from the second table: > > > LEFT OUTER JOIN groups ON groups.id = locations.group_id > > LEFT OUTER JOIN schools ON schools.location_id = locations.id > > So they will load everything from groups and schools. Maybe they > should be left join's not left outer joins? > > Yes, I did that. I tried your other suggestion and it did improve it by about 200ms. I also repurposed the query by selecting first from the groups table and joining with the locations and schools tables, and that made all the difference. Now it's down to 32ms. Yipee! Thanks! Ivan V.
Actually I think LEFT OUTER JOIN is equivalent to LEFT JOIN. The Postgres manual says that the word OUTER is optional. Either way you get "...all rows in the qualified Cartesian product (i.e., all combined rows that pass its join condition), plus one copy of each row in the left-hand table for which there was no right-hand row that passed the join condition." It sounds like the original posters problem was a less than optimal join order, and from what I understand Postgres can't reorder left joins. -----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Chris Sent: Monday, March 06, 2006 6:40 PM To: i.v.r. Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Help understanding indexes, explain, and optimizing i.v.r. wrote: > Hi everyone, [Snip] > So I'm wondering what I'm doing wrong. I migrated this database from > MySQL, and on there it ran pretty fast. Have you done an 'analyze' or 'vacuum analyze' over these tables? A left outer join gets *everything* from the second table: > LEFT OUTER JOIN groups ON groups.id = locations.group_id > LEFT OUTER JOIN schools ON schools.location_id = locations.id So they will load everything from groups and schools. Maybe they should be left join's not left outer joins? -- Postgresql & php tutorials http://www.designmagick.com/ ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
"Dave Dutcher" <dave@tridecap.com> writes: > It sounds like the original posters problem was a less than optimal join > order, and from what I understand Postgres can't reorder left joins. Not really relevant to the OP's immediate problem, but: that's fixed in CVS HEAD. http://archives.postgresql.org/pgsql-hackers/2005-12/msg00760.php http://archives.postgresql.org/pgsql-committers/2005-12/msg00352.php regards, tom lane