Re: [PERFORM] Problems with ordering (can't force query planner to use an index)

2009-03-03 Thread Tom Lane
Sebastjan Trepca writes: > It thinks there are even less rows in the set: >-> Index Scan using core_accessor_fresh_idx on > core_accessor (cost=0.00..8955.44 rows=2440 width=92) (actual > time=0.056..53.107 rows=69312 loops=1) > Index Cond: ((slot_id = 472)

Re: [PERFORM] Problems with ordering (can't force query planner to use an index)

2009-03-03 Thread Robert Haas
On Tue, Mar 3, 2009 at 2:16 PM, Sebastjan Trepca 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=# explain analyze SELECT * FROM "core_accessor" WHERE > (("core_ac

Re: [PERFORM] Problems with ordering (can't force query planner to use an index)

2009-03-03 Thread Sebastjan Trepca
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=# explain analyze SELECT * FROM "core_accessor" WHERE (("core_accessor"."slot_type_id" = 119 noovo-new(# AND "core_accessor"."slot_

Re: [PERFORM] Problems with ordering (can't force query planner to use an index)

2009-03-03 Thread Sebastjan Trepca
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: analyzing "public.core_accessor" INFO: "core_accessor": scanned 291230 of 291230 page

Re: [PERFORM] Problems with ordering (can't force query planner to use an index)

2009-03-03 Thread Sebastjan Trepca
Set statistics to 1000, reanalyzed and got exactly same results: noovo-new=# 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_st

Re: [PERFORM] Problems with ordering (can't force query planner to use an index)

2009-03-03 Thread Robert Haas
On Tue, Mar 3, 2009 at 12:20 PM, Sebastjan Trepca wrote: > 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. I see it now. The lowest level at which I see

Re: [PERFORM] Problems with ordering (can't force query planner to use an index)

2009-03-03 Thread David Wilson
On Tue, Mar 3, 2009 at 12:05 PM, Sebastjan Trepca wrote: >         ->  Nested Loop  (cost=0.00..31883.13 rows=2861 width=860) > (actual time=0.089..543.497 rows=68505 loops=1) >               ->  Index Scan using core_accessor_fresh_idx on > core_accessor  (cost=0.00..5460.07 rows=2970 width=92)

Re: [PERFORM] Problems with ordering (can't force query planner to use an index)

2009-03-03 Thread Sebastjan Trepca
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 Sebastjan On Tue, Mar 3, 2009 at 6:12 PM, Robert Haas wrote: > On Tue, Mar 3, 2009 at 12:05 PM, Sebastjan Trepca wrote: >> Hey, >> >> I have a table that link

Re: [PERFORM] Problems with ordering (can't force query planner to use an index)

2009-03-03 Thread Robert Haas
On Tue, Mar 3, 2009 at 12:05 PM, Sebastjan Trepca wrote: > Hey, > > I have a table that links content together and it currently holds > about 17 mio records. Typical query is a join with a content table and > link table: > > noovo-new=# explain analyze SELECT "core_accessor"."id", > "core_accessor

[PERFORM] Problems with ordering (can't force query planner to use an index)

2009-03-03 Thread Sebastjan Trepca
Hey, I have a table that links content together and it currently holds about 17 mio records. Typical query is a join with a content table and link table: noovo-new=# explain analyze SELECT "core_accessor"."id", "core_accessor"."content_type_id", "core_accessor"."object_id", "core_accessor"."order