Search code examples
postgresqlindexingquery-performance

Are these indexes doing the same thing in respect to customer_id?

I'm pretty new to PostgreSQL so apologies if I'm asking the obvious.

I've got a table called customer_products. It contains the following two indexes:

CREATE INDEX customer_products_customer_id
   ON public.customer_products USING btree (customer_id)

CREATE UNIQUE INDEX customer_products_customer_id_product_id
   ON public.customer_products USING btree (customer_id, product_id)

Are they both doing the same thing in respect to customer_id or do they function in a different way? I'm not sure if I should leave them or remove customer_products_customer_id.

Solution

  • There is nothing that the first index can do that the second cannot, so you should drop the first index.

    The only advantage of the first index over the second when it comes to queries whose WHERE (or ORDER BY) clause involves customer_id only is that the index is smaller. That makes a range scan over many index entries somewhat faster.

    The price for an extra index in terms of size and data modification speed usually outweighs that advantage. In a read-only data warehouse where I have a query that profits significantly I may be tempted to keep both indexes, otherwise I wouldn't.

    You should definitely not drop the UNIQUE index, because it has a valuable use that has nothing to do with performance: it prevents the table from containing two rows that have the save values for the indexed columns. If that is what you want to guarantee, a UNIQUE index will make sure that your data keep in good shape.

    Side remark: even though the effect is the same, it is better if the table has a unique constraint (which is backed by a unique index) than just having the index. If nothing else, it documents the purpose better.