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.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
