I'm struggling to get the below query to perform well. It's doing a sequential scan on the children
table even though there's indexes on the join columns. Any ideas?
EXPLAIN ANALYZE SELECT "children".* from "children"
INNER JOIN "parents" ON "parents"."id" = "children"."parent_id"
WHERE "parents"."partner_pk" = 'partner'
Hash Join (cost=55541.97..140831.44 rows=20804 width=168) (actual time=366.809..1851.311 rows=21215 loops=1)
Hash Cond: (children.parent_id = parents.id)
Buffers: shared hit=47476
-> Seq Scan on children (cost=0.00..66250.30 rows=2152130 width=168) (actual time=0.007..1012.502 rows=2215906 loops=1)
Buffers: shared hit=44729
-> Hash (cost=55282.99..55282.99 rows=20718 width=4) (actual time=17.496..17.496 rows=21215 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 746kB
Buffers: shared hit=2747
-> Bitmap Heap Scan on parents (cost=876.99..55282.99 rows=20718 width=4) (actual time=2.339..10.817 rows=21215 loops=1)
Recheck Cond: ((partner_pk)::text = 'partner'::text)
Heap Blocks: exact=2614
Buffers: shared hit=2747
-> Bitmap Index Scan on index_parents_on_partner_pk (cost=0.00..871.81 rows=20718 width=0) (actual time=1.992..1.992 rows=21215 loops=1)
Index Cond: ((partner_pk)::text = 'partner'::text)
Buffers: shared hit=133
Planning time: 0.280 ms
Execution time: 1855.458 ms
CREATE TABLE "public"."parents" (
"id" int4 NOT NULL DEFAULT nextval('parents_id_seq'::regclass),
"email" varchar NOT NULL,
"partner_id" int4,
"partner_pk" varchar
)
WITH (OIDS=FALSE);
ALTER TABLE "public"."parents" OWNER TO "bark";
CREATE UNIQUE INDEX "index_parents_on_LOWER_email" ON "public"."parents" USING btree("lower(email::text)" ASC NULLS LAST);
CREATE INDEX "index_parents_on_partner_id" ON "public"."parents" USING btree(partner_id ASC NULLS LAST);
CREATE INDEX "index_parents_on_partner_pk" ON "public"."parents" USING btree(partner_pk ASC NULLS LAST);
CREATE TABLE "public"."children" (
"id" int4 NOT NULL DEFAULT nextval('children_id_seq'::regclass),
"first_name" varchar,
"last_name" varchar,
"parent_id" int4,
"email" varchar,
)
CREATE INDEX "index_children_on_parent_id" ON "public"."children" USING btree(parent_id ASC NULLS LAST);
EXPLAIN (ANALYZE, BUFFERS)
, preferably withtrack_io_timings
turned on. Also, is this fully reproducible, or will it run much faster if you immediately execute the query again? – jjanes Sep 20 '18 at 0:04