Re: Index scan cost calculation

On Thu, Nov 26, 2015 at 8:11 AM, Glyn Astill <glynastill(at)yahoo(dot)co(dot)uk> wrote:
> 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 is being 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 asking if 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)

Why does the index seats_index02 exist in the first place? It looks
like an index designed for the benefit of a single query. In which
case, could flag column be moved up front? That should prevent it
from looking falsely enticing.

A column named "flag" is not usually the type of thing you expect to
see a range query on, so moving it leftward in the index should not be
a problem.

Cheers,

Jeff