From: Tom Lane <tgl@sss.pgh.pa.us>
To: Piotr Gasidło <quaker@barbara.eu.org>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Wrong index selection
Date: Fri, 17 Jan 2014 19:33:44 -0500
Message-ID: <29113.1390005224@sss.pgh.pa.us> (raw)
In-Reply-To: <CAF8akQtr9ENiroROUmyRe_gpW5aDf-5WNOaewBSYRizvx6CPtA@mail.gmail.com>
References: <CAF8akQtr9ENiroROUmyRe_gpW5aDf-5WNOaewBSYRizvx6CPtA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
=?UTF-8?Q?Piotr_Gasid=C5=82o?= <quaker@barbara.eu.org> writes:
> [ planner prefers this: ]
> -> Index Scan using cookies2tags_key3 on cookies2tags
> co2ta (cost=0.57..49.34 rows=1 width=57) (actual time=38.339..38.982
> rows=8 loops=1)
> Index Cond: ((co2ta_cl_id = 97) AND (co2ta_ta_id =
> ANY ('{142}'::integer[])))
> Filter: (co2ta_co_id = ANY
> ('{1,123567429,123872617,123929118,123930244,123935996,123937156,123944495,123944999,123945469}'::integer[]))
> Rows Removed by Filter: 32120
> [ over this: ]
> -> Index Scan using cookies2tags_co_id_key2 on
> cookies2tags co2ta (cost=0.58..45.24 rows=1 width=57) (actual
> time=0.031..0.215 rows=8 loops=1)
> Index Cond: ((co2ta_co_id = ANY
> ('{123567429,123872617,123929118,123930244,123935996,123937156,123944495,123944999,123945469}'::integer[]))
> AND (co2ta_cl_id = 97))
> Filter: (co2ta_ta_id = ANY ('{142}'::integer[]))
> Rows Removed by Filter: 187
Well, as you can see the planner thinks these are going to cost about the
same, but actually the first one fetches a lot of rows that end up getting
rejected by the filter condition. That means the co2ta_ta_id condition
is somewhat redundant given the other two, much more so than the
co2ta_co_id condition is given the other two. If the individual
conditions are estimated about right (have you checked?), then that means
that this is an artifact of cross-column correlation statistics, which
unfortunately Postgres doesn't know anything about. Is there any way of
normalizing the data to reduce the cross-column correlations?
My other advice would be to simplify and reduce the set of indexes ---
IMO someone's gone way overboard with index creation here. It's unlikely
that those indexes are all pulling their weight for their maintenance
costs, and you can reduce problems with choosing the "wrong" index if
that index simply isn't there.
On the other hand, if this is a near-read-only table such that having lots
of indexes is basically free, you could fix the problem by creating an
index on all three columns, which should dominate both of these choices.
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, quaker@barbara.eu.org
Subject: Re: Wrong index selection
In-Reply-To: <29113.1390005224@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