1

I have a table with those columns: id, uuid, customer_id, building, dept, values

I have a combined index on those columns: building, dept.

If I run this query: select count(*) from my_table where building = 666 and dept = 'my_dept', it takes 400 ms and there are 25 rows

If I run this query: select count(*) from my_table where building = 666 and dept = 'my_dept' and customer_id = 1, it takes 4 minutes. It always return 25 rows

that means all the 25 rows belong to customer 1. The table contains ~20 millions of rows

I know I can solve adding customer_id into the multi columns index, but why in the second case is so slow? should it benefit from the multi column index even if the customer_id is missed? I expect it a little bit slower but not 4 minutes.

Any idea?

6
  • Are you able to obtain a plan for both queries, to see what the differences are? Mar 4, 2022 at 15:06
  • yep in the second case instead of benefit from the building-dept index, it is trying to use another combined index uuid-customer_id. Might be correct? given the fact the DB does not know which one can be the best upfront.
    – Spinettaro
    Mar 4, 2022 at 15:09
  • What is the order of the columns in the uuid-customer_id index? Assuming they might not match the name. Mar 4, 2022 at 15:12
  • it is customer_id-uuid .
    – Spinettaro
    Mar 4, 2022 at 15:58
  • 1
    "4 minutes" don't make sense either way. Nor do "400 ms" for for an index(-only) scan of just 25 rows. Either should be much faster, even with very limited hardware. If you want to get to the bottom of this, provide proper information as instructed here: dba.meta.stackexchange.com/a/3299/3684 Mar 15, 2022 at 19:48

2 Answers 2

1

The difference is that the first query can use an index-only scan on the two-column index, because no other columns are needed. The second query also has to inspect the customer_id column, so it has to fetch the table row.

If you want an index-only scan for the second query, create the index like this:

CREATE INDEX ON my_table (building, dept) INCLUDE (customer_id);
0

It looks like not having customer_id as a column on the building_dept is going to be your problem.

For your first query, the optimizer knows that it can simply count all the rows directly returned from the building_dept index without needing to lookup additional data elsewhere.

However, once you add the additional where clause, you give the optimizer a decision to make. To to find rows with customer_id, it can do one of two things.

  1. Continue to use the building_dept index, and then lookup the customer_id in the main table.
  2. Use customer_uuid and then lookup building and dept in the main table.

In either case, it now requires looking up data in two place, instead of one, to return the data you're requesting. If you didn't have the customer_uuid index, the optimizer would not have that decision to make, and would go with option 1. However, it would still be looking up data in two locations instead of 1. Which would still be sub-optimal.

Postgres can estimate the number or rows that will be returned using statistics, and with those, it believe the better plan is to go to the customer_id-uuid index and then lookup building and dept in the main table, vs using the building_dept index and looking up customer in the main table. Try reviewing your statistics to see how many rows Postgres believes it has for the values you're passing to your query.

Similarly, try running these three select independently and seeing how many rows are returned by each.

select count(*) from my_table where building = 666

select count(*) from my_table where dept = 'my_dept'

select count(*) from my_table where customer_id = 1

To summarize, your best bet is going to be to add customer_id to the building_dept index and see if that helps the second query.

2
  • thanks for the reply. But why PG optimizer believes using customer_id index is better than using building-dept ? This is what sounds weird to me... As I said I know I can add customer_id into the building-dept index but not sure why it believes the first one is better given the fact it can benefit from 1 column-index only
    – Spinettaro
    Mar 5, 2022 at 9:51
  • @Spinettaro please see the updates to my answer Mar 5, 2022 at 13:08

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.