CODEWITHSUNDEEP
postgresql
xxx_coder_noscope
xxx_coder_noscope

Reputation: 85

Composite primary key and slow queries

I have a table which looks like this:

  CREATE TABLE IF NOT EXISTS codes (
    code       character varying(255) NOT NULL,
    used       boolean NOT NULL DEFAULT FALSE,
    user_id    integer NOT NULL,
    order_id   integer,
    created    timestamp without time zone,
    CONSTRAINT pk_code PRIMARY KEY (code, user_id)
  );

And I have simple query:

SELECT COUNT(*) as "count" 
FROM "codes" "code" 
WHERE "code"."user_id" = {some_id} 
 AND "code"."order_id" IS NULL;

Which is very slow, explain analyze:

'Aggregate  (cost=3576.10..3576.11 rows=1 width=8) (actual time=1471.870..1471.871 rows=1 loops=1)'
'  ->  Seq Scan on codes (cost=0.00..3323.89 rows=50443 width=17) (actual time=0.139..203.139 rows=49998 loops=1)'
'        Filter: ((order_id IS NULL) AND (user_id = 10))'
'        Rows Removed by Filter: 116498'
'Planning Time: 1.450 ms'
'Execution Time: 1471.981 ms'

How can I optimize this query?

Upvotes: 0

Views: 1552

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 248245

An index can only be used efficiently if the first index column is used in the WHERE condition (yes, I know there are exceptions, but it is a good rule of thumb).

So to support that query, you should define the primary key the other way around, as (user_id, code). That will guarantee the constraint just as well, but the underlying index will support your query.

If you cannot change the primary key like that, you need another index on user_id alone.

But then, looking on the Rows Removed by Filter, probably a sequential scan is the fastest access strategy, and an index won't help at all. Try it out.

Upvotes: 2

Related Questions