From: Jim Nasby <Jim.Nasby@BlueTreble.com>
To: Jeff Janes <jeff.janes@gmail.com>
To: Glyn Astill <glynastill@yahoo.co.uk>
Cc: Pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Index scan cost calculation
Date: Wed, 2 Dec 2015 16:32:43 -0600
Message-ID: <565F718B.7080802@BlueTreble.com> (raw)
In-Reply-To: <CAMkU=1x4TcwsyjMubNcrxzOYgUxzGtxq7-58bb-tH80FVh_LgA@mail.gmail.com>
References: <CAMkU=1zy8syr9NkkJR+-d39Gjyg6uTdCvUqfOicvmjmL1EXTOQ@mail.gmail.com>
<1322503558.4427005.1448892201990.JavaMail.yahoo@mail.yahoo.com>
<CAMkU=1x4TcwsyjMubNcrxzOYgUxzGtxq7-58bb-tH80FVh_LgA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Jim.Nasby@BlueTreble.com, jeff.janes@gmail.com, glynastill@yahoo.co.uk
Subject: Re: Index scan cost calculation
In-Reply-To: <565F718B.7080802@BlueTreble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox