
Morning all ...

	First off, this is using v7.2 release ...

	Okay, this is going to drive me up the proverbial wall ... very
simple query:

SELECT p.uid, p.handle
    FROM orientation_c poc, profiles p, gender_f pgf
   WHERE (p.uid = pgf.uid )
     AND (pgf.uid = poc.uid ) ;

profiles contains:

iwantu=# select count(1) from profiles;
 count
--------
 485969
(1 row)

and is everyone in the system ... no problems there ...

gender_f contains:

iwantu=# select count(1) from gender_f;
 count
-------
 75664
(1 row)

And is *just* the uid's of those in profiles that are female ...

finally, orientation_c:

iwantu=# select count(1) from orientation_c;
 count
--------
 126477
(1 row)

Is again *just* the uid's of those in profiles that have a 'c'
orientiation ...

Now, the above wquery has an explain of:

Hash Join  (cost=6363.90..47877.08 rows=19692 width=35)
  ->  Seq Scan on profiles p  (cost=0.00..35707.69 rows=485969 width=19)
  ->  Hash  (cost=6174.74..6174.74 rows=75664 width=16)
        ->  Hash Join  (cost=2928.34..6174.74 rows=75664 width=16)
              ->  Seq Scan on gender_f pgf  (cost=0.00..1165.64 rows=75664 width=8)
              ->  Hash  (cost=1948.77..1948.77 rows=126477 width=8)
                    ->  Seq Scan on orientation_c poc  (cost=0.00..1948.77 rows=126477 width=8)

Now, a join between poc and pgf alone comes out to:

iwantu=# select count(1) from orientation_c poc, gender_f pgf where poc.uid = pgf.uid;
 count
-------
 12703
(1 row)

iwantu=# explain select count(1) from orientation_c poc, gender_f pgf where poc.uid = pgf.uid;
NOTICE:  QUERY PLAN:

Aggregate  (cost=6363.90..6363.90 rows=1 width=16)
  ->  Hash Join  (cost=2928.34..6174.74 rows=75664 width=16)
        ->  Seq Scan on gender_f pgf  (cost=0.00..1165.64 rows=75664 width=8)
        ->  Hash  (cost=1948.77..1948.77 rows=126477 width=8)
              ->  Seq Scan on orientation_c poc  (cost=0.00..1948.77 rows=126477 width=8)

EXPLAIN


Now, what I'd like to have happen is a SEQ SCAN through the smaller table
(gender_f), and grab everything in orientation_c that matches (both tables
have zero duplicates of uid, its purely a one of, so I would think that I
should be able to take 1 uid from pgf, and use the index on poc to
determine if it exists, and do that 75664 times ...

That would live me with 12703 UIDs to match up with apropriate records in
the almost 500+k records in profiles itself, instead of having to scan
through each of thoose 500+k records themselves ...

Then again, let's go one simpler:

iwantu=# \d orientation_c
Table "orientation_c"
 Column |  Type  | Modifiers
--------+--------+-----------
 uid    | bigint |
Indexes: poc_uid

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

if all varlues in orientation_c are unique, and there are 127k
records ... shouldn't it use the index instead of scanning through all 127k records ?  Or am I missing something totally obvious here?


Wed, 6 Feb 2002 16:47:45 -0400 (AST)
"Marc G. Fournier" <scrappy@hub.org>
JOIN between three *simple* tables ...
"Marc G. Fournier" <scrappy@hub.org> 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

Wed, 06 Feb 2002 16:14:33 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: JOIN between three *simple* tables ... 

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" <scrappy@hub.org> 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
>


Wed, 6 Feb 2002 17:41:17 -0400 (AST)
"Marc G. Fournier" <scrappy@hub.org>
Re: JOIN between three *simple* tables ... 