On 17/07/2003 12:13 Fabian Kreitner wrote:That is what I read too and is why Im confused that the index is indeed executing faster. Can this be a problem with the hardware and/or postgress installation?
It's more likely that the OS has most of the data cached after the first query and so doesn't need to re-read that data from disk when you retry the query with seq scan disabled. Try something like this:
set enable_seqscan to true; explain analyze ...... set enable_seqscan to false; explain analyze ...... set enable_seqscan to true; explain analyze ......
I expect you will find that the third query is also a lot faster that the first query.
Im afraid, no. Database has been stopped / started right before this.
perg_1097=# set enable_seqscan to true; SET VARIABLE perg_1097=# EXPLAIN ANALYZE select notiz_id, obj_id, obj_typ perg_1097-# from notiz_objekt a perg_1097-# where not exists perg_1097-# ( perg_1097(# select 1 perg_1097(# from notiz_gelesen b perg_1097(# where ma_id = 2001 perg_1097(# and ma_pid = 1097 perg_1097(# and a.notiz_id = b.notiz_id perg_1097(# ) perg_1097-# ; NOTICE: QUERY PLAN:
Seq Scan on notiz_objekt a (cost=0.00..56125.80 rows=15561 width=12) (actual time=0.28..2298.71 rows=31122 loops=1)
SubPlan
-> Seq Scan on notiz_gelesen b (cost=0.00..1.79 rows=1 width=0) (actual time=0.07..0.07 rows=0 loops=31122)
Total runtime: 2327.37 msec
EXPLAIN perg_1097=# set enable_seqscan to false; SET VARIABLE perg_1097=# EXPLAIN ANALYZE select notiz_id, obj_id, obj_typ perg_1097-# from notiz_objekt a perg_1097-# where not exists perg_1097-# ( perg_1097(# select 1 perg_1097(# from notiz_gelesen b perg_1097(# where ma_id = 2001 perg_1097(# and ma_pid = 1097 perg_1097(# and a.notiz_id = b.notiz_id perg_1097(# ) perg_1097-# ; NOTICE: QUERY PLAN:
Seq Scan on notiz_objekt a (cost=100000000.00..100111719.36 rows=15561 width=12) (actual time=0.25..535.75 rows=31122 loops=1)
SubPlan
-> Index Scan using idx_notiz_gelesen_2 on notiz_gelesen b (cost=0.00..3.57 rows=1 width=0) (actual time=0.01..0.01 rows=0 loops=31122)
Total runtime: 567.94 msec
EXPLAIN perg_1097=# set enable_seqscan to true; SET VARIABLE perg_1097=# EXPLAIN ANALYZE select notiz_id, obj_id, obj_typ perg_1097-# from notiz_objekt a perg_1097-# where not exists perg_1097-# ( perg_1097(# select 1 perg_1097(# from notiz_gelesen b perg_1097(# where ma_id = 2001 perg_1097(# and ma_pid = 1097 perg_1097(# and a.notiz_id = b.notiz_id perg_1097(# ) perg_1097-# ; NOTICE: QUERY PLAN:
Seq Scan on notiz_objekt a (cost=0.00..56125.80 rows=15561 width=12) (actual time=0.13..2300.74 rows=31122 loops=1)
SubPlan
-> Seq Scan on notiz_gelesen b (cost=0.00..1.79 rows=1 width=0) (actual time=0.07..0.07 rows=0 loops=31122)
Total runtime: 2330.25 msec
EXPLAIN perg_1097=#
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
