To: pgsql-performance@postgresql.org
From: Thomas Kellerer <spam_eater@gmx.net>
Subject: Re: Thinking About Correlated Columns (again)
Date: Wed, 15 May 2013 22:31:46 +0200
Lines: 29
Message-ID: <kn0r9o$5je$1@ger.gmane.org>
References: <5193AA62.2040700@optionshouse.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de;
	rv:1.8.1.21) Gecko/20090302 Thunderbird/2.0.0.21 Mnenhy/0.7.5.666
In-Reply-To: <5193AA62.2040700@optionshouse.com>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Shaun Thomas wrote on 15.05.2013 17:31:
> Hi!
>
> This has been a pain point for quite a while. While we've had several
> discussions in the area, it always seems to just kinda trail off and
> eventually vanish every time it comes up.
>
> A really basic example of how bad the planner is here:
>
> CREATE TABLE foo AS
> SELECT a.id, a.id % 1000 AS col_a, a.id % 1000 AS col_b
>    FROM generate_series(1, 1000000) a(id);
>
> CREATE INDEX idx_foo_ab ON foo (col_a, col_b);
>
> Index Scan using idx_foo_ab on foo  (cost=0.00..6.35 rows=1 width=12)
>             (actual time=0.030..3.643 rows=1000 loops=1)
>    Index Cond: ((col_a = 50) AND (col_b = 50))
>
> Hey, look! The row estimate is off by a factor of 1000. This
> particular case doesn't suffer terribly from the mis-estimation, but
> others do. Boy, do they ever.

What happens if you create one index for each column? (instead of one combined index)

For your example it does not seem to improve the situation, but maybe things get better with the "bad" queries?


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