[HACKERS] Strange cost computation?

2002-10-30 Thread Ives Landrieu 
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=10458.18..10916.35 rows=10 width=10)
  -  Seq Scan on job alias96  (cost=1.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

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] Strange cost computation?

2002-10-30 Thread Stephan Szabo 
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?


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] Strange cost computation?

2002-10-30 Thread Stephan Szabo 
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.


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster