DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=ymail.com;
	h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=Mcd68a/6ksVNHWALbCBwMpHM/tb2bqDFxPZcdHYpEBM+Yp412DEN4SuJSU9DB+S9f8QS4ZHhlDxtoiNvY22XU1nlS2p3kmk4Y2xElp4KAFGYsYsGJSpjaWxIy+WD5wQ4Q3bUXRlH2vgSMLMtzYL4CGnxgMLETjmDFz6sg4eA5HI=;
References: <19B2B4ECC4724A438200C54257BA8B20@gmail.com>
Message-ID: <1361566076.10158.YahooMailNeo@web162905.mail.bf1.yahoo.com>
Date: Fri, 22 Feb 2013 12:47:56 -0800 (PST)
From: Kevin Grittner <kgrittn@ymail.com>
Reply-To: Kevin Grittner <kgrittn@ymail.com>
Subject: Re: Bad query plan with high-cardinality column
To: Alexander Staubo <alex@bengler.no>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
In-Reply-To: <19B2B4ECC4724A438200C54257BA8B20@gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Alexander Staubo <alex@bengler.no> wrote:

> This is my schema:
>
>=A0=A0 create table comments (
>=A0=A0=A0=A0 id serial primary key,
>=A0=A0=A0=A0 conversation_id integer,
>=A0=A0=A0=A0 created_at timestamp
>=A0=A0 );
>=A0=A0 create index comments_conversation_id_index on comments (conversati=
on_id);
>=A0=A0 create index comments_created_at_index on comments (created_at);

I suspect you would be better off without those two indexes, and
instead having an index on (conversation_id, created_at).=A0 Not just
for the query you show, but in general.

>=A0=A0 select comments.id from comments where
>=A0=A0=A0=A0 conversation_id =3D 3975979 order by created_at limit 13
>
> This filters about 5000 rows and returns the oldest 13 rows. But
> the query is consistently planned wrong:

> [planner thinks it will be cheaper to read index in ORDER BY
> sequence and filter rows until it has 13 than to read 5471 rows
> and sort them to pick the top 13 after the sort.]

In my experience these problems come largely from the planner not
knowing the cost of dealing with each tuple.=A0 I see a lot less of
this if I raise cpu_tuple_cost to something in the 0.03 to 0.05
range.

--=20
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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