Postgres: Optimization of query with simple where clause

Question

I have a table with the following columns:

ID (VARCHAR)
CUSTOMER_ID (VARCHAR)
STATUS (VARCHAR) (4 different status possible)
other not relevant columns

I try to find all the lines with customer_id = and status = two different status.

The query looks like:

SELECT *
FROM my_table
WHERE customer_id = '12345678' AND status IN ('STATUS1', 'STATUS2');

The table contains about 1 mio lines. I added two indexes on customer_id and status. The query still needs about 1 second to run.

The explain plan is:

1.  Gather      
2.  Seq Scan on my_table
    Filter: (((status)::text = ANY ('{SUBMITTED,CANCELLED}'::text[])) AND ((customer_id)::text = '12345678'::text))

I ran the 'analyze my_table' after creating the indexes. What could I do to improve the performance of this quite simple query?

Could you share (in plain text) the complete results from EXPLAIN(ANALYZE, VERBOSE, BUFFERS) for this query? And why do you have a VARCHAR for everything, even the id's? — Frank Heikens
– Frank Heikens, Commented Aug 19, 2022 at 8:29

O. Jones · Accepted Answer · 2022-08-19 11:13:38Z

You need a compound (multi-column) index to help satisfy your query.

Guessing, it seems like the most selective column (the one with the most distinct values) is customer_id. status probably has only a few distinct values. So customer_id should go first in the index. Try this.

ALTER TABLE my_table ADD INDEX customer_id_status (customer_id, status);

This creates a BTREE index. A useful mental model for such an index is an old-fashioned telephone book. It's sorted in order. You look up the first matching entry in the index, then scan it sequentially for the items you want.

You may also want to try running ANALYZE my_table; to update the statistics (about selectivity) used by the query planner to choose an appropriate index.

Pro tip Avoid SELECT * if you can. Instead name the columns you want. This can help performance a lot.

Pro tip Your question said some of your columns aren't relevant to query optimization. That's probably not true; index design is a weird art. SELECT * makes it less true.

Collectives™ on Stack Overflow

Postgres: Optimization of query with simple where clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related