> From: Jim Nasby <Jim.Nasby@xxxxxxxxxxxxxx> >To: Jeff Janes <jeff.janes@xxxxxxxxx>; Glyn Astill <glynastill@xxxxxxxxxxx> >Cc: Pgsql-performance <pgsql-performance@xxxxxxxxxxxxxx> >Sent: Wednesday, 2 December 2015, 22:32 >Subject: Re: Index scan cost calculation > > >On 11/30/15 5:03 PM, Jeff Janes wrote: >> It thinks the combination of (show, type, best, block) is enough to >> get down to a single row. One index adds "flag" to that (which is not >> useful to the query) and the other adds "row" to that, which is useful >> but the planner doesn't think it is because once you are down to a >> single tuple additional selectivity doesn't help. > >It occurs to me that maybe you could force this behavior by building an >index on a row() instead of on the individual fields. IE: > >CREATE INDEX ... ON( row(show, type, best, block, row) ) > >You would then have to query based on that: > >WHERE row(show, type, best, block, row) = row( 'Trans Siberian >Orchestra', 'Music', true, 1, 1 ) > >You mentioned legacy code which presumably you can't modify to do that, >but maybe there's a way to trick the planner into it with a view... > >CREATE VIEW AS >SELECT r.show, r.type, r..., etc, etc > FROM ( SELECT *, row(show, type, best, block, row) AS r FROM table ) a >; > >When you stick a where clause on that there's a chance it'd get turned >into WHERE row() = row()... but now that I see it I'm probably being >over optimistic about that. You could probably force the issue with an >ON SELECT ON table DO INSTEAD rule, but IIRC those aren't supported. Thanks, interesting idea, but no cigar. For the moment just ensuring the seats_index01 is the last index created seems to suffice, fragile though it is. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
