3

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 character varying(180) NOT NULL,
  hangup_cause character varying(80) NOT NULL,
  parent_callrequest_id integer,
  caller_name character varying(80) NOT NULL,
  alarm_request_id integer,
  CONSTRAINT dialer_callrequest_pkey PRIMARY KEY (id),
  CONSTRAINT aleg_gateway_id_refs_id_8b3a1b12 FOREIGN KEY (aleg_gateway_id)
      REFERENCES dialer_gateway (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT campaign_id_refs_id_4cae9c73 FOREIGN KEY (campaign_id)
      REFERENCES dialer_campaign (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT content_type_id_refs_id_3b560776 FOREIGN KEY (content_type_id)
      REFERENCES django_content_type (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT parent_callrequest_id_refs_id_5095e329 FOREIGN KEY (parent_callrequest_id)
      REFERENCES dialer_callrequest (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT subscriber_id_refs_id_18533096 FOREIGN KEY (subscriber_id)
      REFERENCES dialer_subscriber (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT user_id_refs_id_c0fdfcda FOREIGN KEY (user_id)
      REFERENCES auth_user (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT dialer_callrequest_object_id_check CHECK (object_id >= 0)
)
WITH (
  OIDS=FALSE
);

I have tried each of these, and the same scan heap plan is always chosen.

CREATE INDEX dialer_callrequest_campaign_phone ON dialer_callrequest USING btree (campaign_id,phone_number,call_time DESC,id,last_attempt_time);
CREATE INDEX dialer_callrequest_campaign_phone2 ON dialer_callrequest USING btree (campaign_id,phone_number varchar_pattern_ops,call_time DESC,id,last_attempt_time);
CREATE INDEX dialer_callrequest_campaign_phone3 ON dialer_callrequest USING btree (campaign_id,phone_number text_pattern_ops,call_time DESC,id,last_attempt_time);
vacuum analyze verbose dialer_callrequest;

Scan plan:

Sort  (cost=56636.25..56636.26 rows=3 width=32) (actual time=486.257..486.260 rows=13 loops=1)
  Output: id, phone_number, last_attempt_time, call_time
  Sort Key: dialer_callrequest.call_time
  Sort Method: quicksort  Memory: 26kB
  Buffers: shared hit=6654 read=30574
  ->  Seq Scan on public.dialer_callrequest  (cost=0.00..56636.23 rows=3 width=32) (actual time=51.668..486.195 rows=13 loops=1)
        Output: id, phone_number, last_attempt_time, call_time
        Filter: ((dialer_callrequest.campaign_id = 36) AND ((dialer_callrequest.phone_number)::text = '15555555555'::text))
        Buffers: shared hit=6654 read=30574
Total runtime: 486.306 ms

If I constrain the plans to disable seqscans (set enable_seqscan=False), it will index range scan on the first key, "campaign_id" but not "phone_number", which really doesn't help any.

Constrained plan:

Sort  (cost=61012.72..61012.72 rows=3 width=32) (actual time=510.786..510.788 rows=13 loops=1)
  Output: id, phone_number, last_attempt_time, call_time
  Sort Key: dialer_callrequest.call_time
  Sort Method: quicksort  Memory: 26kB
  Buffers: shared read=34740 written=22
  ->  Bitmap Heap Scan on public.dialer_callrequest  (cost=17051.33..61012.69 rows=3 width=32) (actual time=116.940..510.712 rows=13 loops=1)
        Output: id, phone_number, last_attempt_time, call_time
        Recheck Cond: (dialer_callrequest.campaign_id = 36)
        Filter: ((dialer_callrequest.phone_number)::text = '15555555555'::text)
        Buffers: shared read=34740 written=22
        ->  Bitmap Index Scan on dialer_callrequest_campaign_id  (cost=0.00..17051.33 rows=448891 width=0) (actual time=69.132..69.132 rows=440732 loops=1)
              Index Cond: (dialer_callrequest.campaign_id = 36)
              Buffers: shared read=3119 written=12
Total runtime: 510.825 ms

I'm at a loss as to why it won't use an index for the "phone_number" lookup.

Edit:

It's definitely the "IN" that is causing problems. I tried changing the "phone_number" condition to a straight "=" and it will use the 3rd index then (and maybe others), seeks both fields, no need to touch the heap. 34ms execute time from a remote host instead of 500ms+. That doesn't really work in my case as the program may need to look up multiple values through the ORM. I would think that the IN clause is equivalent to "=" for a single match, but that does not seem to be the case.

Edit 2: more weirdness

I tried the suggestion of putting "phone_number" first in the index, and when I asked PG to EXPLAIN the query, now it is using the plan I want (one step index range scan with both args, no sort, no heap access), but with the "dialer_callrequest_campaign_phone2" index, which is the one I had yesterday!

At this point it seems to be a statistic problem. I thought "ANALYZE " was supposed to fix that?

Is there a way I can ensure it won't revert back to the bad plan in the future?

Current EXPLAIN:

Index Scan using dialer_callrequest_campaign_phone2 on public.dialer_callrequest  (cost=0.00..15.64 rows=3 width=32) (actual time=0.057..0.057 rows=0 loops=1)
  Output: id, phone_number, last_attempt_time, call_time
  Index Cond: ((dialer_callrequest.campaign_id = 36) AND ((dialer_callrequest.phone_number)::text = '15555555555'::text))
  Buffers: shared hit=2 read=2
Total runtime: 0.142 ms
3

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

| improve this answer | |
  • Tried that several times. The "vacuum analyze verbose" does that and also maintains the table. Doesn't help. – Chris Smith Sep 23 '14 at 20:15
  • This worked for me! – jkp Mar 10 at 13:26
2

If the IN value list is only up to 3 items, you can rewrite with UNION. The query will have to be build dynamically, either in some other language or with dynamic SQL but it will have only equiality conditions and should not do a table scan:

SELECT id, phone_number, last_attempt_time
FROM
(   SELECT id, phone_number, last_attempt_time, call_time
    FROM dialer_callrequest AS dc
    WHERE campaign_id = 36 
      AND phone_number = '15555555555'
  UNION ALL
    SELECT id, phone_number, last_attempt_time, call_time
    FROM dialer_callrequest AS dc
    WHERE campaign_id = 36 
      AND phone_number = '16666666666'
  UNION ALL
    SELECT id, phone_number, last_attempt_time, call_time
    FROM dialer_callrequest AS dc
    WHERE campaign_id = 36 
      AND phone_number = '17777777777'
 ) AS t
ORDER BY call_time DESC ;

This might be worth trying as well:

WITH phones (phone_number) AS
    (VALUES ('15555555555'), ('16666666666'), ('17777777777'))
SELECT 
    dc.id, dc.phone_number, dc.last_attempt_time
FROM
    phones AS ph
  JOIN 
    dialer_callrequest AS dc
      ON dc.phone_number = ph.phone_number
WHERE 
    dc.campaign_id = 36 
ORDER BY 
    dc.call_time DESC ;
| improve this answer | |
  • I was trying not to bypass the ORM by doing a custom query. A simpler answer in that case would be to put the phone number conditions as a set of OR alternatives. i.e. (pn='123' OR pn='456'). I'm really disappointed that Postgres doesn't consider that equivalent to the "IN" though, or equivalent to "=" when there is only one arg. – Chris Smith Sep 24 '14 at 21:26
  • Can't Django be lured to generate the queries like this? If it can't, why don't you want to use raw SQL? – ypercubeᵀᴹ Sep 24 '14 at 21:32
  • Raw SQL turned out to be unnecessary... in my last edit now it is using the right plan, no changes that I made since yesterday except to add a new index that it isn't even using. Seems to be a stat problem, don't know why it changed its mind. Don't know how to keep it from changing it back. – Chris Smith Sep 24 '14 at 21:41
2

Since phone_number is the field with high cardinality, I would try putting an index on that.

If your performance is still poor, I would try adding campaign_id and the other output fields (id, last_attempt_time, call_time) to your index, so there's no need to hit the actual table at all.

| improve this answer | |
  • Um, in the CREATE INDEX statements, I already have all the required columns included. When I replace the "IN" with "=" it does not need to hit the actual table. This is a problem where "IN" with one arg is not handled the same as "=". See the edit. – Chris Smith Sep 24 '14 at 21:22
  • You do have broad indices, but not in the right order. Putting your most selective fields first can make an enormous difference in an index's effectiveness. – Jon of All Trades Sep 24 '14 at 21:26
  • Not if the planner doesn't consider the index field usable for the query. – Chris Smith Sep 24 '14 at 21:30
  • Certainly. I hope that's not what's happening when you use IN; disallowing index use when a filter is expressed this way seems like a poor choice, especially if you can't use index hints! If that's the case, you may have to fall back on the UNIONs idea (but I'd try the high-cardinality index first). – Jon of All Trades Sep 24 '14 at 22:13
  • It decided to start using one of my previous indexes on its own, no change to the query. See last edit. I may end up changing the query to something more LIKELY to generate the right plan, like a UNION ALL group or set of OR conditions. If the QO changes its mind again... – Chris Smith Sep 25 '14 at 17:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.