Re: Help understanding indexes, explain, and optimizing

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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/


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux