0

I was looking at a query to make it more performant and I encountered an interesting case. This query executes much faster when I add a (redundant) JOIN that doesn't change the actual result set.

Original Query:

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
select
    *
from
    product_reservations l1_
inner join product_occupancy_items l0_ on l1_.id = l0_.id
inner join products l2_ on l0_.product_id = l2_.id
where
    l2_.customer_id = 'a19917c2-5ee8-47c2-a757-7799c0e54b0d'
    and l0_.date_range && '[2019-08-09,2019-08-11)' = true

The query executes at ~88ms, this is the explain https://explain.dalibo.com/plan/yJG

Adding a redundant JOIN:

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
select
    *
from
    product_reservations l1_
inner join product_occupancy_items l0_ on l1_.id = l0_.id
inner join products l2_ on l0_.product_id = l2_.id
inner join products l3_ on l0_.product_id = l3_.id
where
    l2_.customer_id = 'a19917c2-5ee8-47c2-a757-7799c0e54b0d'
    and l0_.date_range && '[2019-08-09,2019-08-11)' = true

This produces the following explain https://explain.dalibo.com/plan/Op3

This query runs at 20ms. About 4 times faster. As you maybe noticed, the only difference is just an useless JOIN on a table already JOINed (products).

When looking at the EXPLAIN for differences, we can find something in the product_occupancy_items section:

  • In the 1st query, the planner uses only the index of the external key with products and takes all the matching products applying the date range filter without index. This is much faster even though there are ~12k matching products to deal with (and then after applying the filter just 65)
  • In the 2nd query, the planner uses the index idx_occupancy_items_date_range to filter rows with the overlap (this is a gist index on date range) that's actually good on paper, but it takes a whole 80ms. This is understandable since it's used as bitmap index scan and there are many rows with that date range (~40k)

What's going on is kind of clear from a "what" point of view. I'm not sure to understand the "why". It looks to me that purely for an implementation point of view, it changed the part of the plan to access product_occupancy_items and got lucky.

I'm wondering if we can do anything to allow the planner to make the same decision without requiring to modify the query in order to fool the planner.

Note how while the difference may sound in the order of few ms, this query is executed many times for a bulk process. The whole bulk execution runs at about 3s with the double join and at about 30s without it. So the difference is sensible.

EDIT: Adding relevant part of the schema definition (just redacted columns not useful for this question)

CREATE TABLE public.product_occupancy_items (
    id uuid NOT NULL,
    product_id uuid NOT NULL,
    date_range daterange NOT NULL,
    CONSTRAINT product_occupancy_items_pkey PRIMARY KEY (id),
    CONSTRAINT fk_4a50848387335af1 FOREIGN KEY (product_id) REFERENCES public.products(id) ON DELETE CASCADE
);
CREATE INDEX idx_4a50848387335af1 ON public.product_occupancy_items USING btree (product_id);
CREATE INDEX idx_occupancy_items_date_range ON public.product_occupancy_items USING gist (date_range);

CREATE TABLE public.products (
    id uuid NOT NULL,
    customer_id uuid NOT NULL,
    quantity int2 NOT NULL,
    CONSTRAINT products_pkey PRIMARY KEY (id),
    CONSTRAINT fk_c03833c58565851 FOREIGN KEY (customer_id) REFERENCES public.customers(id) ON DELETE CASCADE
);
CREATE INDEX idx_c03833c58565851 ON public.products USING btree (customer_id);

CREATE TABLE public.product_reservations (
    id uuid NOT NULL,
    order_id uuid NOT NULL,
    CONSTRAINT product_reservations_pkey PRIMARY KEY (id),
    CONSTRAINT fk_93e513ae8d9f6d38 FOREIGN KEY (order_id) REFERENCES public.orders(id) ON DELETE CASCADE
);
CREATE INDEX idx_93e513ae8d9f6d38 ON public.product_reservations USING btree (order_id);
11
  • Would it make a difference if you were to add the criteria of the where-clause to the inner joins? Commented Jul 20, 2021 at 14:27
  • No, as expected in this specific case, there is no difference for the planner. Moving the conditions in the inner joins does not change the explain of the respective queries.
    – Daniele
    Commented Jul 20, 2021 at 15:01
  • Any chance you could post those as text formatted plans? JSON formatted plans are almost impossible for humans to read, and whatever Dalibo does to them doesn't improve the matter any.
    – jjanes
    Commented Jul 20, 2021 at 15:40
  • Sure pastebin.com/f9pCDYW3 anyway dalibo is not that bad, you can also see the more traditional top down view by clicking "settings" and then classic. Also the json representation has more stuff. Once you are used to, it's much better
    – Daniele
    Commented Jul 20, 2021 at 16:35
  • What version of PostgreSQL are you using?
    – jjanes
    Commented Jul 20, 2021 at 16:44

2 Answers 2

1

The easiest thing would be to just get rid of the GiST index. It can't be misused if it doesn't exist. Assuming you created that index for a good (but un-shown) reason and still need it, then if your goal is not to change the original query, I think the best shot is to create a new index:

USING gist (date_range, product_id);

Or maybe

USING gist (product_id, date_range);

The 2nd of those is probably better for your shown query; while the 1st is probably "good enough" with that query, plus will probably work well with the other queries that motivated you to create the original GiST index in the first place (and so can be used as a replacement for that original index, saving both space and future index maintenance work). You will need the btree_gist extension to include product_id into a GiST index.

I don't know why the extra join makes things better. But if your goal is to not change the original query, then figuring out why is probably not worth the effort as I doubt it will lead to any insights that can be acted upon without changing the query.

Regarding the 12,000 rows that get filtered out with your truly faster plan, PostgreSQL thinks that it will need to read them from disk before it can filter them. So it thinks that that will be quite expensive. In reality, it seems like all that data was already cached (or maybe you just have really fast disks) so that expense did not actually occur. So it is possible you could get a similar plan to the fast one with the original query and original indexes just by changing random_page_cost, and maybe seq_page_cost. But if that does work, I suspect the new settings good for this query would not be good for the system as a whole (unless your system settings are just wrong to start with).

What is your distribution of date_range like? Is each range about the same length, or do they vary greatly in length? What is the total time period covered by all ranges combined? Your GiST index seems to be underperforming compared to what I can simulate, but if rebuilding the index didn't help then there is probably nothing you can do about this, it is probably baked into your data distribution.

4
  • This is the most satisfying answer. I still did not verify everything but I agree overall with what you're saying. Having that index avoids postgresql using a bitmap index. The bitmap index is particularly slow because there are many rows in that date range.
    – Daniele
    Commented Jul 22, 2021 at 14:43
  • Little update: playing with random_page_cost, and seq_page_cost do not make any difference. I think this because that would make a difference in case it should decide between an index scan and sequential scan, but this is not the case with bitmap index scan vs index scan.
    – Daniele
    Commented Jul 26, 2021 at 14:42
  • Also to reply to your questions: the distribution of date_range does not vary greatly (kind of few days each). Date ranges are from 2018 to 2021, select count(*), sum(UPPER(poi.date_range) - LOWER(poi.date_range)), AVG(UPPER(poi.date_range) - LOWER(poi.date_range)) FROM product_occupancy_items poi gives 457267, 1455640, 3.1
    – Daniele
    Commented Jul 26, 2021 at 14:52
  • The index scan which has 953*13 rows removed using a filter is going to be believed by the planner to generate a lot of IO (the planner doesn't tell you how many rows it thought would be filtered out, so it is hard to say for sure), which is why the IO cost parameters had some shot of working. (It did work for me on a simulated data set). But it isn't really surprising it didn't work for you, they are a crude tool.
    – jjanes
    Commented Jul 26, 2021 at 16:56
1

It may be that all you need is to avoid using the date-range index. I would have done it like this on Oracle:

select
    *
from
    product_reservations l1_
inner join product_occupancy_items l0_ on l1_.id = l0_.id
inner join products l2_ on l0_.product_id = l2_.id
where
    l2_.customer_id = 'a19917c2-5ee8-47c2-a757-7799c0e54b0d'
    and l0_.date_range + 0 && '[2019-08-09,2019-08-11)' = true

Any function that prevents Postgresql from using the index will do.

4
  • You can't add zero to a daterange, as it is not a scalar. Maybe the best way to situationally break the index would be to cast the column to text and back again, l0_.date_range::text::daterange
    – jjanes
    Commented Jul 20, 2021 at 16:11
  • Thanks. I'm familiar with those kind of workarounds. But I'm looking for some more in-depth explanation of why PostgreSQL thinks it's better taking that route and eventually what can be done to fix this thing in a more organic way rather than avoiding to use the index for this specific query.
    – Daniele
    Commented Jul 20, 2021 at 16:25
  • 1
    In that case some more information about your schema could be useful, table and index create statements. I thought your question was "I'm wondering if we can do anything to allow the planner to make the same decision ...". Commented Jul 20, 2021 at 16:45
  • Updated original question with some table definitions
    – Daniele
    Commented Jul 20, 2021 at 17:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.