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=ihumlZG/r6GRTVzorl/guRQNJGqrgmvuLuPM1NyjgB2PIu0LRkRvNSRK6TLAHQvpCh
	TVlHUQwYj6uRjicFSKDN9x77m1NpGA8ZgxhWGyulq82sf8VymBq28VkveJzqd8VNXDNU
	6gga5mS9aeXsDLRYrR8va2+PE4w5/m+W8BEx0=
MIME-Version: 1.0
In-Reply-To: <e7f9235d0903030934q3352b5ffs23ca0de97ab34252@mail.gmail.com>
References: <cd329af80903030905y2efa15bdt123779ee8877df4a@mail.gmail.com>
	<e7f9235d0903030934q3352b5ffs23ca0de97ab34252@mail.gmail.com>
Date: Tue, 3 Mar 2009 18:40:26 +0100
Message-ID: <cd329af80903030940v13bbbb41xaafc4dfa4c2ac228@mail.gmail.com>
Subject: Re: Problems with ordering (can't force query planner to
	use an index)
From: Sebastjan Trepca <trepca@gmail.com>
To: David Wilson <david.t.wilson@gmail.com>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Set statistics to 1000, reanalyzed and got exactly same results:


noovo-new=3D# explain analyze SELECT "core_accessor"."id",
"core_accessor"."content_type_id",
"core_accessor"."object_id", "core_accessor"."ordering",
"core_accessor"."label", "core_accessor"."date_posted",
"core_accessor"."publish_state", "core_accessor"."nooximity_old",
"core_accessor"."rising", "core_accessor"."nooximity",
"core_accessor"."nooximity_old_date_posted",
"core_accessor"."nooximity_date_posted", "core_accessor"."user_id",
"core_accessor"."slot_id", "core_accessor"."slot_type_id",
"core_accessor"."role", "core_base"."object_id",
"core_base"."content_type_id", "core_base"."abstract",
"core_base"."abstract_title", "core_base"."image",
 "core_base"."date_posted", "core_base"."date_modified",
"core_base"."date_expires", "core_base"."publish_state",
 "core_base"."location", "core_base"."location_x",
"core_base"."location_y", "core_base"."raw", "core_base"."author_id",
 "core_base"."excerpt", "core_base"."state_id",
"core_base"."country_id", "core_base"."language",
"core_base"."_identifier",
  "core_base"."slot_url", "core_base"."source_id",
"core_base"."source_content_type_id", "core_base"."source_type",
 "core_base"."source_value", "core_base"."source_title",
"core_base"."direct_to_source", "core_base"."comment_count",
 "core_base"."public" FROM "core_accessor" INNER JOIN core_base AS
core_base ON core_base.content_type_id =3D
 core_accessor.content_type_id AND core_base.object_id =3D
core_accessor.object_id WHERE (("core_accessor"."slot_type_id" =3D 119
 AND "core_accessor"."slot_id" =3D 472 AND "core_accessor"."label" =3D E''
AND "core_accessor"."publish_state" >=3D 60 AND
 "core_accessor"."role" IN (0) AND "core_accessor"."user_id" =3D 0))
order by core_accessor.date_posted, core_accessor.nooximity LIMIT 5
;

      QUERY PLAN
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------
 Limit  (cost=3D31716.13..31716.14 rows=3D5 width=3D860) (actual
time=3D711.340..711.343 rows=3D5 loops=3D1)
   ->  Sort  (cost=3D31716.13..31722.19 rows=3D2424 width=3D860) (actual
time=3D711.339..711.339 rows=3D5 loops=3D1)
         Sort Key: core_accessor.date_posted, core_accessor.nooximity
         Sort Method:  top-N heapsort  Memory: 31kB
         ->  Nested Loop  (cost=3D0.00..31675.87 rows=3D2424 width=3D860)
(actual time=3D0.076..544.039 rows=3D68505 loops=3D1)
               ->  Index Scan using core_accessor_fresh_idx on
core_accessor  (cost=3D0.00..9234.77 rows=3D2511 width=3D92) (actual
time=3D0.058..55.225 rows=3D69312 loops=3D1)
                     Index 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))
               ->  Index Scan using core_base_pkey on core_base
(cost=3D0.00..8.92 rows=3D1 width=3D768) (actual time=3D0.005..0.005 rows=
=3D1
loops=3D69312)
                     Index Cond: ((core_base.object_id =3D
core_accessor.object_id) AND (core_base.content_type_id =3D
core_accessor.content_type_id))
 Total runtime: 711.443 ms
(10 rows)


This is how I did it:

noovo-new=3D# alter table core_accessor alter column slot_id set statistics=
 1000;
ALTER TABLE
noovo-new=3D# alter table core_accessor alter column slot_type_id set
statistics 1000;
ALTER TABLE
noovo-new=3D# alter table core_accessor alter column label set statistics 1=
000;
ALTER TABLE
noovo-new=3D# alter table core_accessor alter column user_id set statistics=
 1000;
ALTER TABLE
noovo-new=3D# alter table core_accessor alter column role set statistics 10=
00;
ALTER TABLE
noovo-new=3D# alter table core_accessor alter column publish_state set
statistics 1000;
ALTER TABLE
noovo-new=3D# analyze core_accessor;
ANALYZE


Sebastjan


On Tue, Mar 3, 2009 at 6:34 PM, David Wilson <david.t.wilson@gmail.com> wro=
te:
> On Tue, Mar 3, 2009 at 12:05 PM, Sebastjan Trepca <trepca@gmail.com> wrot=
e:
>
>> =C2=A0 =C2=A0 =C2=A0 =C2=A0 -> =C2=A0Nested Loop =C2=A0(cost=3D0.00..318=
83.13 rows=3D2861 width=3D860)
>> (actual time=3D0.089..543.497 rows=3D68505 loops=3D1)
>> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -> =C2=A0Index Scan usi=
ng core_accessor_fresh_idx on
>> core_accessor =C2=A0(cost=3D0.00..5460.07 rows=3D2970 width=3D92) (actua=
l
>> time=3D0.068..54.921 rows=3D69312 loops=3D1)
>> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 In=
dex 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))
>
> That index scan on core_accessor_fresh_idx has a pretty big disparity
> between what the planer expects to get (2970 rows) and what it
> actually gets (69312 rows). You should try increasing the statistics
> target if you haven't, then re-analyze and try the query again to see
> if the planner picks something better. The default of 10 is pretty
> small- try 100, or higher.
>
>
>
> --
> - David T. Wilson
> david.t.wilson@gmail.com
>