2 
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;
Improve this question
19
  • 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 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. 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. 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? 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. Commented Feb 9, 2021 at 21:26

3 Answers 3

Reset to default
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.

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

2 Comments

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?
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..
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.

Improve this answer

3 Comments

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.
Also it's AWS RDS Aurora, so I'm hoping they have set those variables to appropriate values for their environment?
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...
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).

Improve this answer

4 Comments

Thanks. This is the best solution so far, but it is weird that this would be needed on a simple join?
@Piotr I don't know better way and use this always when planner generate suboptimal plans.
@a_horse_with_no_name You are right, I fixed.
Apparently you can use a hack to force PostgreSQL not to inline: stackoverflow.com/questions/14897816/…

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.