pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Glyn Astill <glynastill@yahoo.co.uk>
To: Jim Nasby <Jim.Nasby@BlueTreble.com>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: Pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Index scan cost calculation
Date: Thu, 3 Dec 2015 13:06:57 +0000 (UTC)
Message-ID: <1409411790.24325018.1449148017669.JavaMail.yahoo@mail.yahoo.com> (raw)
In-Reply-To: <565F718B.7080802@BlueTreble.com>
References: <565F718B.7080802@BlueTreble.com>
List-Unsubscribe:  <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>


> From: Jim Nasby <Jim.Nasby@BlueTreble.com>
>To: Jeff Janes <jeff.janes@gmail.com>; Glyn Astill <glynastill@yahoo.co.uk> 
>Cc: Pgsql-performance <pgsql-performance@postgresql.org>
>Sent: Wednesday, 2 December 2015, 22:32
>Subject: Re: [PERFORM] Index scan cost calculation
> 
>
>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.


Thanks, interesting idea, but no cigar.

For the moment just ensuring the seats_index01 is the last index created seems to suffice, fragile though it is.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



view thread (11+ messages)

Message-ID: <1409411790.24325018.1449148017669.JavaMail.yahoo@mail.yahoo.com>
Permalink:  ../1409411790.24325018.1449148017669.JavaMail.yahoo@mail.yahoo.com/
Also on:    postgresql.org/message-id/1409411790.24325018.1449148017669.JavaMail.yahoo@mail.yahoo.com

 · 

reply
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: glynastill@yahoo.co.uk, Jim.Nasby@BlueTreble.com, jeff.janes@gmail.com
  Subject: Re: Index scan cost calculation
  In-Reply-To: <1409411790.24325018.1449148017669.JavaMail.yahoo@mail.yahoo.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