Postgres update not using primary key index

Question

I have an issue with our new Postgres App in testing. When we run an update using the primary key, it looks to use a sequence scan on the primary key instead of the index. The table is to large to warrant using a full scan, so i'm unsure why it's choosing this method.

pdns=# \d+ pdns.meta_data
Table "schema.table"
  Column   |          Type           | 
 id        | integer                 | 
 block_id  | integer                 | 
 feed_name | character varying(100)  | 
 metadata  | character varying(1000) | 
Indexes:
    "table_pkey" PRIMARY KEY, btree (id)

pdns=# select * from pg_stat_all_tables where schemaname not like 'pg%';
-[ RECORD 1 ]-------+------------------------------
relid               | 16403
schemaname          | schema
relname             | table
seq_scan            | 26720      <------ Times sequence scan is used
seq_tup_read        | 87233270740
idx_scan            | 1          <------ Times Index scan is used
idx_tup_fetch       | 0
n_tup_ins           | 26714
n_tup_upd           | 26714
n_tup_del           | 0
n_tup_hot_upd       | 2407
n_live_tup          | 3278722           <------ Actual rows
n_dead_tup          | 2122
n_mod_since_analyze | 2196
last_vacuum         | 2018-08-16 14:51:12.559508+00
last_autovacuum     | 2018-08-16 15:37:55.617413+00
last_analyze        | 2018-08-16 15:10:38.768696+00
last_autoanalyze    | 2018-08-16 15:38:55.205594+00
vacuum_count        | 1
autovacuum_count    | 4
analyze_count       | 2
autoanalyze_count   | 8

You can see that it's doing huge amount of seq_scans and no idex scans (that one was me doing it manually). the query is run using bind variables. The query as called by the app is:

postgres=# SELECT   min_time, max_time,  calls,  query FROM pg_stat_statements WHERE query like'%TABLE%';
 min_time  |  max_time  | calls |                                     query
-----------+------------+-------+--------------------------------------------------------------------------------
           |            |       | RETURNING *
 604.26355 | 2447.10553 | 31591 | update SCHEMA.TABLE set block_id=$1 where id=$2
(1 rows)    
postgres=#

However when I run it manually, I get index scan:

pdns=# prepare stmt(int) as update SCHEMA.TABLE set block_id=$1 where id=$2;
PREPARE
pdns=# explain execute stmt(1,2);
QUERY PLAN | Update on table (cost=0.43..8.45 rows=1 width=102)
QUERY PLAN |   ->  Index Scan using table_pkey on table(cost=0.43..8.45 rows=1 width=102)
QUERY PLAN |         Index Cond: (id = 2)

It's just too weird. Any ideas would be hugely appreciated.

many thanks H

I'm guessing that your application (or the ORM that it uses) is doing something shady. Try enabling auto_explain and debugging your slow, sequential-scanning queries in production. — Linas Valiukas
– Linas Valiukas, Commented Aug 16, 2018 at 17:13
Thankyou, yes, I've just done that: Query Text: update SCHEMA.TABLE set block_id=$1 where id=$2 Update on table (cost=0.00..110291.14 rows=17624 width=102) > Seq Scan on table (cost=0.00..110291.14 rows=17624 width=102) Filter: ((id)::numeric = '3525375'::numeric) So yes, when going through the app, it definately is using a sequential scan. As the sql is self contained, can there be an outside influence to make it choose a sequential scan? — Hawkster
– Hawkster, Commented Aug 17, 2018 at 7:45
I believe it's using a sequential scan because it thinks that there it's pulling out far more rows (17624) and so a seq scan is more optimal. But as the search column is a primary key, it will only have a single entry so I'm not sure if it's a case of stats being wrong. I have already gathered statistics on this table. — Hawkster
– Hawkster, Commented Aug 17, 2018 at 8:11
Really weird! Try: 1) running VACUUM ANALYZE on the table; 2) fiddling with random_page_cost and cpu_index_tuple_cost, maybe they’re way off (or your app sets those two to custom values on startup - just guessing here); 3) recreating the offending index; 4) SET enable_seqscan = off (but just as a debugging measure) — Linas Valiukas
– Linas Valiukas, Commented Aug 17, 2018 at 8:12

Hawkster · Accepted Answer · 2018-08-17 12:50:55Z

4

We found the answer in the end, and it's actually in the auto_explain logs:

Query Text: update SCHEMA.TABLE set block_id=$1 where id=$2 Update on table 
(cost=0.00..110291.14 rows=17624 width=102) > Seq Scan on table (cost=0.00..110291.14 
rows=17624 width=102) Filter: ((id)::numeric = '3525375'::numeric)

The key is

((id)::numeric = '3525375'::numeric)

This shows that the query is searching on a conversion of integer from numeric.

The app was actually putting data in as Numeric when the column type was integer. As such the insert statement was doing an implicit data type change which then caused the index not to be used. It was fixed by changing the app to use an integer instead of numeric data type Phew. Hope this helps other people. Thanks for your suggestions Linas

edited Aug 17, 2018 at 12:50

answered Aug 17, 2018 at 12:45

Hawkster

516 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Linas Valiukas Over a year ago

Good catch, didn’t notice ::numeric!

Collectives™ on Stack Overflow

Postgres update not using primary key index

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related