Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178])
	by postgresql.org (Postfix) with ESMTP id E1E50475C4A
	for <pgsql-sql@postgresql.org>; Tue, 23 Jul 2002 13:00:15 -0400 (EDT)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id 08B43D60A; Tue, 23 Jul 2002 10:00:09 -0700 (PDT)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id F28AC5C03; Tue, 23 Jul 2002 10:00:08 -0700 (PDT)
Date: Tue, 23 Jul 2002 10:00:08 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Daryl Herzmann <akrherz@iastate.edu>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: Queries not using Index
In-Reply-To: 
 <Pine.LNX.4.44.0207231124580.1171-100000@akrherz.agron.iastate.edu>
Message-ID: <20020723095432.F25463-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Archive-Number: 200207/356
X-Sequence-Number: 8734

On Tue, 23 Jul 2002, Daryl Herzmann wrote:

>
> >On Tue, 23 Jul 2002, Daryl Herzmann wrote:
> >
> >> snet=# explain analyze select * from t2002_06 WHERE station = 'SAMI4';
> >> NOTICE:  QUERY PLAN:
> >>
> >> Seq Scan on t2002_06  (cost=0.00..35379.69 rows=34979 width=47) (actual
> >> time=67.89..3734.93 rows=38146 loops=1)
> >> Total runtime: 3748.33 msec
> >>
> >> EXPLAIN
> >>
> >> snet=# set enable_seqscan=off;
> >> SET VARIABLE
> >> snet=# explain analyze select * from t2002_06 WHERE station = 'SAMI4';
> >> NOTICE:  QUERY PLAN:
> >>
> >> Index Scan using t2002_06_station_idx on t2002_06  (cost=0.00..132124.96
> >> rows=34979 width=47) (actual time=72.03..298.85 rows=38146 loops=1)
> >> Total runtime: 317.76 msec
> >
> >Looks like the estimated cost is way divorced from reality.  Is the
> >34979 row estimate even realistic and how well ordered is the table
> >(actually output from pg_statistic would be good as well :) ).
>
> Thanks for the help! I am not sure if I can answer your questions.  I will
> try :)
>
> I believe the row estimate is realistic based on this value.
>
> snet=# select count(*) from t2002_06 WHERE station = 'SAMI4';
>  count
> -------
>  38146

> I am really sorry, but I don't know what to output from pg_statistic. I
> searched around on the Internet and was not sure what to send you from
> this table.  Sorry :(

Right... sorry about that...
select * from pg_statistic where starelid=(select oid from pg_class
 where relname='t2002_06';