Re: index vs seqscan question

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?

Sort (cost=17171.83..17171.83 rows=488 width=56)
-> Merge Join (cost=17023.63..17150.05 rows=488 width=56)
-> Sort (cost=17002.73..17002.73 rows=10075 width=28)
-> Subquery Scan ts (cost=15325.25..16332.79 rows=10075
width=28)
-> Aggregate (cost=15325.25..16332.79 rows=10075
width=28)
-> Group (cost=15325.25..15577.14 rows=100754
width=28)
-> Sort (cost=15325.25..15325.25
rows=100754 width=28)
-> Seq Scan on
timesheet (cost=0.00..5410.22 rows=100754 width=28)
-> Sort (cost=20.89..20.89 rows=38 width=36)
-> Seq Scan on employee (cost=0.00..19.89 rows=38 width=36)

At 04:15 PM 11/22/02, Frank Bax wrote:
>I was looking and one of my SQL statements today and decided to try a
>slight variation to my query - and what a difference it made!! The query
>went from over 10 minutes down to under 30 seconds. I curious though; if
>I read the explain output correctly...
>-> Index Scan using timesheet_index_emp on
>timesheet (cost=0.00..19056.43 rows=122207 width=40)
>-> Seq Scan on timesheet (cost=0.00..7244.02 rows=122207 width=28)
>
>These statements imply the planner knows a seqscan is quicker than an
>index scan (only 3 times faster by its estimate, actually much more), why
>does it still use an index scan?
>
>Additional information:
>- pgsql version 7.1
>- vacuum analyse is run nightly.
>- the "earncode in..." clause by itself will select 85% of rows.
>- earncode = ' ' selects 80% of rows in table.
>- typ ' ' or 'A' selects 99% of rows in table.
>- final result is just approx 100 rows.
>
>Frank
>
>
>The original (slow) version:
>gwacl=> explain SELECT * FROM (SELECT emp, SUM (CASE WHEN earncode IN ('
>','A','O','P','Q','X','Z') THEN
>date_part('epoch',age(hi_shift,lo_shift))/(60*60) ELSE 0 END) AS hours
>FROM timesheet WHERE typ=' ' OR typ='A' GROUP BY emp) AS ts INNER JOIN
>(SELECT emp, first, last FROM employee WHERE status='A') AS emp ON (ts.emp
>= emp.emp) WHERE hours BETWEEN 0 AND 1250 ORDER BY hours DESC;
>NOTICE: QUERY PLAN:
>
>Sort (cost=21441.77..21441.77 rows=592 width=56)
> -> Merge Join (cost=21261.28..21414.52 rows=592 width=56)
> -> Sort (cost=21300.99..21300.99 rows=12221 width=40)
> -> Subquery Scan ts (cost=0.00..20278.50 rows=12221 width=40)
> -> Aggregate (cost=0.00..20278.50 rows=12221 width=40)
> -> Group (cost=0.00..19361.94 rows=122207
> width=40)
> -> Index Scan using timesheet_index_emp
> on timesheet (cost=0.00..19056.43 rows=122207 width=40)
> -> Sort (cost=20.89..20.89 rows=38 width=36)
> -> Seq Scan on employee (cost=0.00..19.89 rows=38 width=36)
>EXPLAIN
>
>The revised (much improved) version:
>gwacl=> explain SELECT * FROM (SELECT emp,
>SUM(date_part('epoch',age(hi_shift,lo_shift))/(60*60)) as hours FROM
>timesheet WHERE typ=' ' OR typ='A' AND earncode IN ('
>','A','O','P','Q','X','Z') GROUP BY emp) AS ts INNER JOIN (SELECT emp,
>first, last FROM employee WHERE status='A') AS emp ON (ts.emp = emp.emp)
>WHERE hours BETWEEN 0 AND 1250 ORDER BY hours DESC;
>NOTICE: QUERY PLAN:
>
>Sort (cost=23993.79..23993.79 rows=592 width=56)
> -> Merge Join (cost=23813.31..23966.55 rows=592 width=56)
> -> Sort (cost=23816.60..23816.60 rows=12221 width=28)
> -> Subquery Scan ts (cost=21608.46..22830.53 rows=12221
> width=28)
> -> Aggregate (cost=21608.46..22830.53 rows=12221
> width=28)
> -> Group (cost=21608.46..21913.97 rows=122207
> width=28)
> -> Sort (cost=21608.46..21608.46
> rows=122207 width=28)
> -> Seq Scan on
> timesheet (cost=0.00..7244.02 rows=122207 width=28)
> -> Sort (cost=20.89..20.89 rows=38 width=36)
> -> Seq Scan on employee (cost=0.00..19.89 rows=38 width=36)
>EXPLAIN
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org