Yeech ... more on SEQSCAN vs having it disabled ... - Mailing list pgsql-hackers

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.voiceFROM ( SELECT poc.uid,headline,pictures,voice FROM orient poc JOIN
clubsc ON (poc.uid = c.uid AND c.club = 1 AND ( c.hide ='1' OR c.hide='2' )   AND (poc.female) ) ) AS ta    JOIN gender
pgfON ( 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.38rows=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)             ->
NestedLoop  (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.90rows=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.63rows=76954 loops=1)                               ->  Index Scan using gender_pkey on gender pgf
(cost=0.00..278734.47rows=387155 width=13) (actual time=0.03..5280.48 rows=385969 loops=1)                         ->
IndexScan 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=1loops=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.72rows=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.89rows=63539 width=134) (actual time=76911.24..76911.39 rows=26 loops=1)       ->  Merge Join
(cost=254132.94..256300.95rows=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=47441loops=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.18rows=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)
(actualtime=0.16..3693.87 rows=385970 loops=1)                                           ->  Hash
(cost=53609.16..53609.16rows=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=50745loops=1)                                                       ->  Seq Scan on clubs c  (cost=0.00..34057.19
rows=91367width=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.84rows=418951 loops=1)                         ->  Hash  (cost=7922.73..7922.73 rows=483973 width=16)
(actualtime=7010.57..7010.57 rows=0 loops=1)                               ->  Seq Scan on lastlogin ll
(cost=0.00..7922.73rows=483973 width=16) (actual time=0.11..3648.06 rows=483973 loops=1)             ->  Sort
(cost=65415.69..65415.69rows=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