
Granted that I'm still futzing with my table structures and whatnot, but
here is the full query, and the explain ANALYZE for it with SEQSCAN both
enabled and disbled ... enabled, it takes 2x longer??

SELECT p.uid, tB.headline, tB.pictures, tB.voice, pa.zip, tb.gender, p.profiles_handle
  FROM
    ((( SELECT ta.uid,pgf.gender,ta.headline,ta.pictures,ta.voice FROM ( SELECT poc.uid,headline,pictures,voice FROM orient poc JOIN clubs c ON (poc.uid = c.uid AND c.club = 1 AND ( c.hide ='1' OR c.hide='2' )   AND (poc.female) ) ) AS ta
     JOIN gender pgf ON ( ta.uid = pgf.uid   AND (pgf.gender='M') ) ) AS tb
    JOIN iwantu_profiles p USING (uid))
   LEFT JOIN lastlogin ll USING (uid))
  LEFT JOIN location pa USING (uid)  ORDER BY ll.lastlogin DESC LIMIT 25 OFFSET 0;;


Limit  (cost=2939483.38..2939483.38 rows=25 width=134) (actual time=38013.45..38013.87 rows=25 loops=1)
  ->  Sort  (cost=2939483.38..2939483.38 rows=63539 width=134) (actual time=38013.43..38013.58 rows=26 loops=1)
        ->  Nested Loop  (cost=0.00..2930209.43 rows=63539 width=134) (actual time=1.94..35774.58 rows=47441 loops=1)
              ->  Nested Loop  (cost=0.00..2693295.07 rows=63539 width=120) (actual time=1.50..32086.98 rows=47441 loops=1)
                    ->  Nested Loop  (cost=0.00..2466570.37 rows=63539 width=104) (actual time=1.07..28589.72 rows=47441 loops=1)
                          ->  Merge Join  (cost=0.00..2237377.25 rows=63539 width=85) (actual time=0.59..23263.91 rows=47441 loops=1)
                                ->  Merge Join  (cost=0.00..1956681.26 rows=79756 width=72) (actual time=0.47..14295.90 rows=50745 loops=1)
                                      ->  Index Scan using orient_pkey on orient poc  (cost=0.00..256490.59 rows=424251 width=8) (actual time=0.04..4833.53 rows=418951 loops=1)
                                      ->  Index Scan using clubs_idx on clubs c  (cost=0.00..1697904.67 rows=91367 width=64) (actual time=0.34..5187.63 rows=76954 loops=1)
                                ->  Index Scan using gender_pkey on gender pgf  (cost=0.00..278734.47 rows=387155 width=13) (actual time=0.03..5280.48 rows=385969 loops=1)
                          ->  Index Scan using iwantu_profiles_n_pkey on iwantu_profiles p  (cost=0.00..3.59 rows=1 width=19) (actual time=0.07..0.08 rows=1 loops=47441)
                    ->  Index Scan using lastlogin_pkey on lastlogin ll  (cost=0.00..3.56 rows=1 width=16) (actual time=0.04..0.04 rows=1 loops=47441)
              ->  Index Scan using location_pkey on location pa  (cost=0.00..3.72 rows=1 width=14) (actual time=0.04..0.05 rows=1 loops=47441)
Total runtime: 38059.34 msec


Limit  (cost=265574.89..265574.89 rows=25 width=134) (actual time=76911.26..76911.68 rows=25 loops=1)
  ->  Sort  (cost=265574.89..265574.89 rows=63539 width=134) (actual time=76911.24..76911.39 rows=26 loops=1)
        ->  Merge Join  (cost=254132.94..256300.95 rows=63539 width=134) (actual time=67544.75..74800.40 rows=47441 loops=1)
              ->  Sort  (cost=188717.25..188717.25 rows=63539 width=120) (actual time=48313.73..48656.13 rows=47441 loops=1)
                    ->  Hash Join  (cost=129420.48..180063.31 rows=63539 width=120) (actual time=30001.67..46783.32 rows=47441 loops=1)
                          ->  Hash Join  (cost=72389.02..116937.80 rows=63539 width=104) (actual time=22960.36..37247.98 rows=47441 loops=1)
                                ->  Seq Scan on iwantu_profiles p  (cost=0.00..35233.69 rows=485969 width=19) (actual time=0.42..6145.64 rows=485969 loops=1)
                                ->  Hash  (cost=71361.18..71361.18 rows=63539 width=85) (actual time=22946.01..22946.01 rows=0 loops=1)
                                      ->  Hash Join  (cost=54743.55..71361.18 rows=63539 width=85) (actual time=12332.48..22558.83 rows=47441 loops=1)
                                            ->  Seq Scan on gender pgf  (cost=0.00..9170.61 rows=387155 width=13) (actual time=0.16..3693.87 rows=385970 loops=1)
                                            ->  Hash  (cost=53609.16..53609.16 rows=79756 width=72) (actual time=12328.38..12328.38 rows=0 loops=1)
                                                  ->  Hash Join  (cost=13562.51..53609.16 rows=79756 width=72) (actual time=6104.95..11926.93 rows=50745 loops=1)
                                                        ->  Seq Scan on clubs c  (cost=0.00..34057.19 rows=91367 width=64) (actual time=0.16..2938.62 rows=76954 loops=1)
                                                        ->  Hash  (cost=7718.69..7718.69 rows=424251 width=8) (actual time=6080.37..6080.37 rows=0 loops=1)
                                                              ->  Seq Scan on orient poc  (cost=0.00..7718.69 rows=424251 width=8) (actual time=0.13..3144.84 rows=418951 loops=1)
                          ->  Hash  (cost=7922.73..7922.73 rows=483973 width=16) (actual time=7010.57..7010.57 rows=0 loops=1)
                                ->  Seq Scan on lastlogin ll  (cost=0.00..7922.73 rows=483973 width=16) (actual time=0.11..3648.06 rows=483973 loops=1)
              ->  Sort  (cost=65415.69..65415.69 rows=485969 width=14) (actual time=19230.90..22199.19 rows=485965 loops=1)
                    ->  Seq Scan on location pa  (cost=0.00..9649.69 rows=485969 width=14) (actual time=0.11..4289.88 rows=485969 loops=1)
Total runtime: 76970.09 msec




Mon, 11 Feb 2002 10:40:16 -0400 (AST)
"Marc G. Fournier" <scrappy@hub.org>
Yeech ... more on SEQSCAN vs having it disabled ...