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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx> wrote:
> On Tue, Mar 3, 2009 at 12:20 PM, Sebastjan Trepca <trepca@xxxxxxxxx> 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
>

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux