DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=I389bKuXSIHlJeou+pSdRJg6/F7yF/N2xv6IAm4GVmMDT/E+qx46CDkRLc7eD64R3gLkoty5y8YsO0M9Dx/ifhaVvhxPLtz/xgMhd14571PaTSNUPalFfX7vD7ajr9nRUGURr3JO3oOcymeXLO9E9+VDtjyc3LXsSTBl+aiKXmY=
Message-ID: <82fa9e310805261958n5586835csf48a5453fd3de4d6@mail.gmail.com>
Date: Mon, 26 May 2008 19:58:51 -0700
From: mark <markkicks@gmail.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Subject: Re: select query takes 13 seconds to run with index
Cc: pgsql-general@postgresql.org, david.t.wilson@gmail.com,
	Gregory.Williamson@digitalglobe.com, justin@emproshunts.com
In-Reply-To: <18458.1211848570@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <82fa9e310805261549m2362fbe8sbf18789938fee833@mail.gmail.com>
	<18458.1211848570@sss.pgh.pa.us>

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