Re: Problems with ordering (can't force query planner to use an index) - Mailing list pgsql-performance

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_id" = 472 AND
"core_accessor"."label" = E'' AND "core_accessor"."publish_state" >=
60 AND
noovo-new(#  "core_accessor"."role" IN (0) AND
"core_accessor"."user_id" = 0)) ORDER BY "core_accessor"."date_posted"
DESC, "core_accessor"."nooximity" DESC LIMIT 5
noovo-new-# ;

       QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..3709.56 rows=5 width=178) (actual
time=4593.867..4597.587 rows=5 loops=1)
   ->  Index Scan Backward using core_accessor_date_idx on
core_accessor  (cost=0.00..1810265.67 rows=2440 width=178) (actual
time=4593.866..4597.583 rows=5 loops=1)
         Filter: ((publish_state >= 60) AND (slot_type_id = 119) AND
(slot_id = 472) AND (label = ''::text) AND (role = 0) AND (user_id =
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:  analyzing "public.core_accessor"
> INFO:  "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:
>
>
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  Limit  (cost=30816.49..30816.50 rows=5 width=855) (actual
> time=683.907..683.910 rows=5 loops=1)
>   ->  Sort  (cost=30816.49..30822.29 rows=2321 width=855) (actual
> time=683.906..683.907 rows=5 loops=1)
>         Sort Key: core_accessor.date_posted, core_accessor.nooximity
>         Sort Method:  top-N heapsort  Memory: 31kB
>         ->  Nested Loop  (cost=0.00..30777.94 rows=2321 width=855)
> (actual time=0.072..517.970 rows=68505 loops=1)
>               ->  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) AND (slot_type_id =
> 119) AND (label = ''::text) AND (user_id = 0) AND (role = 0) AND
> (publish_state >= 60))
>               ->  Index Scan using core_base_pkey on core_base
> (cost=0.00..8.93 rows=1 width=763) (actual time=0.004..0.005 rows=1
> loops=69312)
>                     Index Cond: ((core_base.object_id =
> core_accessor.object_id) AND (core_base.content_type_id =
> core_accessor.content_type_id))
>  Total 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> 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 a problem is here:
>>
>> ->  Index Scan using core_accessor_fresh_idx on core_accessor
>> (cost=0.00..5460.07 rows=2970 width=92) (actual time=0.068..54.921
>> rows=69312 loops=1)
>>    Index Cond: ((slot_id = 472) AND (slot_type_id = 119) AND (label =
>> ''::text) AND (user_id = 0) AND (role = 0) AND (publish_state >= 60))
>>
>> 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
>>
>