Why isn't PostgreSQL using index for this join query

Question

explain (analyze) select
    event_user_detail.*

from event_user_detail
inner join guest_list on event_user_detail.guest_list_id = guest_list.id

where
    guest_list.event_id=2985739029

Results in the following query plan:

Gather  (cost=1052.56..43408.58 rows=244 width=97) (actual time=66.570..67.810 rows=0 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Nested Loop  (cost=52.56..42384.18 rows=102 width=97) (actual time=57.183..57.183 rows=0 loops=3)
        ->  Parallel Seq Scan on guest_list  (cost=0.00..13151.33 rows=5 width=8) (actual time=56.941..57.169 rows=2 loops=3)
              Filter: (event_id = '2985739029'::bigint)
              Rows Removed by Filter: 254489
        ->  Bitmap Heap Scan on event_user_detail  (cost=52.56..5830.93 rows=1564 width=97) (actual time=0.007..0.007 rows=0 loops=5)
              Recheck Cond: (guest_list_id = guest_list.id)
              ->  Bitmap Index Scan on idx_event_user_detail_guest_list_id  (cost=0.00..52.17 rows=1564 width=0) (actual time=0.005..0.005 rows=0 loops=5)
                    Index Cond: (guest_list_id = guest_list.id)
Planning time: 0.252 ms
Execution time: 67.838 ms

Even tho there is an index on guest_list(event_id). Can someone explain why this is happening and if there is some way to fix it?

If I split this up in 2 queries, of which one is just to get the guest_list ids, and then do a simple in (...ids) then the query is super quick. I tried doing the same with a subquery, but I think the optimiser made it into a join.

-- ----------------------------
-- Table structure for guest_list
-- ----------------------------
DROP TABLE IF EXISTS "public"."guest_list";
CREATE TABLE "public"."guest_list" (
  "id" int8 NOT NULL,
  "creation_date" timestamp(6),
  "last_modification_date" timestamp(6),
  "uuid" uuid,
  "deleted" bool NOT NULL,
  "name" varchar(255) COLLATE "pg_catalog"."default",
  "version" int4,
  "event_id" int8,
  "permanent_guest_list_id" int8,
  "color" varchar(255) COLLATE "pg_catalog"."default"
)
;

-- ----------------------------
-- Indexes structure for table guest_list
-- ----------------------------
CREATE INDEX "idx_guest_list_event_id" ON "public"."guest_list" USING btree (
  "event_id" "pg_catalog"."int8_ops" ASC NULLS LAST
);
CREATE INDEX "idx_guest_list_permanent_guest_list_id" ON "public"."guest_list" USING btree (
  "permanent_guest_list_id" "pg_catalog"."int8_ops" ASC NULLS LAST
);

-- ----------------------------
-- Uniques structure for table guest_list
-- ----------------------------
ALTER TABLE "public"."guest_list" ADD CONSTRAINT "uk_o4sa0dw6lcdjv96gl2p96xwki" UNIQUE ("uuid");

-- ----------------------------
-- Primary Key structure for table guest_list
-- ----------------------------
ALTER TABLE "public"."guest_list" ADD CONSTRAINT "guest_list_pkey" PRIMARY KEY ("id");

-- ----------------------------
-- Foreign Keys structure for table guest_list
-- ----------------------------
ALTER TABLE "public"."guest_list" ADD CONSTRAINT "fk7tk6fxgyo4h7ykelb9c0pe5ap" FOREIGN KEY ("event_id") REFERENCES "public"."event" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "public"."guest_list" ADD CONSTRAINT "guest_list_permanent_guest_list_id_fkey" FOREIGN KEY ("permanent_guest_list_id") REFERENCES "public"."permanent_guest_list" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;

-- ----------------------------
-- Table structure for event_user_detail
-- ----------------------------
DROP TABLE IF EXISTS "public"."event_user_detail";
CREATE TABLE "public"."event_user_detail" (
  "id" int8 NOT NULL,
  "creation_date" timestamp(6),
  "last_modification_date" timestamp(6),
  "uuid" uuid,
  "deleted" bool NOT NULL,
  "name" varchar(255) COLLATE "pg_catalog"."default",
  "value" text COLLATE "pg_catalog"."default",
  "version" int4,
  "event_id" int8,
  "user_id" int8,
  "guest_list_id" int8,
  "reference_user_id" int8
)
;

-- ----------------------------
-- Indexes structure for table event_user_detail
-- ----------------------------
CREATE INDEX "idx_event_user_detail_deleted" ON "public"."event_user_detail" USING btree (
  "deleted" "pg_catalog"."bool_ops" ASC NULLS LAST
);
CREATE INDEX "idx_event_user_detail_event_id" ON "public"."event_user_detail" USING btree (
  "event_id" "pg_catalog"."int8_ops" ASC NULLS LAST
);
CREATE INDEX "idx_event_user_detail_guest_list_id" ON "public"."event_user_detail" USING btree (
  "guest_list_id" "pg_catalog"."int8_ops" ASC NULLS LAST
);
CREATE INDEX "idx_event_user_detail_user_id" ON "public"."event_user_detail" USING btree (
  "user_id" "pg_catalog"."int8_ops" ASC NULLS LAST
);

-- ----------------------------
-- Uniques structure for table event_user_detail
-- ----------------------------
ALTER TABLE "public"."event_user_detail" ADD CONSTRAINT "uk_orfh8fkwtk681af38a65everr" UNIQUE ("uuid");

-- ----------------------------
-- Primary Key structure for table event_user_detail
-- ----------------------------
ALTER TABLE "public"."event_user_detail" ADD CONSTRAINT "event_user_detail_pkey" PRIMARY KEY ("id");

-- ----------------------------
-- Foreign Keys structure for table event_user_detail
-- ----------------------------
ALTER TABLE "public"."event_user_detail" ADD CONSTRAINT "fk8bffonom9l1fgcanegl9nm641" FOREIGN KEY ("user_id") REFERENCES "public"."user" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "public"."event_user_detail" ADD CONSTRAINT "fk_event_user_detail_guest_list_id" FOREIGN KEY ("guest_list_id") REFERENCES "public"."guest_list" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "public"."event_user_detail" ADD CONSTRAINT "fk_event_user_detail_reference_user_id" FOREIGN KEY ("reference_user_id") REFERENCES "public"."user" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "public"."event_user_detail" ADD CONSTRAINT "fkisr2ccpapw537ntw4c0mlytcw" FOREIGN KEY ("event_id") REFERENCES "public"."event" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;

I haven't thought through the explain plan so not adding an actual answer but looks like your query involves a sql-92 Cartesian join and an old style where clause for the join. I suspect you want to join on guest_list_id = id — Chris
– Chris, Commented Feb 9, 2021 at 21:16
Why do you use a CROSS JOIN and then implicitly making it an INNER JOIN in the WHERE clause? Just do an INNER JOIN. — sticky bit
– sticky bit, Commented Feb 9, 2021 at 21:17
That's my ORM - but actually it didn't make any difference in the query plan. Updated the question now. — Piotr
– Piotr, Commented Feb 9, 2021 at 21:22
What happens if you use FROM guest_list INNER JOIN event_user_detail ON guest_list.event_id=2985739029 AND event_user_detail.guest_list_id = guest_list.id WHERE guest_list.event_id=2985739029? — Angelicos Phosphoros
– Angelicos Phosphoros, Commented Feb 9, 2021 at 21:25
Also, if 2 requests works fast, you can do something like: WITH guets AS (SELECT id FROM guest_list WHERE event_id = xxx LIMIT 1) SELECT * FROM event_user_detail WHERE guest_list_id IN (SELECT id FROM guests). It should work very fast. Maybe you need a materialized WITH in newer versions. — Angelicos Phosphoros
– Angelicos Phosphoros, Commented Feb 9, 2021 at 21:26

jjanes · Accepted Answer · 2021-02-10 01:37:30Z

4

It vastly overestimates how many rows in event_user_detail it is going to find for each row in guest_list (probably because there is some row(s) in guest_list which does have a lot of entries in event_user_detail, just not the ones you are selecting here). The large number of rows it thinks it is going to find makes parallel query look attractive, but the way to get that parallel query is by using the seq scan on guest_list. So that is what it does.

You can disable parallel queries by setting max_parallel_workers_per_gather to 0. If you don't get much benefit from parallel query anyway, this may be a good "real" solution for you. If you do get a benefit from it and don't want to disable it, then you can at least do this just in the current session to see if my theory is correct.

answered Feb 10, 2021 at 1:37

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Piotr Over a year ago

SET max_parallel_workers_per_gather = 0; Fixed it. Wow! Is there any way I can figure out why this happened in the first place for such a simple query?

jjanes Over a year ago

I speculated some entries for guest_list really do have a large number of entries in event_user_detail. Is that true? If so, that is the answer to the why. If not, then your stats are probably way off and perhaps you could fix them with VACUUM ANALYZE..

Laurenz Albe · Accepted Answer · 2021-02-10 07:37:10Z

3

I concur with jjanes' answer, but I want to suggest these additional experiments:

Try to ANALYZE event_user_detail; and see if that improves the estimate.
It could be that random_page_cost is set too high: it is designed for spinning disks and estimates index scans as comparatively expensive. If you lower that parameter, PostgreSQL will be more ready to use index scans.

answered Feb 10, 2021 at 7:37

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

3 Comments

Piotr Over a year ago

Analyze didn't help unfortunately but that makes me think perhaps I should try to check those analytics myself and understand why the planner did what it did.

Piotr Over a year ago

Also it's AWS RDS Aurora, so I'm hoping they have set those variables to appropriate values for their environment?

Piotr Over a year ago

Actually turns out that random_page_cost was set to 4 on AWS Aurora (!). Setting it to 1.5 or lower fixes the problem. Weird...

Angelicos Phosphoros · Accepted Answer · 2021-02-09 22:05:36Z

1

You can do using CTE:

WITH guest AS (
  SELECT id FROM guest_list WHERE event_id=2985739029 LIMIT 1
)
SELECT * FROM event_user_detail WHERE guest_list_id IN (SELECT id FROM guest)

CTE in older versions of postgresql runs like separate queries in one transactions and planned independently but doesn't send results from CTE to client.

You can read about them in the docs. Beware that this behaviour changed since 12 version of postgres and if you want to preserve old you should write it like:

WITH guest AS MATERIALIZED (
  SELECT id FROM guest_list WHERE event_id=2985739029 LIMIT 1
)
SELECT * FROM event_user_detail WHERE guest_list_id IN (SELECT id FROM guest)

Also they are very useful to avoid deadlocks in updates:

WITH to_update AS (
    SELECT * FROM my_table WHERE condition 
    ORDER BY id ASC FOR UPDATE
)
UPDATE my_table SET ... WHERE condition;

This would make all rows lock in certain order which prevents deadlocks which possible with plain update queries (e.g. both queries need to modify ids 1 and 2, and with this CTE there cannot be that first lock 1 and wait 2 while second lock 2 and wait for 1).

edited Feb 9, 2021 at 22:05

answered Feb 9, 2021 at 21:44

Angelicos Phosphoros

3,22113 silver badges32 bronze badges

4 Comments

Piotr Over a year ago

Thanks. This is the best solution so far, but it is weird that this would be needed on a simple join?

Angelicos Phosphoros Over a year ago

@Piotr I don't know better way and use this always when planner generate suboptimal plans.

Angelicos Phosphoros Over a year ago

@a_horse_with_no_name You are right, I fixed.

Piotr Over a year ago

Apparently you can use a hack to force PostgreSQL not to inline: stackoverflow.com/questions/14897816/…

Collectives™ on Stack Overflow

Why isn't PostgreSQL using index for this join query

3 Answers 3

2 Comments

3 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related