CODEWITHSUNDEEP
postgresqlindexing
Hawkster
Hawkster

Reputation: 51

Postgres update not using primary key index

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

Upvotes: 1

Views: 1285

Answers (1)

Hawkster
Hawkster

Reputation: 51

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

Upvotes: 4

Related Questions