Change table scan in query plan to seek

I have an ORM generated query (Django) running against a local Postgres 9.1.11 instance that looks up ~5 records from two specific match fields. Due to the nature of the EDI load program, I need to execute this query many thousands of times. The planner insists on scanning the entire table for each query and the whole process is taking an unreasonably long time (1+ seconds per query). There has to be an index I can create to allow Postgres to seek to the few records I want upon each execution.

Query, generated by the ORM:

SELECT "dialer_callrequest"."id", "dialer_callrequest"."phone_number", "dialer_callrequest"."last_attempt_time"
FROM "dialer_callrequest"
WHERE ("dialer_callrequest"."campaign_id" = 36  AND "dialer_callrequest"."phone_number" IN ('15555555555'))
ORDER BY "dialer_callrequest"."call_time" DESC

There will always be exactly one "campaign_id", and 1-3 or so "phone_number"s to match in the IN clause. The problem seems to be in making the IN clause (which is often only one value) match an index field. The "campaign_id" field has low cardinality in this table, but combined with "phone_number" it is high.

```
CREATE TABLE dialer_callrequest
(
id serial NOT NULL,
user_id integer NOT NULL,
request_uuid character varying(120),
aleg_uuid character varying(120),
call_time timestamp with time zone NOT NULL,
created_date timestamp with time zone NOT NULL,
updated_date timestamp with time zone NOT NULL,
call_type integer,
status integer,
callerid character varying(80) NOT NULL,
phone_number character varying(80) NOT NULL,
timeout integer NOT NULL,
timelimit integer NOT NULL,
extra_dial_string character varying(500) NOT NULL,
subscriber_id integer,
campaign_id integer,
aleg_gateway_id integer,
content_type_id integer NOT NULL,
object_id integer NOT NULL,
completed boolean NOT NULL,
extra_data character varying(120) NOT NULL,
num_attempt integer NOT NULL,
last_attempt_time timestamp with time zone,
result ch

I'm not a Postgres guy, but with other DBs this can be a problem with the table statistics that the query planner relies on. Resetting those via an

ANALYZE TABLE

can resolve the issue. Your queries & indexes look correct.

http://www.postgresql.org/docs/9.0/static/sql-analyze.html

StackExchange Database Administrators Q#77393, answer score: 3