While working on a previous question I posed to this group, I ran a number
of EXPLAIN ANALYZE's to provide as examples. After sending up my last
email, I ran the same query *without* EXPLAIN ANALYZE. The runtimes were
vastly different. In the following example, I ran two identical queries
one right after the other. The runtimes for both was very close (44.77
sec). I then immediately ran the exact same query, but without EXPLAIN
ANALYZE. The same number of rows was returned, but the runtime was only
8.7 sec. I don't think EXPLAIN ANALYZE puts that much overhead on a query.
Does anyone have any idea what is going on here?

--- Steve

vsa=# explain analyze SELECT id,win_patch_scan_id FROM
vsa.tbl_win_patch_scan_item WHERE win_patch_scan_id IN (SELECT id FROM
vsa.tbl_win_patch_scan WHERE scan_datetime < '2004-09-18 00:00:00');
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on tbl_win_patch_scan_item (cost=0.00..382335670.62 rows=376033
width=8) (actual time=10.18..44773.22 rows=2045 loops=1)
Filter: (subplan)
SubPlan
-> Materialize (cost=505.06..511.38 rows=632 width=4) (actual
time=0.00..0.02 rows=43 loops=752066)
-> Seq Scan on tbl_win_patch_scan (cost=0.00..505.06 rows=632
width=4) (actual time=0.02..10.09 rows=43 loops=1)
Filter: (scan_datetime < '2004-09-18 00:00:00'::timestamp
without time zone)
Total runtime: 44774.49 msec
(7 rows)

Time: 44775.62 ms


vsa=# explain analyze SELECT id,win_patch_scan_id FROM
vsa.tbl_win_patch_scan_item WHERE win_patch_scan_id IN (SELECT id FROM
vsa.tbl_win_patch_scan WHERE scan_datetime < '2004-09-18 00:00:00');
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on tbl_win_patch_scan_item (cost=0.00..382335670.62 rows=376033
width=8) (actual time=10.18..44765.36 rows=2045 loops=1)
Filter: (subplan)
SubPlan
-> Materialize (cost=505.06..511.38 rows=632 width=4) (actual
time=0.00..0.02 rows=43 loops=752066)
-> Seq Scan on tbl_win_patch_scan (cost=0.00..505.06 rows=632
width=4) (actual time=0.02..10.10 rows=43 loops=1)
Filter: (scan_datetime < '2004-09-18 00:00:00'::timestamp
without time zone)
Total runtime: 44766.62 msec
(7 rows)

Time: 44767.71 ms


vsa=# SELECT id,win_patch_scan_id FROM vsa.tbl_win_patch_scan_item WHERE
win_patch_scan_id IN (SELECT id FROM vsa.tbl_win_patch_scan WHERE
scan_datetime < '2004-09-18 00:00:00');
id | win_patch_scan_id
--------+-------------------
1 | 1
2 | 1
3 | 1
4 | 1
5 | 1
----------8< SNIP --------------
211 | 7
212 | 7
213 | 7
214 | 7
215 | 7
216 | 7
217 | 7
692344 | 9276
692345 | 9276
692346 | 9276
692347 | 9276
692348 | 9276
----------8< SNIP --------------
694167 | 9311
694168 | 9311
694169 | 9311
694170 | 9311
694171 | 9311
(2045 rows)

Time: 8703.56 ms
vsa=#
__________________________________________________ _________________________________

Steven Rosenstein
IT Architect/Developer | IBM Virtual Server Administration
Voice/FAX: 845-689-2064 | Cell: 646-345-6978 | Tieline: 930-6001
Text Messaging: 6463456978 @ mobile.mycingular.com
Email: srosenst @ us.ibm.com

"Learn from the mistakes of others because you can't live long enough to
make them all yourself." -- Eleanor Roosevelt


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)