Re: index vs seqscan question

At 05:30 PM 11/22/02, Tom Lane wrote:
>Frank Bax <fbax@sympatico.ca> writes:
> > OK, I should have finished testing my changes before posting - the
> > new/faster query should have brackets around (typ=' ' OR typ='A'), but its
> > still fast as lightning!  I don't think it made a big difference to
> explain
> > results, but it appears seqscan is cheaper than it was before?
>
>The version with the typo couldn't use an indexscan, I think (planner's
>not real smart about asymmetrical AND/OR structures).  The fixed
>version is probably going for seqscan because with the additional AND
>condition, it's estimating fewer rows need to be sorted.  The seqscan
>isn't getting cheaper, but the sort is.
>
>Looking only at the planner's estimates is not very reliable though.
>What does EXPLAIN ANALYZE have to say?


Did I say 7.1?  But I do have access to 7.2.2 on a faster system and there
seqscan was used for both queries.  The new query is still faster; probably
because more rows were removed sooner.  The old query was written the way
it was because at one time hours were reported in two columns (therefore
the case stmt).  Sorry to be a bother.  Results below if you're still
interested...

NOTICE:  QUERY PLAN:

Sort  (cost=20886.51..20886.51 rows=5045 width=48) (actual
time=9647.67..9647.80 rows=116 loops=1)
   ->  Merge Join  (cost=20481.62..20576.24 rows=5045 width=48) (actual
time=9642.71..9646.58 rows=116 loops=1)
       ->  Sort  (cost=20484.38..20484.38 rows=12302 width=31) (actual
time=9638.32..9638.57 rows=230 loops=1)
           ->  Subquery Scan ts  (cost=18247.82..19478.04 rows=12302
width=31) (actual time=5976.92..9636.87 rows=230 loops=1)
               ->  Aggregate  (cost=18247.82..19478.04 rows=12302 width=31)
(actual time=5976.91..9636.01 rows=230 loops=1)
                   ->  Group  (cost=18247.82..18555.38 rows=123022
width=31) (actual time=5966.50..7083.89 rows=122970 loops=1)
                       ->  Sort  (cost=18247.82..18247.82 rows=123022
width=31) (actual time=5966.48..6228.12 rows=122970 loops
=1)
                           ->  Seq Scan on timesheet  (cost=0.00..3293.56
rows=123022 width=31) (actual time=0.04..624.57 rows=
122970 loops=1)
       ->  Sort  (cost=33.55..33.55 rows=326 width=30) (actual
time=4.26..4.66 rows=326 loops=1)
           ->  Seq Scan on employee  (cost=0.00..19.94 rows=326 width=30)
(actual time=0.49..2.07 rows=326 loops=1)
Total runtime: 9984.54 msec

NOTICE:  QUERY PLAN:

Sort  (cost=17738.45..17738.45 rows=4237 width=48) (actual
time=8188.82..8188.95 rows=116 loops=1)
   ->  Merge Join  (cost=17403.58..17483.19 rows=4237 width=48) (actual
time=8183.96..8187.71 rows=116 loops=1)
         ->  Sort  (cost=17502.32..17502.32 rows=10333 width=26) (actual
time=8179.38..8179.64 rows=230 loops=1)
             ->  Subquery Scan ts  (cost=15647.81..16681.09 rows=10333
width=26) (actual time=5528.26..8177.94 rows=230 loops=1
)
                 ->  Aggregate  (cost=15647.81..16681.09 rows=10333
width=26) (actual time=5528.26..8177.07 rows=230 loops=1)
                     ->  Group  (cost=15647.81..15906.13 rows=103328
width=26) (actual time=5519.65..6468.57 rows=107328 loops=
1)
                         ->  Sort  (cost=15647.81..15647.81 rows=103328
width=26) (actual time=5519.64..5737.93 rows=107328 loo
ps=1)
                             ->  Seq Scan on timesheet  (cost=0.00..5447.88
rows=103328 width=26) (actual time=0.06..848.90 row
s=107328 loops=1)
         ->  Sort  (cost=33.55..33.55 rows=326 width=30) (actual
time=4.45..4.81 rows=326 loops=1)
             ->  Seq Scan on employee  (cost=0.00..19.94 rows=326 width=30)
(actual time=0.54..2.18 rows=326 loops=1)
Total runtime: 8412.90 msec