Search Postgresql Archives

Re: First query very slow. Solutions: memory, or settings, or SQL?

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

 



On Tuesday 21 July 2009 04:36:41 Phoenix Kiula wrote:
> On Mon, Jul 20, 2009 at 2:04 PM, Peter Eisentraut<peter_e@xxxxxxx> wrote:
> > On Monday 20 July 2009 06:45:40 Phoenix Kiula wrote:
> >> explain analyze select * from sites where user_id = 'phoenix' order by
> >> id desc limit 10;
> >>
> >>                                                         QUERY PLAN
> >> ------------------------------------------------------------------------
> >>--- ----------------------------------------------------------- Limit
> >> (cost=344.85..344.87 rows=10 width=262) (actual
> >> time=5879.069..5879.167 rows=10 loops=1)
> >>    ->  Sort  (cost=344.85..345.66 rows=323 width=262) (actual
> >> time=5879.060..5879.093 rows=10 loops=1)
> >>          Sort Key: id
> >>          ->  Index Scan using new_idx_sites_userid on sites
> >> (cost=0.00..331.39 rows=323 width=262) (actual time=44.408..5867.557
> >> rows=2178 loops=1)
> >>                Index Cond: ((user_id)::text = 'phoenix'::text)
> >>  Total runtime: 5879.414 ms
> >> (6 rows)
> >
> > The row estimate for the index scan is off.  Try reanalyzing, or increase
> > the statistics target.
>
> How did you tell that the row estimate is off? I mean which numbers?

Compare the rows=N figures for the estimate and the actual time.

> Also, my statistics are already set pretty high. On "USER_ID" they are
> at 100. Which columns should I increase the stats on, those in the
> WHERE clause or those in the SELECT bit? The USER_ID and the
> USER_KNOWN, which are in the WHERE clause, both have statistics of
> 100! And USER_KNOWN is just a binary value (0 or 1) so I wonder what
> purpose stats would serve?

Well then that should be OK.

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux