Received: from earth.hub.org (earth.hub.org [64.49.215.11]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g16LfPE76600; Wed, 6 Feb 2002 16:41:25 -0500 (EST) (envelope-from scrappy@hub.org) Received: from localhost (scrappy@localhost) by earth.hub.org (8.11.3/8.11.6) with ESMTP id g16LfMh90963; Wed, 6 Feb 2002 17:41:23 -0400 (AST) (envelope-from scrappy@hub.org) Date: Wed, 6 Feb 2002 17:41:17 -0400 (AST) From: "Marc G. Fournier" To: Tom Lane cc: pgsql-sql@postgresql.org, Subject: Re: JOIN between three *simple* tables ... In-Reply-To: <8775.1013030073@sss.pgh.pa.us> Message-ID: <20020206174041.L57607-100000@earth.hub.org> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200202/199 X-Sequence-Number: 18527 Okay, now I'm annoyed ... I *swear* I ran VACUUM ANALYZE a couple of times since populating those tables *sigh* now its bringing up indices I'm expecting :( On Wed, 6 Feb 2002, Tom Lane wrote: > "Marc G. Fournier" writes: > > iwantu=# \d poc_uid > > Index "poc_uid" > > Column | Type > > --------+-------- > > uid | bigint > > btree > > > iwantu=# explain select count(1) from orientation_c poc where uid = 1; > > NOTICE: QUERY PLAN: > > > Aggregate (cost=2264.97..2264.97 rows=1 width=0) > > -> Seq Scan on orientation_c poc (cost=0.00..2264.96 rows=1 width=0) > > > EXPLAIN > > You're forgetting ye olde constant-casting problem. You need something > like > > select count(1) from orientation_c poc where uid = 1::bigint; > > to use an index on a bigint column. > > Not sure about the other thing; have you VACUUM ANALYZEd (or at least > ANALYZEd) since filling the tables? It looks like the system thinks > the tables are much smaller than they really are. > > regards, tom lane >