From: Tom Lane <tgl@sss.pgh.pa.us>
To: Glyn Astill <glynastill@yahoo.co.uk>
Cc: Pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Index scan cost calculation
Date: Thu, 26 Nov 2015 13:26:27 -0500
Message-ID: <6786.1448562387@sss.pgh.pa.us> (raw)
In-Reply-To: <412855216.18274544.1448560255082.JavaMail.yahoo@mail.yahoo.com>
References: <31151.1448556291@sss.pgh.pa.us>
<412855216.18274544.1448560255082.JavaMail.yahoo@mail.yahoo.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Glyn Astill <glynastill@yahoo.co.uk> writes:
>> From: Tom Lane <tgl@sss.pgh.pa.us>
>> The problem will probably go away by itself as your table grows, but
>> if you don't want to wait, you might want to reflect on which of the index
>> columns might be (partially?) functionally dependent on the other columns,
>> and whether you could redesign the key structure to avoid that.
> Many thanks for the explanation, is such a functional dependency assumed purely based optimistically on statistics gathered by analyze?
Well, the point is that the selectivities associated with the individual
WHERE clauses are assumed independent, which allows us to just multiply
them together. If they're not really independent then multiplication
gives a combined selectivity that's too small. But without cross-column
statistics there's not much we can do to get a better estimate.
regards, tom lane
--
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: tgl@sss.pgh.pa.us, glynastill@yahoo.co.uk
Subject: Re: Index scan cost calculation
In-Reply-To: <6786.1448562387@sss.pgh.pa.us>
* 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