Re: Huge Performance Difference on Similar Query in Pg7.2
От | Heiko Klein |
---|---|
Тема | Re: Huge Performance Difference on Similar Query in Pg7.2 |
Дата | |
Msg-id | 15515.2678.217337.385989@polar.oslo.dnmi.no обсуждение исходный текст |
Ответ на | Re: Huge Performance Difference on Similar Query in Pg7.2 ("Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>) |
Список | pgsql-general |
Luis Alberto Amigo Navarro writes: > first of all the difference between expected times and real times may be > reduced tuning your postgresql.conf > try to set enable_seqscan to off and then re explain analyze to see if there > is another possible solution > Regards About the times, how do I adjust those? Does it really makes such a huge difference or is it just for better display. I set enable_seqscan to off, and it changed to the Seq scan on emissions to a index scan on emissions, but time didn't change, still about a factor 50 between both queries: New explain analyze report of 2) emep=> explain analyze select count(*) from LowHiSectorGridEmissionsView , DataSetsView where DataSetsView.setid ='4614' and DataSetsView.setid = LowHiSectorGridEmissionsView.setid ; NOTICE: QUERY PLAN: Aggregate (cost=103440665.94..103440665.94 rows=1 width=131) (actual time=61151.81..61151.81 rows=1 loops=1) -> Hash Join (cost=100005420.21..103440665.93 rows=4 width=131) (actual time=49892.37..61150.12 rows=1606 loops=1) -> Nested Loop (cost=100005403.12..103440051.40 rows=119480 width=103) (actual time=46157.46..59840.49 rows=1025405loops=1) -> Seq Scan on footnotes (cost=100000000.00..100000001.01 rows=1 width=4) (actual time=0.01..0.03 rows=1loops=1) -> Materialize (cost=3438258.19..3438258.19 rows=119480 width=99) (actual time=46157.41..48845.75 rows=1025405loops=1) -> Hash Join (cost=5403.12..3438258.19 rows=119480 width=99) (actual time=1344.17..43964.04 rows=1025405loops=1) -> Merge Join (cost=5392.13..3405513.72 rows=871449 width=77) (actual time=1336.38..23857.31rows=1035128 loops=1) -> Sort (cost=5392.13..5392.13 rows=28443 width=65) (actual time=1323.65..1383.10 rows=31471loops=1) -> Hash Join (cost=54.91..2615.14 rows=28443 width=65) (actual time=21.62..901.24rows=33593 loops=1) -> Hash Join (cost=50.15..2112.63 rows=28443 width=38) (actual time=11.27..646.34rows=33593 loops=1) -> Hash Join (cost=43.48..1537.09 rows=28443 width=34) (actual time=10.80..443.36rows=33593 loops=1) -> Index Scan using datasets_setid_idx on datasets (cost=0.00..969.14rows=33786 width=22) (actual time=0.64..249.20 rows=33786 loops=1) -> Hash (cost=42.53..42.53 rows=378 width=12) (actual time=10.08..10.08rows=0 loops=1) -> Hash Join (cost=6.68..42.53 rows=378 width=12) (actualtime=0.53..9.39 rows=377 loops=1) -> Index Scan using reports_pkey on reports (cost=0.00..28.29rows=378 width=8) (actual time=0.07..7.25 rows=378 loops=1) -> Hash (cost=6.50..6.50 rows=71 width=4) (actual time=0.38..0.38rows=0 loops=1) -> Index Scan using areas_pkey on areas (cost=0.00..6.50rows=71 width=4) (actual time=0.01..0.24 rows=71 loops=1) -> Hash (cost=6.50..6.50 rows=71 width=4) (actual time=0.40..0.40 rows=0loops=1) -> Index Scan using areas_pkey on areas (cost=0.00..6.50 rows=71width=4) (actual time=0.02..0.27 rows=71 loops=1) -> Hash (cost=4.71..4.71 rows=18 width=27) (actual time=10.26..10.26 rows=0loops=1) -> Index Scan using datatypes_pkey on datatypes (cost=0.00..4.71 rows=18width=27) (actual time=10.15..10.23 rows=18 loops=1) -> Index Scan using lhsecgriemis_setid_idx on lowhisectorgridemissions (cost=0.00..3386569.53rows=1035134 width=12) (actual time=12.67..13841.30 rows=1035134 loops=1) -> Hash (cost=10.72..10.72 rows=109 width=22) (actual time=7.68..7.68 rows=0 loops=1) -> Index Scan using sectordefinitions_pkey on sectordefinitions (cost=0.00..10.72 rows=109width=22) (actual time=6.74..7.46 rows=109 loops=1) -> Hash (cost=17.09..17.09 rows=1 width=28) (actual time=27.32..27.32 rows=0 loops=1) -> Nested Loop (cost=0.00..17.09 rows=1 width=28) (actual time=27.29..27.31 rows=1 loops=1) -> Nested Loop (cost=0.00..12.55 rows=1 width=24) (actual time=27.26..27.28 rows=1 loops=1) -> Nested Loop (cost=0.00..8.01 rows=1 width=20) (actual time=12.04..12.05 rows=1 loops=1) -> Index Scan using datasets_setid_idx on datasets (cost=0.00..3.36 rows=1 width=12) (actualtime=4.61..4.62 rows=1 loops=1) -> Index Scan using reports_pkey on reports (cost=0.00..4.64 rows=1 width=8) (actual time=7.41..7.41rows=1 loops=1) -> Index Scan using areas_pkey on areas (cost=0.00..4.52 rows=1 width=4) (actual time=15.21..15.21rows=1 loops=1) -> Index Scan using areas_pkey on areas (cost=0.00..4.52 rows=1 width=4) (actual time=0.02..0.02 rows=1loops=1) Total runtime: 61309.75 msec EXPLAIN
В списке pgsql-general по дате отправления:
Предыдущее
От: "Luis Alberto Amigo Navarro"Дата:
Сообщение: Re: Huge Performance Difference on Similar Query in Pg7.2