Index scan cost calculation - Mailing list pgsql-performance

Hi All,

Using pg 9.4.5 I'm looking at a table set up by a 3rd party application and trying to figure out why a particular index
isbeing chosen over another for updates/deletes. 

From what I can see the reason is that plans using either index have the same exactly the same cost.  So rather I'm
askingif there's something glaringly obvious I'm missing, or is there anything I can to to get better estimates. 

The table is as follows and has  ~ 50M rows, ~ 4.5GB in size:

CREATE TABLE tickets.seats
(
  recnum serial NOT NULL,
  show numeric(8,0) NOT NULL,
  type numeric(4,0) NOT NULL,
  block character varying(8) NOT NULL,
  "row" numeric(14,0) NOT NULL,
  seat numeric(8,0) NOT NULL,
  flag character varying(15) NOT NULL,
  transno numeric(8,0) NOT NULL,
  best numeric(4,0) NOT NULL,
  "user" character varying(15) NOT NULL,
  "time" numeric(10,0) NOT NULL,
  date date NOT NULL,
  date_reserved timestamp NOT NULL
);

Indexes:
  "seats_index01" PRIMARY KEY, btree (show, type, best, block, "row", seat)              // (1094 MB)
  "seats_index00" UNIQUE, btree (recnum)                                                  // (2423 MB)
  "seats_index02" UNIQUE, btree (show, type, best, block, flag, "row", seat, recnum)      // (2908 MB)

default_statistics target is 100, and the following columns are non-default:

attname | attstattarget
--------+---------------
show      |          1000
type       |          1000
block    |          2000
row        |          1000
seat       |          1000
flag       |          1000
best       |          1000

Increasing these further appears to make no noticeable difference. (pg_stats here for these columns here:
http://pastebin.com/2WQQec7N)

An example query below shows that in some cases the seats_index02 index is being chosen:

# analyze verbose seats;
INFO:  analyzing "tickets.seats"
INFO:  "seats": scanned 593409 of 593409 pages, containing 50926456 live rows and 349030 dead rows; 600000 rows in
sample,50926456 estimated total rows 

# begin;
BEGIN
# explain analyze delete from seats where ("show" = 58919 AND "type" = 1 AND "best" = 10 AND "block" = 'GMA' AND "row"
=26AND "seat" = 15); 
QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Delete on seats  (cost=0.56..4.59 rows=1 width=6) (actual time=0.480..0.480 rows=0 loops=1)
->  Index Scan using seats_index02 on seats  (cost=0.56..4.59 rows=1 width=6) (actual time=0.452..0.453 rows=1 loops=1)
Index Cond: ((show = 58919::numeric) AND (type = 1::numeric) AND (best = 10::numeric) AND ((block)::text = 'GMA'::text)
AND("row" = 26::numeric) AND (seat = 15::numeric)) 
Planning time: 2.172 ms
Execution time: 0.531 ms
(5 rows)

But from my naive standpoint, seats_index01 is a better candidate:

# abort; begin;
ROLLBACK
BEGIN

# update pg_index set indisvalid = false where indexrelid = 'seats_index02'::regclass;
# explain analyze delete from seats where ("show" = 58919 AND "type" = 1 AND "best" = 10 AND "block" = 'GMA' AND "row"
=26AND "seat" = 15); 
QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Delete on seats  (cost=0.56..4.59 rows=1 width=6) (actual time=0.103..0.103 rows=0 loops=1)
->  Index Scan using seats_index01 on seats  (cost=0.56..4.59 rows=1 width=6) (actual time=0.078..0.080 rows=1 loops=1)
Index Cond: ((show = 58919::numeric) AND (type = 1::numeric) AND (best = 10::numeric) AND ((block)::text = 'GMA'::text)
AND("row" = 26::numeric) AND (seat = 15::numeric)) 
Planning time: 0.535 ms
Execution time: 0.146 ms
(5 rows)


In this instance, the time difference is not huge, however in some seemingly random cases where there are a lot of rows
withonly the "seat" column differing the choice of seats_index02 is much larger ~ 70ms vs 0.something ms with
seats_index01

I suspect some of the seemingly random cases could be where there's been an update, followed by a delete since the last
analyze,despite auto analyze running fairly frequently. 

Any suggestions appreciated.

Thanks
Glyn