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=Jrq3w33Lo/2KZ05EHT0JqCy51n+2aEanszYWQPMDDEg5S7jwo2czWT5DSNlidijqRN
	StUQZ4OHuXTiHYvJnO9cY8hburNliRyB+DjQzVxZsdzCYSYX57lYUhUcrQ+NW9+Q5DbN
	N5M7vpOLS82evzWYQADIOXF6IPPm2NHLRwD1o=
MIME-Version: 1.0
In-Reply-To: <cd329af80903031105o754b328aid9c1ecdd60b44278@mail.gmail.com>
References: <cd329af80903030905y2efa15bdt123779ee8877df4a@mail.gmail.com>
	<603c8f070903030912t36c541ebr50b4576c7a9a5b65@mail.gmail.com>
	<cd329af80903030920x7ccb7b10l7a279a464cab5bd4@mail.gmail.com>
	<603c8f070903030940n3fb1e9eay53666d06cf489469@mail.gmail.com>
	<cd329af80903031105o754b328aid9c1ecdd60b44278@mail.gmail.com>
Date: Tue, 3 Mar 2009 20:16:12 +0100
Message-ID: <cd329af80903031116r1b55085ehaa73e4f1fe93eebe@mail.gmail.com>
Subject: Re: Problems with ordering (can't force query planner to
	use an index)
From: Sebastjan Trepca <trepca@gmail.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

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(#  AND "core_accessor"."slot_id" =3D 472 AND
"core_accessor"."label" =3D E'' AND "core_accessor"."publish_state" >=3D
60 AND
noovo-new(#  "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-# ;

       QUERY PLAN
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
----------------
 Limit  (cost=3D0.00..3709.56 rows=3D5 width=3D178) (actual
time=3D4593.867..4597.587 rows=3D5 loops=3D1)
   ->  Index Scan Backward using core_accessor_date_idx on
core_accessor  (cost=3D0.00..1810265.67 rows=3D2440 width=3D178) (actual
time=3D4593.866..4597.583 rows=3D5 loops=3D1)
         Filter: ((publish_state >=3D 60) AND (slot_type_id =3D 119) AND
(slot_id =3D 472) AND (label =3D ''::text) AND (role =3D 0) AND (user_id =
=3D
0))
 Total runtime: 4597.632 ms
(4 rows)


Sebastjan


On Tue, Mar 3, 2009 at 8:05 PM, Sebastjan Trepca <trepca@gmail.com> wrote:
> Still the same :/
>
> I raised the default_statistics_target to 600 (it was already 100). I
> then restarted pg, ran analyze through all tables and yet there is not
> effect.
> This is the output for core_accessor:
> INFO: =C2=A0analyzing "public.core_accessor"
> INFO: =C2=A0"core_accessor": scanned 291230 of 291230 pages, containing
> 17144315 live rows and 0 dead rows; 300000 rows in sample, 17144315
> estimated total rows
>
> It thinks there are even less rows in the set:
>
> -------------------------------------------------------------------------=
---------------------------------------------------------------------------=
-----------------
> =C2=A0Limit =C2=A0(cost=3D30816.49..30816.50 rows=3D5 width=3D855) (actua=
l
> time=3D683.907..683.910 rows=3D5 loops=3D1)
> =C2=A0 -> =C2=A0Sort =C2=A0(cost=3D30816.49..30822.29 rows=3D2321 width=
=3D855) (actual
> time=3D683.906..683.907 rows=3D5 loops=3D1)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 Sort Key: core_accessor.date_posted, core_acc=
essor.nooximity
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 Sort Method: =C2=A0top-N heapsort =C2=A0Memor=
y: 31kB
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 -> =C2=A0Nested Loop =C2=A0(cost=3D0.00..3077=
7.94 rows=3D2321 width=3D855)
> (actual time=3D0.072..517.970 rows=3D68505 loops=3D1)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -> =C2=A0Index Scan usin=
g core_accessor_fresh_idx on
> core_accessor =C2=A0(cost=3D0.00..8955.44 rows=3D2440 width=3D92) (actual
> time=3D0.056..53.107 rows=3D69312 loops=3D1)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Ind=
ex Cond: ((slot_id =3D 472) AND (slot_type_id =3D
> 119) AND (label =3D ''::text) AND (user_id =3D 0) AND (role =3D 0) AND
> (publish_state >=3D 60))
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -> =C2=A0Index Scan usin=
g core_base_pkey on core_base
> (cost=3D0.00..8.93 rows=3D1 width=3D763) (actual time=3D0.004..0.005 rows=
=3D1
> loops=3D69312)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Ind=
ex Cond: ((core_base.object_id =3D
> core_accessor.object_id) AND (core_base.content_type_id =3D
> core_accessor.content_type_id))
> =C2=A0Total runtime: 684.015 ms
> (10 rows)
>
>
>
>
>
> Sebastjan
>
>
>
> On Tue, Mar 3, 2009 at 6:40 PM, Robert Haas <robertmhaas@gmail.com> wrote=
:
>> On Tue, Mar 3, 2009 at 12:20 PM, Sebastjan Trepca <trepca@gmail.com> wro=
te:
>>> But it's already attached in the first mail or am I missing something?
>>>
>>> If you don't see it, check this: http://pastebin.com/d71b996d0
>>
>> Woops, sorry, I thought you had sent plain EXPLAIN. =C2=A0I see it now.
>>
>> The lowest level at which I see a problem is here:
>>
>> -> =C2=A0Index Scan using core_accessor_fresh_idx on core_accessor
>> (cost=3D0.00..5460.07 rows=3D2970 width=3D92) (actual time=3D0.068..54.9=
21
>> rows=3D69312 loops=3D1)
>> =C2=A0 =C2=A0Index Cond: ((slot_id =3D 472) AND (slot_type_id =3D 119) A=
ND (label =3D
>> ''::text) AND (user_id =3D 0) AND (role =3D 0) AND (publish_state >=3D 6=
0))
>>
>> For some reason it expect 2970 rows but gets 69312.
>>
>> A good place to start is to change your default_statistics_target
>> value to 100 in postgresql.conf, restart postgresql, and re-ANALYZE.
>>
>> ...Robert
>>
>