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, Feb 09 '21 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, Feb 09 '21 at 21:17
That's my ORM - but actually it didn't make any difference in the query plan. Updated the question now. — Piotr, Feb 09 '21 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, Feb 09 '21 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, Feb 09 '21 at 21:26
Is there a difference if you make your where clause part of the join? — Jonathan Willcock, Feb 09 '21 at 21:31
@AngelicosPhosphoros suggestion 1 didn't do anything, the second one worked great thanks. So this is basically forcing the optimizers hand and the only way to do it? — Piotr, Feb 09 '21 at 21:34
@JonathanWillcock it didn't make any difference. Only thing that seems to help so far is to make it a "With as..." subquery like Angelicos suggested. — Piotr, Feb 09 '21 at 21:36
@Piotr It basically run 2 queries in one transaction but doesn't send data between server and client. It named CTE and each CTE optimized like separate query. — Angelicos Phosphoros, Feb 09 '21 at 21:38
I would almost certainly think there is something wrong with the structure of your tables/indexes. Can you post your table and indexes definitions? This is a really simple case where there shouldn't be any issue using indexes as assumed. — Kousalik, Feb 09 '21 at 22:05
The estimated rows are completely wrong. Does the plan change if you run `vacuum analyze guest_list,`? — , Feb 09 '21 at 22:05
There is something strange going on. What is your exact Postgres version? `select version();` will tell you. Is `event_user_detail.guest_list_id` also a `bigint`? — , Feb 09 '21 at 22:13
@a_horse_with_no_name version is "PostgreSQL 10.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit" (AWS Aurora postgresql). Glad to hear it's not just me that thinks this is weird. What specifically is it that you think doesn't make sense? — Piotr, Feb 09 '21 at 22:19
@Kousalik and a_horse_with_no_name. I have added the db structures to the question now. — Piotr, Feb 09 '21 at 22:24
FYI: Might be better asked on sister site, http://www.DBA.StackExchange.com/ — Basil Bourque, Feb 09 '21 at 23:16
@Piotr in my empty test database, the plan has only 3 steps, NESTED LOOPS(INDEX SCAN guest_list.event_id, INDEX SCAN event_user_detail.guest_list_id) as I would expect. Please take a look at the answers suggesting actions around statistics - this is most likely the right way to go. — Kousalik, Feb 10 '21 at 08:08

score 4 · Answer 1 · answered Feb 10 '21 at 01:37

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.

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? — Piotr, Feb 10 '21 at 08:27
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.. — jjanes, Feb 10 '21 at 16:36

score 3 · Accepted Answer · answered Feb 10 '21 at 07:37

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 '21 at 07:37

Laurenz Albe

209,280
17
206
263

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 Feb 10 '21 at 08:28
Also it's AWS RDS Aurora, so I'm hoping they have set those variables to appropriate values for their environment? – Piotr Feb 10 '21 at 08:28
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... – Piotr Feb 10 '21 at 08:58

Angelicos Phosphoros · Answer 3 · 2021-02-09T22:05:36.847

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 09 '21 at 22:05

answered Feb 09 '21 at 21:44

Angelicos Phosphoros

2,469
9
24

Thanks. This is the best solution so far, but it is weird that this would be needed on a simple join? – Piotr Feb 09 '21 at 21:52
@Piotr I don't know better way and use this always when planner generate suboptimal plans. – Angelicos Phosphoros Feb 09 '21 at 21:55
@a_horse_with_no_name You are right, I fixed. – Angelicos Phosphoros Feb 09 '21 at 22:06
Apparently you can use a hack to force PostgreSQL not to inline: https://stackoverflow.com/questions/14897816/how-can-i-prevent-postgres-from-inlining-a-subquery – Piotr Feb 09 '21 at 22:14

Why isn't PostgreSQL using index for this join query

3 Answers3