Hi,

Can anybody explain the following results when using EXPLAIN, 
one time with enable_seqscan=on and one time with enable_seqscan=off.
What I don't understand is that the nodes created are the same 
(index scan, seq scan), but the costs differ.  

set enable_seqscan=on;
explain (SELECT alias96.ID FROM JOB AS alias96 WHERE  NOT EXISTS
((SELECT alias97.DEPENDENT_ID FROM JOB_MISSING_DEPENDENCIES AS alias97
WHERE alias97.DEPENDENT_ID = alias96.ID)) AND  NOT EXISTS ((SELECT
alias98.JOB_ID FROM ACTIVE_JOBS AS alias98 WHERE alias98.JOB_ID =
alias96.ID)) AND ((alias96.STATUS IN (-4, -1))) FOR UPDATE OF alias96
OFFSET 10 LIMIT 10);
NOTICE:  QUERY PLAN:

Limit  (cost=458.18..916.35 rows=10 width=10)
  ->  Seq Scan on job alias96  (cost=0.00..2185013.05 rows=47689
width=10)
        SubPlan
          ->  Index Scan using job_mis6970_dependent_id_idx on
job_missing_dependencies alias97  (cost=0.00..893.88 rows=288 width=4)
          ->  Index Scan using active_jobs_job_id_key on active_jobs
alias98  (cost=0.00..4.48 rows=1 width=4)

set enable_seqscan=off;
explain (SELECT alias96.ID FROM JOB AS alias96 WHERE  NOT EXISTS
((SELECT alias97.DEPENDENT_ID FROM JOB_MISSING_DEPENDENCIES AS alias97
WHERE alias97.DEPENDENT_ID = alias96.ID)) AND  NOT EXISTS ((SELECT
alias98.JOB_ID FROM ACTIVE_JOBS AS alias98 WHERE alias98.JOB_ID =
alias96.ID)) AND ((alias96.STATUS IN (-4, -1))) FOR UPDATE OF alias96
OFFSET 10 LIMIT 10);
NOTICE:  QUERY PLAN:

Limit  (cost=100000458.18..100000916.35 rows=10 width=10)
  ->  Seq Scan on job alias96  (cost=100000000.00..102185013.05
rows=47689 width=10)
        SubPlan
          ->  Index Scan using job_mis6970_dependent_id_idx on
job_missing_dependencies alias97  (cost=0.00..893.88 rows=288 width=4)
          ->  Index Scan using active_jobs_job_id_key on active_jobs
alias98  (cost=0.00..4.48 rows=1 width=4)


Ives

29 Oct 2002 02:11:14 -0800
ives.landrieu@ua.ac.be (Ives Landrieu)
Strange cost computation?
On 29 Oct 2002, Ives Landrieu wrote:

> Hi,
>
> Can anybody explain the following results when using EXPLAIN,
> one time with enable_seqscan=on and one time with enable_seqscan=off.
> What I don't understand is that the nodes created are the same
> (index scan, seq scan), but the costs differ.

Enable seq scan actually just sets a large cost disbenefit to seq scans.
Is alias96.STATUS indexed?


Wed, 30 Oct 2002 07:59:56 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Strange cost computation?
On Wed, 30 Oct 2002, Stephan Szabo wrote:

> On 29 Oct 2002, Ives Landrieu wrote:
>
> > Hi,
> >
> > Can anybody explain the following results when using EXPLAIN,
> > one time with enable_seqscan=on and one time with enable_seqscan=off.
> > What I don't understand is that the nodes created are the same
> > (index scan, seq scan), but the costs differ.
>
> Enable seq scan actually just sets a large cost disbenefit to seq scans.

Boy, that sentence sure didn't make sense.  enable_seqscan=off actually
gives a large cost disbenefit to seqscans.


Wed, 30 Oct 2002 08:12:12 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Strange cost computation?