DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=v49qK4msPae2N2eFImgOh3L/wb3ZC6306rF/VwWLtt1UdGEnpZ6lSh/8JwfCju399F
	IdEWoPFFt4kefO+BAhAGabp5gu21JFzRMCEV2DXw8EZo0U/YWJs7WHFxPWQxHnkCCt7K
	RBXq7ALlbslaLmf4B0cbT2FDYuWXq4th9gWVE=
MIME-Version: 1.0
In-Reply-To: <cd329af80903031116r1b55085ehaa73e4f1fe93eebe@mail.gmail.com>
References: <cd329af80903030905y2efa15bdt123779ee8877df4a@mail.gmail.com>
	<603c8f070903030912t36c541ebr50b4576c7a9a5b65@mail.gmail.com>
	<cd329af80903030920x7ccb7b10l7a279a464cab5bd4@mail.gmail.com>
	<603c8f070903030940n3fb1e9eay53666d06cf489469@mail.gmail.com>
	<cd329af80903031105o754b328aid9c1ecdd60b44278@mail.gmail.com>
	<cd329af80903031116r1b55085ehaa73e4f1fe93eebe@mail.gmail.com>
Date: Tue, 3 Mar 2009 15:27:12 -0500
Message-ID: <603c8f070903031227p4e849d02peea05784f4b3d1f5@mail.gmail.com>
Subject: Re: Problems with ordering (can't force query planner to
	use an index)
From: Robert Haas <robertmhaas@gmail.com>
To: Sebastjan Trepca <trepca@gmail.com>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Tue, Mar 3, 2009 at 2:16 PM, Sebastjan Trepca <trepca@gmail.com> wrote:
> Maybe this is useful, I removed the JOIN and it uses other
> index(core_accessor_date_idx indexes (date_posted, nooximity)), but
> its still hardly any better:
>
> noovo-new=3D# explain analyze SELECT * FROM "core_accessor" WHERE
> (("core_accessor"."slot_type_id" =3D 119
> noovo-new(# =A0AND "core_accessor"."slot_id" =3D 472 AND
> "core_accessor"."label" =3D E'' AND "core_accessor"."publish_state" >=3D
> 60 AND
> noovo-new(# =A0"core_accessor"."role" IN (0) AND
> "core_accessor"."user_id" =3D 0)) ORDER BY "core_accessor"."date_posted"
> DESC, "core_accessor"."nooximity" DESC LIMIT 5
> noovo-new-# ;
>
> =A0 =A0 =A0 QUERY PLAN
> -------------------------------------------------------------------------=
---------------------------------------------------------------------------=
------------------
> =A0Limit =A0(cost=3D0.00..3709.56 rows=3D5 width=3D178) (actual
> time=3D4593.867..4597.587 rows=3D5 loops=3D1)
> =A0 -> =A0Index Scan Backward using core_accessor_date_idx on
> core_accessor =A0(cost=3D0.00..1810265.67 rows=3D2440 width=3D178) (actua=
l
> time=3D4593.866..4597.583 rows=3D5 loops=3D1)
> =A0 =A0 =A0 =A0 Filter: ((publish_state >=3D 60) AND (slot_type_id =3D 11=
9) AND
> (slot_id =3D 472) AND (label =3D ''::text) AND (role =3D 0) AND (user_id =
=3D
> 0))
> =A0Total runtime: 4597.632 ms
> (4 rows)
>
>
> Sebastjan

Well, in that case, you are being bitten by the fact that our
multi-column selectivity estimates are not very good.  The planner has
good information on how each column behaves in isolation, but not how
they act together.  I've found this to be a very difficult problem to
fix.

Which of the parameters in this query vary and which ones are
typically always the same?  Sometimes you can improve things by
creating an appropriate partial index.

...Robert