Re: Index scan cost calculation

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux