On Thu, Nov 26, 2015 at 8:11 AM, Glyn Astill <glynast...@yahoo.co.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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
