Re: Index scan cost calculation

> From: Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com>
>To: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>; Glyn Astill <glynastill(at)yahoo(dot)co(dot)uk>
>Cc: Pgsql-performance <pgsql-performance(at)postgresql(dot)org>
>Sent: Wednesday, 2 December 2015, 22:32
>Subject: Re: [PERFORM] 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.