Query planner using a primary key index instead of a more targeted column index when adding order by primary key

Question

Please excuse simplification of actual query. Just to make it readable. Currently having slowdowns in our queries when adding order by primary key.

select id, field1, field2
from table1
where field1 = 'value'
limit 1000;

So having an index for field1, this query uses that index, which makes the query faster. I can trace that the query planner uses that index via the explain command.

Adding an order by suddenly changes the index used to the primary key index though. Which makes the query a lot slower.

select id, field1, field2
from table1 where field1 = 'value'
order by id asc
limit 1000;

Is there a way to force the query planner to use the field1 index?

EDIT: Actual table detail:

\d fax_message
                     Table "public.fax_message"
          Column          |            Type             | Modifiers 
--------------------------+-----------------------------+-----------
 id                       | bigint                      | not null
 broadcast_ref            | character varying(255)      | 
 busy_retries             | integer                     | 
 cli                      | character varying(255)      | 
 dncr                     | boolean                     | not null
 document_set_id          | bigint                      | not null
 fax_broadcast_attempt_id | bigint                      | 
 fps                      | boolean                     | not null
 header_format            | character varying(255)      | 
 last_updated             | timestamp without time zone | not null
 max_fax_pages            | integer                     | 
 message_ref              | character varying(255)      | 
 must_be_sent_before_date | timestamp without time zone | 
 request_id               | bigint                      | 
 resolution               | character varying(255)      | 
 retries                  | integer                     | 
 send_from                | character varying(255)      | 
 send_ref                 | character varying(255)      | 
 send_to                  | character varying(255)      | not null
 smartblock               | boolean                     | not null
 status                   | character varying(255)      | not null
 time_zone                | character varying(255)      | 
 total_pages              | integer                     | 
 user_id                  | uuid                        | not null
 delay_status_check_until | timestamp without time zone | 
 version                  | bigint                      | default 0
 cost                     | numeric(40,10)              | default 0
Indexes:
    "fax_message_pkey" PRIMARY KEY, btree (id)
    "fax_message_broadcast_ref_idx" btree (broadcast_ref)
    "fax_message_delay_status_check_until" btree (delay_status_check_until)
    "fax_message_document_set_idx" btree (document_set_id)
    "fax_message_fax_broadcast_attempt_idx" btree (fax_broadcast_attempt_id)
    "fax_message_fax_document_set_idx" btree (document_set_id)
    "fax_message_message_ref_idx" btree (message_ref)
    "fax_message_request_idx" btree (request_id)
    "fax_message_send_ref_idx" btree (send_ref)
    "fax_message_status_fax_broadcast_attempt_idx" btree (status, fax_broadcast_attempt_id)
    "fax_message_user" btree (user_id)
Foreign-key constraints:
    "fk2881c4e5106ed2de" FOREIGN KEY (request_id) REFERENCES core_api_send_fax_request(id)
    "fk2881c4e5246f3088" FOREIGN KEY (document_set_id) REFERENCES fax_document_set(id)
    "fk2881c4e555aad98b" FOREIGN KEY (user_id) REFERENCES users(id)
    "fk2881c4e59920b254" FOREIGN KEY (fax_broadcast_attempt_id) REFERENCES fax_broadcast_attempt(id)
Referenced by:
    TABLE "fax_message_status_modifier" CONSTRAINT "fk2dfbe52acb955ec1" FOREIGN KEY (fax_message_id) REFERENCES fax_message(id)
    TABLE "fax_message_attempt" CONSTRAINT "fk82058973cb955ec1" FOREIGN KEY (fax_message_id) REFERENCES fax_message(id)

Actual index used:

\d fax_message_status_fax_broadcast_attempt_idx
         Index "public.fax_message_status_fax_broadcast_attempt_idx"
          Column          |          Type          |        Definition        
--------------------------+------------------------+--------------------------
 status                   | character varying(255) | status
 fax_broadcast_attempt_id | bigint                 | fax_broadcast_attempt_id
btree, for table "public.fax_message"

Real queries:

With order by:

explain select this_.id as id65_0_, this_.version as version65_0_, this_.broadcast_ref as broadcast3_65_0_, this_.busy_retries as busy4_65_0_, this_.cli as cli65_0_, this_.cost as cost65_0_, this_.delay_status_check_until as delay7_5_0_, this_.dncr as dncr65_0_, this_.document_set_id as document9_65_0_, this_.fax_broadcast_attempt_id as fax10_65_0_, this_.fps as fps65_0_, this_.header_format as header12_65_0_, this_.last_updated as last13_65_0_, this_.max_fax_pages as max14_65_0_, this_.message_ref as message15_65_0_, this_.must_be_sent_before_date as must16_65_0_, this_.request_id as request17_65_0_, this_.resolution as resolution65_0_, this_.retries as retries65_0_, this_.send_from as send20_65_0_, this_.send_ref as send21_65_0_, this_.send_to as send22_65_0_, this_.smartblock as smartblock65_0_, this_.status as status65_0_, this_.time_zone as time25_65_0_, this_.total_pages as total26_65_0_, this_.user_id as user27_65_0_ from fax_message this_ where this_.status='TO_CHARGE_GROUP' order by id asc limit 1000;
                                                 QUERY PLAN                                                  
