Re: Huge Performance Difference on Similar Query in Pg7.2 - Mailing list pgsql-general
| From | Heiko Klein |
|---|---|
| Subject | Re: Huge Performance Difference on Similar Query in Pg7.2 |
| Date | |
| Msg-id | 15514.64568.516307.214946@polar.oslo.dnmi.no Whole thread Raw |
| In response to | Re: Huge Performance Difference on Similar Query in Pg7.2 ("Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>) |
| List | pgsql-general |
Luis Alberto Amigo Navarro writes:
> can you send "explain analyze" instead of "explain" results in order to view
> real time?
> Regards
>
Here it is:
1)
emep=> explain analyze select count(*) from EmissionsView , DataSetsView where DataSetsView.setid =
EmissionsView.setidand EmissionsView.setid = '4614' ;
NOTICE: QUERY PLAN:
Aggregate (cost=5975.66..5975.66 rows=1 width=131) (actual time=1264.12..1264.13 rows=1 loops=1)
-> Hash Join (cost=5958.18..5975.58 rows=35 width=131) (actual time=1205.90..1262.46 rows=1606 loops=1)
-> Hash Join (cost=5956.30..5972.99 rows=35 width=127) (actual time=1197.85..1248.93 rows=1606 loops=1)
-> Hash Join (cost=5954.41..5970.40 rows=35 width=123) (actual time=1197.50..1224.92 rows=1606 loops=1)
-> Seq Scan on reports (cost=0.00..11.78 rows=378 width=8) (actual time=0.02..1.03 rows=378
loops=1)
-> Hash (cost=5954.30..5954.30 rows=42 width=115) (actual time=1193.55..1193.55 rows=0 loops=1)
-> Nested Loop (cost=4375.32..5954.30 rows=42 width=115) (actual time=1121.44..1188.23
rows=1606loops=1)
-> Nested Loop (cost=4375.32..5812.95 rows=42 width=103) (actual
time=1115.41..1134.58rows=1606 loops=1)
-> Seq Scan on footnotes (cost=0.00..1.01 rows=1 width=4) (actual
time=0.01..0.02rows=1 loops=1)
-> Materialize (cost=5811.31..5811.31 rows=42 width=99) (actual
time=1115.36..1116.33rows=1606 loops=1)
-> Hash Join (cost=4375.32..5811.31 rows=42 width=99) (actual
time=1047.90..1112.31rows=1606 loops=1)
-> Hash Join (cost=4371.95..5796.47 rows=307 width=77) (actual
time=1042.42..1076.85rows=1606 loops=1)
-> Merge Join (cost=4370.73..5789.88 rows=307 width=50)
(actualtime=1039.84..1059.42 rows=1606 loops=1)
-> Sort (cost=4370.73..4370.73 rows=28192 width=38)
(actualtime=996.25..1001.69 rows=3830 loops=1)
-> Hash Join (cost=24.06..1834.10 rows=28192
width=38)(actual time=7.50..507.66 rows=33593 loops=1)
-> Hash Join (cost=22.17..1268.37
rows=28192width=34) (actual time=7.15..303.78 rows=33593 loops=1)
-> Seq Scan on datasets
(cost=0.00..724.86rows=33786 width=22) (actual time=0.36..122.78 rows=33786 loops=1)
-> Hash (cost=21.23..21.23 rows=378
width=12)(actual time=6.73..6.73 rows=0 loops=1)
-> Hash Join (cost=1.89..21.23
rows=378width=12) (actual time=3.17..6.03 rows=377 loops=1)
-> Seq Scan on reports
(cost=0.00..11.78rows=378 width=8) (actual time=2.83..4.05 rows=378 loops=1)
-> Hash (cost=1.71..1.71
rows=71width=4) (actual time=0.26..0.26 rows=0 loops=1)
-> Seq Scan on areas
(cost=0.00..1.71 rows=71 width=4) (actual time=0.01..0.14 rows=71 loops=1)
-> Hash (cost=1.71..1.71 rows=71 width=4)
(actualtime=0.27..0.27 rows=0 loops=1)
-> Seq Scan on areas (cost=0.00..1.71
rows=71width=4) (actual time=0.01..0.15 rows=71 loops=1)
-> Index Scan using lhsecgriemis_setid_idx on emissions
(cost=0.00..1343.91rows=368 width=12) (actual time=33.77..42.61 rows=1606 loops=1)
-> Hash (cost=1.18..1.18 rows=18 width=27) (actual
time=2.49..2.49rows=0 loops=1)
-> Seq Scan on datatypes (cost=0.00..1.18 rows=18
width=27)(actual time=2.40..2.44 rows=18 loops=1)
-> Hash (cost=3.09..3.09 rows=109 width=22) (actual time=5.41..5.41
rows=0loops=1)
-> Seq Scan on sectordefinitions (cost=0.00..3.09 rows=109
width=22)(actual time=4.89..5.19 rows=109 loops=1)
-> Index Scan using datasets_setid_idx on datasets (cost=0.00..3.37 rows=1 width=12)
(actualtime=0.02..0.02 rows=1 loops=1606)
-> Hash (cost=1.71..1.71 rows=71 width=4) (actual time=0.25..0.25 rows=0 loops=1)
-> Seq Scan on areas (cost=0.00..1.71 rows=71 width=4) (actual time=0.01..0.13 rows=71 loops=1)
-> Hash (cost=1.71..1.71 rows=71 width=4) (actual time=7.95..7.95 rows=0 loops=1)
-> Seq Scan on areas (cost=0.00..1.71 rows=71 width=4) (actual time=7.69..7.83 rows=71 loops=1)
Total runtime: 1361.51 msec
EXPLAIN
2)
emep=> explain analyze select count(*) from EmissionsView , DataSetsView where DataSetsView.setid =
EmissionsView.setidand DataSetsView.setid = '4614' ;
NOTICE: QUERY PLAN:
Aggregate (cost=91660.36..91660.36 rows=1 width=131) (actual time=64414.80..64414.80 rows=1 loops=1)
-> Hash Join (cost=2732.23..91660.35 rows=3 width=131) (actual time=58428.47..64413.14 rows=1606 loops=1)
-> Nested Loop (cost=2719.53..91058.61 rows=117798 width=103) (actual time=49523.50..63005.67 rows=1025405
loops=1)
-> Seq Scan on footnotes (cost=0.00..1.01 rows=1 width=4) (actual time=0.01..0.03 rows=1 loops=1)
-> Materialize (cost=89290.63..89290.63 rows=117798 width=99) (actual time=49523.43..51974.76
rows=1025405loops=1)
-> Hash Join (cost=2719.53..89290.63 rows=117798 width=99) (actual time=1619.56..47188.00
rows=1025405loops=1)
-> Hash Join (cost=2716.17..56957.45 rows=863754 width=77) (actual time=1617.06..27358.00
rows=1035128loops=1)
-> Seq Scan on emissions (cost=0.00..18502.34 rows=1035134 width=12) (actual
time=6.42..5027.94rows=1035134 loops=1)
-> Hash (cost=2328.69..2328.69 rows=28192 width=65) (actual time=848.29..848.29
rows=0loops=1)
-> Hash Join (cost=25.29..2328.69 rows=28192 width=65) (actual
time=12.33..762.21rows=33593 loops=1)
-> Hash Join (cost=24.06..1834.10 rows=28192 width=38) (actual
time=10.57..519.08rows=33593 loops=1)
-> Hash Join (cost=22.17..1268.37 rows=28192 width=34) (actual
time=10.22..315.37rows=33593 loops=1)
-> Seq Scan on datasets (cost=0.00..724.86 rows=33786
width=22)(actual time=0.44..129.13 rows=33786 loops=1)
-> Hash (cost=21.23..21.23 rows=378 width=12) (actual
time=9.71..9.71rows=0 loops=1)
-> Hash Join (cost=1.89..21.23 rows=378 width=12)
(actualtime=5.23..8.98 rows=377 loops=1)
-> Seq Scan on reports (cost=0.00..11.78 rows=378
width=8)(actual time=4.90..7.04 rows=378 loops=1)
-> Hash (cost=1.71..1.71 rows=71 width=4) (actual
time=0.26..0.26rows=0 loops=1)
-> Seq Scan on areas (cost=0.00..1.71
rows=71width=4) (actual time=0.01..0.14 rows=71 loops=1)
-> Hash (cost=1.71..1.71 rows=71 width=4) (actual time=0.27..0.27
rows=0loops=1)
-> Seq Scan on areas (cost=0.00..1.71 rows=71 width=4)
(actualtime=0.01..0.15 rows=71 loops=1)
-> Hash (cost=1.18..1.18 rows=18 width=27) (actual time=1.69..1.69 rows=0
loops=1)
-> Seq Scan on datatypes (cost=0.00..1.18 rows=18 width=27) (actual
time=1.60..1.64rows=18 loops=1)
-> Hash (cost=3.09..3.09 rows=109 width=22) (actual time=2.44..2.44 rows=0 loops=1)
-> Seq Scan on sectordefinitions (cost=0.00..3.09 rows=109 width=22) (actual
time=1.91..2.20rows=109 loops=1)
-> Hash (cost=12.70..12.70 rows=1 width=28) (actual time=25.72..25.72 rows=0 loops=1)
-> Hash Join (cost=10.62..12.70 rows=1 width=28) (actual time=25.53..25.71 rows=1 loops=1)
-> Seq Scan on areas (cost=0.00..1.71 rows=71 width=4) (actual time=0.01..0.14 rows=71 loops=1)
-> Hash (cost=10.62..10.62 rows=1 width=24) (actual time=25.44..25.44 rows=0 loops=1)
-> Nested Loop (cost=0.00..10.62 rows=1 width=24) (actual time=25.24..25.43 rows=1 loops=1)
-> Nested Loop (cost=0.00..5.96 rows=1 width=16) (actual time=21.79..21.98 rows=1
loops=1)
-> Index Scan using datasets_setid_idx on datasets (cost=0.00..3.37 rows=1
width=12)(actual time=15.48..15.48 rows=1 loops=1)
-> Seq Scan on areas (cost=0.00..1.71 rows=71 width=4) (actual time=6.23..6.38
rows=71loops=1)
-> Index Scan using reports_pkey on reports (cost=0.00..4.64 rows=1 width=8) (actual
time=3.44..3.44rows=1 loops=1)
Total runtime: 64568.82 msec
EXPLAIN
pgsql-general by date: