Subject: Re: Index scan cost calculation
To: Jeff Janes <jeff.janes@gmail.com>, Glyn Astill <glynastill@yahoo.co.uk>
References: 
 <CAMkU=1zy8syr9NkkJR+-d39Gjyg6uTdCvUqfOicvmjmL1EXTOQ@mail.gmail.com>
 <1322503558.4427005.1448892201990.JavaMail.yahoo@mail.yahoo.com>
 <CAMkU=1x4TcwsyjMubNcrxzOYgUxzGtxq7-58bb-tH80FVh_LgA@mail.gmail.com>
CC: Pgsql-performance <pgsql-performance@postgresql.org>
From: Jim Nasby <Jim.Nasby@BlueTreble.com>
Message-ID: <565F718B.7080802@BlueTreble.com>
Date: Wed, 2 Dec 2015 16:32:43 -0600
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:38.0)
 Gecko/20100101 Thunderbird/38.3.0
MIME-Version: 1.0
In-Reply-To: 
 <CAMkU=1x4TcwsyjMubNcrxzOYgUxzGtxq7-58bb-tH80FVh_LgA@mail.gmail.com>
Content-Type: text/plain; charset="utf-8"; format=flowed
Content-Transfer-Encoding: 7bit
Received-SPF: None (protection.outlook.com: bluetreble.com does not designate
 permitted sender hosts)
SpamDiagnosticOutput: 1:23
SpamDiagnosticMetadata: NSPM
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 02 Dec 2015 22:32:49.0390 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-Transport-CrossTenantHeadersStamped: CY1PR11MB0684
X-Pg-Spam-Score: -1.9 (-)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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