-------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..53956.06 rows=1000 width=2234)
   ->  Index Scan using fax_message_pkey on fax_message this_  (cost=0.43..2601902.61 rows=48223 width=2234)
         Filter: ((status)::text = 'TO_CHARGE_GROUP'::text)
(3 rows)

This one without the order by:

explain select this_.id as id65_0_, this_.version as version65_0_, this_.broadcast_ref as broadcast3_65_0_, this_.busy_retries as busy4_65_0_, this_.cli as cli65_0_, this_.cost as cost65_0_, this_.delay_status_check_until as delay7_5_0_, this_.dncr as dncr65_0_, this_.document_set_id as document9_65_0_, this_.fax_broadcast_attempt_id as fax10_65_0_, this_.fps as fps65_0_, this_.header_format as header12_65_0_, this_.last_updated as last13_65_0_, this_.max_fax_pages as max14_65_0_, this_.message_ref as message15_65_0_, this_.must_be_sent_before_date as must16_65_0_, this_.request_id as request17_65_0_, this_.resolution as resolution65_0_, this_.retries as retries65_0_, this_.send_from as send20_65_0_, this_.send_ref as send21_65_0_, this_.send_to as send22_65_0_, this_.smartblock as smartblock65_0_, this_.status as status65_0_, this_.time_zone as time25_65_0_, this_.total_pages as total26_65_0_, this_.user_id as user27_65_0_ from fax_message this_ where this_.status='TO_CHARGE_GROUP'  limit 1000;
                                                              QUERY PLAN                                                               
---------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..1744.13 rows=1000 width=2234)
   ->  Index Scan using fax_message_status_fax_broadcast_attempt_idx on fax_message this_  (cost=0.56..84080.59 rows=48223 width=2234)
         Index Cond: ((status)::text = 'TO_CHARGE_GROUP'::text)
(3 rows)

The cost on the query that used the fax_message_pkey is greater than the max cost of the query that used fax_message_status_fax_broadcast_attempt_idx. I was hoping that the query will still use the fax_message_status_fax_broadcast_attempt_idx index even with the order by there.

`Please excuse simplification of actual query. Just to make it readable.` It would be more readable if you posted the real query. Plus the DDL, plus some sample data (or of least an indicatien of the cardinalities involved). Plus the intended result. — wildplasser, May 15 '17 at 23:07
@wildplasser Added the actual queries, hopefully that helps. — froi, May 15 '17 at 23:22
Just a guess, but your index on `field` probably isn't very specific. Are there a lot of duplicate values in it? You could try increasing the index statistics. If your query is always for a single value you could create a partial index that matches it. — teppic, May 16 '17 at 00:59
@EvanCarroll was not able to use analyze since it runs the actual query, and it will take a while to execute. Don't want to put in more load. — froi, May 16 '17 at 01:04
@froi that's fine but we can't do anything with a query plan that you admit is wrong unless we know why and what the better plan looks like. Both of those require `EXPLAIN ANALYZE` — Evan Carroll, May 16 '17 at 01:05
@EvanCarroll understood, sorry about that, what do you think I can supply to better detail the question? happy to add that in. — froi, May 16 '17 at 01:06
`EXPLAIN ANALYZE` on the failed query, and the successful one or we can't help any further than what we've given. We have to have a test case, or see what you've got. — Evan Carroll, May 16 '17 at 01:08
Probably a dumb suggestion, but in any case - It might be worth doing a cleanup of the table and indexes in question (ie VACUUM ANALYZE and/or VACUUM FULL and index rebuild) - that should, if I remember correctly, update the table and index statistics the query planner relies on to make the plan. And if it starts with wrong idea about your table, how could it possibly come with a good plan? — Lukáš Říha, May 16 '17 at 08:53

score 0 · Answer 1 · edited May 23 '17 at 11:54

0

According to How do I force Postgres to use a particular index? (and links from answers there) there does not seem to be a way to force use of particular index .

edited May 23 '17 at 11:54

Community

1
1

answered May 15 '17 at 23:58

Lukáš Říha

141
4

score 0 · Answer 2 · answered May 16 '17 at 00:42

0

CTEs are a optimization fence. You're not giving us enough information to tell you why your query is getting planned wrong, but this should work if you don't care to actually fix the problem.

WITH t AS (

    select id, field1, field2
    from table1
    where field1 = 'value'
    limit 1000

)
SELECT *
FROM t  
order by id asc;

answered May 16 '17 at 00:42

Evan Carroll

78,363
46
261
468

1

But wouldn't the query in your answer _possibly_ provide a different result set that the one with ORDER BY by @froi? Let me rephrase (your example): take _any_ 1000 rows where field1 = 'value' and order those by id asc - as opposed to (original example): order rows by id desc and take first 1000 that fullfills field1 = 'value' condition? – Lukáš Říha May 16 '17 at 08:45

Query planner using a primary key index instead of a more targeted column index when adding order by primary key

2 Answers2