Hi,
On Tue, 26 Aug 2003, Stephan Szabo wrote:
...
> But does it change the amount of time the query actually takes to run?
> seqscans are not always slower nor are they necessarily the actual problem
> here. The problem seems to be choosing a group aggregate + sort which is
> taking alot of time, if you look at the real time on the steps below that
> it's approximately the same for seqscan or index scan.
Ok, with plenty of sort_mem (327680) the seqscan seems to be faster.
Using 64000 shared_buffers it's not that amazing since nothing hits the disks.
As last times aus1.txt is before aus2.txt after the VACUUM ANALYZE.
Bye/2
---
Michael Reifenberger, Business Unit Manager SAP-Basis, Plaut Consulting
Comp: [EMAIL PROTECTED] | Priv: [EMAIL PROTECTED]
http://www.plaut.de | http://www.Reifenberger.com
#
# Anzahl Sätze in der DB, von Datum, bis Datum, Anzahl Tage
#
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Aggregate (cost=28924.49..28924.49 rows=1 width=4) (actual time=3408.51..3408.51
rows=1 loops=1)
-> Seq Scan on flows (cost=0.00..19380.88 rows=763488 width=4) (actual
time=0.02..1671.79 rows=763488 loops=1)
Total runtime: 3409.05 msec
(3 Zeilen)
#
# Tagesstatistik je Protokoll
#
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=14419.56..14419.89 rows=131 width=52) (actual time=13472.11..13472.38
rows=159 loops=1)
Sort Key: f.date, (sum(f.sdbytes) + sum(f.dsbytes))
-> HashAggregate (cost=14412.99..14414.95 rows=131 width=52) (actual
time=13470.92..13471.59 rows=159 loops=1)
-> Hash Join (cost=2.62..14375.76 rows=2482 width=52) (actual
time=10.58..7998.45 rows=753800 loops=1)
Hash Cond: ("outer"."type" = "inner".pnum)
-> Index Scan using flows_2 on flows f (cost=0.00..14214.68 rows=3818
width=24) (actual time=0.18..2987.40 rows=753800 loops=1)
Index Cond: ((date >= '2003-07-01'::date) AND (date <=
'2003-08-01'::date))
-> Hash (cost=2.30..2.30 rows=130 width=36) (actual time=6.23..6.23
rows=0 loops=1)
-> Seq Scan on protos p (cost=0.00..2.30 rows=130 width=36)
(actual time=0.09..0.43 rows=130 loops=1)
Total runtime: 13478.65 msec
(10 Zeilen)
#
# Tagesstatistik gesamt
#
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=14262.45..14262.45 rows=2 width=20) (actual time=5141.80..5141.86 rows=31
loops=1)
Sort Key: date, (sum(sdbytes) + sum(dsbytes))
-> GroupAggregate (cost=0.00..14262.44 rows=2 width=20) (actual
time=193.42..5141.57 rows=31 loops=1)
-> Index Scan using flows_2 on flows f (cost=0.00..14214.68 rows=3818
width=20) (actual time=0.12..2423.80 rows=753800 loops=1)
Index Cond: ((date >= '2003-07-01'::date) AND (date <=
'2003-08-01'::date))
Total runtime: 5142.25 msec
(6 Zeilen)
#
# Top 50: max( Bytes )
#
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=14312.30..14312.42 rows=50 width=72) (actual time=8398.72..8398.97
rows=50 loops=1)
-> Sort (cost=14312.30..14312.80 rows=201 width=72) (actual time=8398.70..8398.79
rows=50 loops=1)
Sort Key: (sum(sdbytes) + sum(dsbytes))
-> HashAggregate (cost=14300.59..14304.61 rows=201 width=72) (actual
time=8188.61..8296.36 rows=23410 loops=1)
-> Index Scan using flows_2 on flows f (cost=0.00..14252.86 rows=3818
width=72) (actual time=0.15..4768.39 rows=753800 loops=1)
Index Cond: ((date >= '2003-07-01'::date) AND (date <=
'2003-08-01'::date))
Total runtime: 8432.66 msec
(7 Zeilen)
#
# Anzahl Sätze in der DB, von Datum, bis Datum, Anzahl Tage
#
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Aggregate (cost=28924.49..28924.49 rows=1 width=4) (actual time=3408.29..3408.29
rows=1 loops=1)
-> Seq Scan on flows (cost=0.00..19380.88 rows=763488 width=4) (actual
time=0.02..1671.06 rows=763488 loops=1)
Total runtime: 3408.83 msec
(3 Zeilen)
#
# Tagesstatistik je Protokoll
#
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Sort (cost=46007.38..46017.59 rows=4087 width=28) (actual time=11906.64..11906.91
rows=159 loops=1)
Sort Key: f.date, (sum(f.sdbytes) + sum(f.dsbytes))
-> HashAggregate (cost=45700.92..45762.22 rows=4087 width=28) (actual
time=11905.42..11906.11 rows=159 loops=1)
-> Hash Join (cost=2.62..34450.93 rows=749999 width=28) (actual
time=16.34..6845.94 rows=753800 loops=1)
Hash Cond: ("outer"."type" = "inner".pnum)
-> Seq Scan on flows f (cost=0.00..23198.32 rows=749999 width=24)
(actual time=0.10..2255.99 rows=753800 loops=1)
Filter: ((date >= '2003-07-01'::date) AND (date <=
'2003-08-01'::date))
-> Hash (cost=2.30..2.30 rows=130 width=12) (actual time=9.49..9.49
rows=0 loops=1)
-> Seq Scan on protos p (cost=0.00..2.30 rows=130 width=12)
(actual time=0.11..0.45 rows=130 loops=1)
Total runtime: 11916.51 msec
(10 Zeilen)
#
# Tagesstatistik gesamt
#
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Sort (cost=32574.59..32574.67 rows=32 width=20) (actual time=4781.48..4781.53
rows=31 loops=1)
Sort Key: date, (sum(sdbytes) + sum(dsbytes))
-> HashAggregate (cost=32573.31..32573.79 rows=32 width=20) (actual
time=4781.19..4781.30 rows=31 loops=1)
-> Seq Scan on flows f (cost=0.00..23198.32 rows=749999 width=20) (actual
time=0.07..2009.61 rows=753800 loops=1)
Filter: ((date >= '2003-07-01'::date) AND (date <= '2003-08-01'::date))
Total runtime: 4782.13 msec
(6 Zeilen)
#
# Top 50: max( Bytes )
#
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Limit (cost=128260.16..128260.29 rows=50 width=30) (actual time=7451.21..7451.46
rows=50 loops=1)
-> Sort (cost=128260.16..130135.16 rows=749999 width=30) (actual
time=7451.20..7451.28 rows=50 loops=1)
Sort Key: (sum(sdbytes) + sum(dsbytes))
-> HashAggregate (cost=40073.30..55073.28 rows=749999 width=30) (actual
time=7231.99..7350.32 rows=23410 loops=1)
-> Seq Scan on flows f (cost=0.00..30698.31 rows=749999 width=30)
(actual time=0.14..3984.49 rows=753800 loops=1)
Filter: ((date >= '2003-07-01'::date) AND (date <=
'2003-08-01'::date))
Total runtime: 7523.39 msec
(7 Zeilen)
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
