Re: select query takes 13 seconds to run with index

On Mon, May 26, 2008 at 5:36 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> mark <markkicks@gmail.com> writes:
>> EXPLAIN ANALYZE select * from pokes where uid = 578439028 order by id
>> DESC limit 6;
>>                                                                      QUERY PLAN
>>
----------------------------------------------------------------------------------------------------------------------------------------------------
>>  Limit  (cost=0.00..9329.02 rows=6 width=135) (actual
>> time=13612.247..13612.247 rows=0 loops=1)
>>    ->  Index Scan Backward using pokes_pkey on pokes
>> (cost=0.00..5182270.69 rows=3333 width=135) (actual
>> time=13612.245..13612.245 rows=0 loops=1)
>>          Filter: (uid = 578439028)
>>  Total runtime: 13612.369 ms
>> (4 rows)
>
> The problem is the vast disconnect between the estimated and actual
> rowcounts for the indexscan (3333 vs 0).  The planner thinks there
> are three thousand rows matching uid = 578439028, and that encourages
> it to try a plan that's only going to be fast if at least six such
> rows show up fairly soon while scanning the index in reverse id order.
> What you really want it to do here is scan on the uid index and then
> sort the result by id ... but that will be slow in exactly the case
> where this plan is fast, ie, when there are a lot of matching uids.
>
> Bottom line: the planner cannot make the right choice between these
> alternatives unless it's got decent statistics about the frequency
> of uid values.  "I analyzed the table about a week ago" is not good
> enough maintenance policy --- you need current stats, and you might need
> to bump up the statistics target to get enough data about less-common
> values of uid.
how do i do this? bump up the statistics target?

> (Since it's 8.3, the autovac daemon might have been analyzing for you,
> if you didn't turn off autovacuum.  In that case increasing the
> statistics target is the first thing to try.)
i did not turn it off..
and my OS is fedora 9

i ran vacuum verbose analyze pokes, and then ran the same query, and
there is no improvement..

EXPLAIN ANALYZE select * from pokes where uid = 578439028 order by id  limit 6;
                                                                QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..8446.80 rows=6 width=130) (actual
time=12262.779..12262.779 rows=0 loops=1)
   ->  Index Scan using pokes_pkey on pokes  (cost=0.00..5149730.49
rows=3658 width=130) (actual time=12262.777..12262.777 rows=0 loops=1)
         Filter: (uid = 578439028)
 Total runtime: 12262.817 ms

VACUUM VERBOSE ANALYZE pokes ;
INFO:  vacuuming "public.pokes"
INFO:  index "pokes_pkey" now contains 22341026 row versions in 61258 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.24s/0.06u sec elapsed 1.61 sec.
INFO:  index "idx_action_idx" now contains 22341026 row versions in 61548 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.38s/0.09u sec elapsed 7.21 sec.
INFO:  index "idx_friend_id" now contains 22341026 row versions in 60547 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.44s/0.11u sec elapsed 9.13 sec.
INFO:  index "idx_pokes_uid" now contains 22341026 row versions in 62499 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.41s/0.09u sec elapsed 7.44 sec.
INFO:  "pokes": found 0 removable, 22341026 nonremovable row versions
in 388144 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
1923 pages contain useful free space.
0 pages are entirely empty.
CPU 3.02s/2.38u sec elapsed 29.21 sec.
INFO:  vacuuming "pg_toast.pg_toast_43415"
INFO:  index "pg_toast_43415_index" now contains 12 row versions in 2 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_toast_43415": found 0 removable, 12 nonremovable row
versions in 2 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
2 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.pokes"
INFO:  "pokes": scanned 3000 of 388144 pages, containing 172933 live
rows and 0 dead rows; 3000 rows in sample, 22374302 estimated total