Optimize Postgresql query

Question

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);

Answer 1

The obvious alternative to the seq scan on the child table would be the nested loop in which it has to probe the child table by index 20,718 separate times. (Actually 21,215 times, but of course the planner doesn't know that at the time it makes its decision). Each one of those probes is going to go to an assumedly random leaf page in the index, and then follow up to a random spot in the child table itself, triggering a bunch of random IO. If all that of that data is already found in the cache, this will be a good plan. But if it actually has to hit the disk for each one this is going to be ungodly slow (especially since your disk seems to be powered by gerbils--your pre-edit plan where it took 22 seconds to seq scan the child table is simply horrible).

If you want to see the alternative plan, you can set enable_hashjoin = off before executing the query. This is generally not a setting you would want to do in production, it is more for exploratory purposes. But as a last resort, you could make this setting change locally just for running this query, then reset it.

If you like the nested loop because most of your data actually is in memory most of the time, you can signal this to the planner by reducing the value of "random_page_cost" to something closer to 1. In my hands, lowering from the default of 4 down to 1.4 was enough to flip the plan from the seq scan to the nested loop. If most of your data is always in cache (not just the child table and its index) then it might be reasonable to make a global change to this parameter. If you do that, however, you should have a plan to pre-warm your database in case you reboot the machine (such as this). Getting the data back into cache the natural way is likely to be slow and painful.

Answer 2

In your table declaration you have partner_pk as varchar. That should not be varchar. In fact, this whole table structure could be better defined as a single-table hierarchy.

• Use Identity Columns
• Use a custom domain to store email
• Use a single-table hierarchy

Like this,

CREATE EXTENSION citext;
CREATE DOMAIN email AS citext
  CHECK ( value ~ '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$' );


CREATE TABLE public.people (
    people_id         int     PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    parent_people_id  int     REFERENCES people,
    first_name        text,
    last_name         text,
    email             email   NOT NULL,
    partner_id        int
);
CREATE INDEX ON people(parent_people_id);
CREATE INDEX ON people(email);

See also

• What is the best way to store an email address in PostgreSQL?
• What are Identity Columns?