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> |
Subject: | Re: Index scan cost calculation |
Date: | 2015-12-02 22:32:43 |
Message-ID: | 565F718B.7080802@BlueTreble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Glyn Astill | 2015-12-03 13:06:57 | Re: Index scan cost calculation |
Previous Message | Glyn Astill | 2015-12-01 11:22:12 | Re: Index scan cost calculation |