Обсуждение: Strange cost computation?
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_keyon 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_keyon active_jobs alias98 (cost=0.00..4.48 rows=1 width=4) Ives
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?
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.