Re: index vs seqscan question - Mailing list pgsql-general
From | Frank Bax |
---|---|
Subject | Re: index vs seqscan question |
Date | |
Msg-id | 5.1.1.6.0.20021122174303.033a5e10@pop6.sympatico.ca Whole thread Raw |
In response to | Re: index vs seqscan question (Tom Lane <tgl@sss.pgh.pa.us>) |
List | pgsql-general |
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
pgsql-general by date: