Skip to content

Instantly share code, notes, and snippets.

@danypr92

danypr92/explain_analyze_materialized_view.out

Last active March 21, 2018 10:54
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save danypr92/113f006bf1516aab81c93e34fdc74e90 to your computer and use it in GitHub Desktop.
Save danypr92/113f006bf1516aab81c93e34fdc74e90 to your computer and use it in GitHub Desktop.
Download ZIP
Difference between `VIEW` or `MATERIALIZED VIEW` performance cost in PostgreSQL 9.5.
Raw
explain_analyze_materialized_view.out
test-readonly=# EXPLAIN ANALYZE(select * from party_materialized_view);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Seq Scan on party_materialized_view (cost=0.00..138.04 rows=1904 width=473) (actual time=0.018..1.022 rows=5235 loops=1)
Planning time: 0.131 ms
Execution time: 1.271 ms
(3 rows)
test-readonly=# EXPLAIN ANALYZE(select * from party_materialized_view where identifier_code = 'ESX640B');
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Seq Scan on party_materialized_view (cost=0.00..184.44 rows=1 width=149) (actual time=1.631..1.632 rows=1 loops=1)
Filter: ((identifier_code)::text = 'ESX640B'::text)
Rows Removed by Filter: 5234
Planning time: 0.073 ms
Execution time: 1.659 ms
(5 rows)
Raw
explain_analyze_view.out
test-readonly=# EXPLAIN ANALYZE(select * from party_view);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on party_view (cost=3116.95..3211.15 rows=5233 width=181) (actual time=39.240..43.625 rows=5235 loops=1)
-> Unique (cost=3116.95..3158.82 rows=5233 width=190) (actual time=39.239..42.451 rows=5235 loops=1)
-> Sort (cost=3116.95..3137.88 rows=8373 width=190) (actual time=39.238..39.866 rows=10214 loops=1)
Sort Key: party.id, address.invoice DESC, address.id
Sort Method: quicksort Memory: 3003kB
-> Hash Right Join (cost=1990.09..2571.39 rows=8373 width=190) (actual time=19.733..28.831 rows=10214 loops=1)
Hash Cond: (address.party = party.id)
-> Hash Left Join (cost=173.66..640.05 rows=8314 width=82) (actual time=1.249..6.743 rows=8316 loops=1)
Hash Cond: (address.subdivision = subdivision.id)
-> Hash Left Join (cost=8.60..361.57 rows=8314 width=69) (actual time=0.079..3.740 rows=8316 loops=1)
Hash Cond: (address.country = country.id)
-> Seq Scan on party_address address (cost=0.00..239.10 rows=8314 width=58) (actual time=0.004..1.787 rows=8316 loops=1)
Filter: (active IS TRUE)
Rows Removed by Filter: 396
-> Hash (cost=5.49..5.49 rows=249 width=19) (actual time=0.066..0.066 rows=249 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 21kB
-> Seq Scan on country_country country (cost=0.00..5.49 rows=249 width=19) (actual time=0.006..0.034 rows=249 loops=1)
-> Hash (cost=104.47..104.47 rows=4847 width=21) (actual time=1.164..1.164 rows=4847 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 325kB
-> Seq Scan on country_subdivision subdivision (cost=0.00..104.47 rows=4847 width=21) (actual time=0.003..0.559 rows=4847 loops=1)
-> Hash (cost=1750.56..1750.56 rows=5270 width=112) (actual time=18.478..18.478 rows=5736 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 775kB
-> Hash Left Join (cost=880.09..1750.56 rows=5270 width=112) (actual time=5.296..16.953 rows=5736 loops=1)
Hash Cond: ((property.value)::text = ('ir.lang,'::text || (language.id)::text))
-> Hash Left Join (cost=878.69..1705.49 rows=5270 width=99) (actual time=5.283..14.784 rows=5736 loops=1)
Hash Cond: (('party.party,'::text || (party.id)::text) = (property.res)::text)
-> Hash Left Join (cost=373.43..873.94 rows=5270 width=80) (actual time=3.337..9.413 rows=5736 loops=1)
Hash Cond: (party.id = identifier.party)
-> Hash Right Join (cost=203.74..632.27 rows=5270 width=62) (actual time=2.060..6.368 rows=5736 loops=1)
Hash Cond: (contactmethod.party = party.id)
-> Seq Scan on party_contact_mechanism contactmethod (cost=0.00..356.06 rows=5270 width=15) (actual time=0.132..2.453 rows=5271 loops=1)
Filter: ((type)::text = 'email'::text)
Rows Removed by Filter: 7376
-> Hash (cost=138.33..138.33 rows=5233 width=51) (actual time=1.923..1.923 rows=5235 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 517kB
-> Seq Scan on party_party party (cost=0.00..138.33 rows=5233 width=51) (actual time=0.004..1.023 rows=5235 loops=1)
-> Hash (cost=104.86..104.86 rows=5186 width=22) (actual time=1.272..1.272 rows=5187 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 348kB
-> Seq Scan on party_identifier identifier (cost=0.00..104.86 rows=5186 width=22) (actual time=0.004..0.649 rows=5187 loops=1)
-> Hash (cost=439.62..439.62 rows=5251 width=36) (actual time=1.939..1.939 rows=5252 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 368kB
-> Bitmap Heap Scan on ir_property property (cost=100.98..439.62 rows=5251 width=36) (actual time=0.295..1.194 rows=5253 loops=1)
Recheck Cond: (field = 970)
Heap Blocks: exact=168
-> Bitmap Index Scan on ir_property_field_index (cost=0.00..99.67 rows=5251 width=0) (actual time=0.280..0.280 rows=5253 loops=1)
Index Cond: (field = 970)
-> Hash (cost=1.18..1.18 rows=18 width=36) (actual time=0.010..0.010 rows=18 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on ir_lang language (cost=0.00..1.18 rows=18 width=36) (actual time=0.002..0.007 rows=18 loops=1)
Planning time: 1.701 ms
Execution time: 43.954 ms
(51 rows)
Raw
explain_analyze_view_party_contact_mechanism_type_index.out.out
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on party_otrs_view (cost=3117.17..3224.45 rows=26 width=181) (actual time=46.379..46.380 rows=1 loops=1)
Filter: ((party_otrs_view.identifier_code)::text = 'ESX2431640B'::text)
Rows Removed by Filter: 5234
-> Unique (cost=3117.17..3159.04 rows=5233 width=190) (actual time=43.035..45.735 rows=5235 loops=1)
-> Sort (cost=3117.17..3138.10 rows=8374 width=190) (actual time=43.032..43.492 rows=10214 loops=1)
Sort Key: party.id, address.invoice DESC, address.id
Sort Method: quicksort Memory: 3003kB
-> Hash Right Join (cost=1990.23..2571.53 rows=8374 width=190) (actual time=22.585..31.984 rows=10214 loops=1)
Hash Cond: (address.party = party.id)
-> Hash Left Join (cost=173.66..640.05 rows=8314 width=82) (actual time=1.845..7.620 rows=8316 loops=1)
Hash Cond: (address.subdivision = subdivision.id)
-> Hash Left Join (cost=8.60..361.57 rows=8314 width=69) (actual time=0.152..4.163 rows=8316 loops=1)
Hash Cond: (address.country = country.id)
-> Seq Scan on party_address address (cost=0.00..239.10 rows=8314 width=58) (actual time=0.015..2.248 rows=8316 loops=1)
Filter: (active IS TRUE)
Rows Removed by Filter: 396
-> Hash (cost=5.49..5.49 rows=249 width=19) (actual time=0.126..0.126 rows=249 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 21kB
-> Seq Scan on country_country country (cost=0.00..5.49 rows=249 width=19) (actual time=0.009..0.065 rows=249 loops=1)
-> Hash (cost=104.47..104.47 rows=4847 width=21) (actual time=1.656..1.656 rows=4847 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 325kB
-> Seq Scan on country_subdivision subdivision (cost=0.00..104.47 rows=4847 width=21) (actual time=0.008..0.870 rows=4847 loops=1)
-> Hash (cost=1750.68..1750.68 rows=5271 width=112) (actual time=20.710..20.710 rows=5736 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 775kB
-> Hash Left Join (cost=880.09..1750.68 rows=5271 width=112) (actual time=8.179..19.068 rows=5736 loops=1)
Hash Cond: ((property.value)::text = ('ir.lang,'::text || (language.id)::text))
-> Hash Left Join (cost=878.69..1705.60 rows=5271 width=99) (actual time=8.151..17.065 rows=5736 loops=1)
Hash Cond: (('party.party,'::text || (party.id)::text) = (property.res)::text)
-> Hash Left Join (cost=373.43..874.00 rows=5271 width=80) (actual time=4.575..10.499 rows=5736 loops=1)
Hash Cond: (party.id = identifier.party)
-> Hash Right Join (cost=203.74..632.31 rows=5271 width=62) (actual time=2.847..6.999 rows=5736 loops=1)
Hash Cond: (contactmethod.party = party.id)
-> Seq Scan on party_contact_mechanism contactmethod (cost=0.00..356.09 rows=5271 width=15) (actual time=0.142..2.375 rows=5271 loops=1)
Filter: ((type)::text = 'email'::text)
Rows Removed by Filter: 7376
-> Hash (cost=138.33..138.33 rows=5233 width=51) (actual time=2.683..2.683 rows=5235 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 517kB
-> Seq Scan on party_party party (cost=0.00..138.33 rows=5233 width=51) (actual time=0.011..1.512 rows=5235 loops=1)
-> Hash (cost=104.86..104.86 rows=5186 width=22) (actual time=1.705..1.705 rows=5187 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 348kB
-> Seq Scan on party_identifier identifier (cost=0.00..104.86 rows=5186 width=22) (actual time=0.009..0.917 rows=5187 loops=1)
-> Hash (cost=439.62..439.62 rows=5251 width=36) (actual time=3.536..3.536 rows=5252 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 368kB
-> Bitmap Heap Scan on ir_property property (cost=100.98..439.62 rows=5251 width=36) (actual time=0.387..2.424 rows=5253 loops=1)
Recheck Cond: (field = 970)
Heap Blocks: exact=168
-> Bitmap Index Scan on ir_property_field_index (cost=0.00..99.67 rows=5251 width=0) (actual time=0.359..0.359 rows=5253 loops=1)
Index Cond: (field = 970)
-> Hash (cost=1.18..1.18 rows=18 width=36) (actual time=0.013..0.013 rows=18 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on ir_lang language (cost=0.00..1.18 rows=18 width=36) (actual time=0.002..0.005 rows=18 loops=1)
Planning time: 2.841 ms
Execution time: 46.664 ms
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment