Skip to content

Instantly share code, notes, and snippets.

@kbarber

kbarber/facts-master

Created August 13, 2014 21:36
Show Gist options
  • Save kbarber/8d56a5134dc78579ad01 to your computer and use it in GitHub Desktop.
Save kbarber/8d56a5134dc78579ad01 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
facts-master
Branch pdb-809 (master basically) with 1 node of facts:
Expanded display is used automatically.
Timing is on.
Null display is "<<NULL>>".
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=689.44..711.69 rows=72 width=730) (actual time=3.193..3.810 rows=102 loops=1)
Hash Cond: (fs.certname = certnames.name)
-> Merge Semi Join (cost=636.99..658.69 rows=144 width=762) (actual time=3.140..3.707 rows=102 loops=1)
Merge Cond: (((fp.name)::text = (fp_1.name)::text) AND (fs.certname = fs_1.certname))
Join Filter: (env.name = env_1.name)
-> Sort (cost=274.91..277.79 rows=1150 width=747) (actual time=1.251..1.259 rows=102 loops=1)
Sort Key: fp.name, fs.certname
Sort Method: quicksort Memory: 40kB
-> Hash Join (cost=140.43..216.45 rows=1150 width=747) (actual time=0.430..0.546 rows=102 loops=1)
Hash Cond: (f.factset_id = fs.id)
-> Hash Join (cost=63.15..108.99 rows=1150 width=691) (actual time=0.399..0.464 rows=102 loops=1)
Hash Cond: (f.fact_value_id = fv.id)
-> Seq Scan on facts f (cost=0.00..27.70 rows=1770 width=16) (actual time=0.008..0.019 rows=102 loops=1)
-> Hash (cost=59.90..59.90 rows=260 width=691) (actual time=0.360..0.360 rows=113 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Hash Join (cost=38.23..59.90 rows=260 width=691) (actual time=0.125..0.283 rows=113 loops=1)
Hash Cond: (fv.value_type_id = vt.id)
-> Hash Join (cost=12.93..31.03 rows=260 width=617) (actual time=0.108..0.205 rows=113 loops=1)
Hash Cond: (fv.path_id = fp.id)
-> Seq Scan on fact_values fv (cost=0.00..14.00 rows=400 width=73) (actual time=0.012..0.036 rows=113 loops=1)
-> Hash (cost=11.30..11.30 rows=130 width=560) (actual time=0.082..0.082 rows=105 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 8kB
-> Seq Scan on fact_paths fp (cost=0.00..11.30 rows=130 width=560) (actual time=0.009..0.031 rows=105 loops=1)
-> Hash (cost=16.80..16.80 rows=680 width=90) (actual time=0.008..0.008 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on value_types vt (cost=0.00..16.80 rows=680 width=90) (actual time=0.005..0.006 rows=5 loops=1)
-> Hash (cost=66.53..66.53 rows=860 width=72) (actual time=0.020..0.020 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Hash Left Join (cost=36.10..66.53 rows=860 width=72) (actual time=0.019..0.019 rows=1 loops=1)
Hash Cond: (fs.environment_id = env.id)
-> Seq Scan on factsets fs (cost=0.00..18.60 rows=860 width=48) (actual time=0.004..0.004 rows=1 loops=1)
-> Hash (cost=21.60..21.60 rows=1160 width=40) (actual time=0.006..0.006 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on environments env (cost=0.00..21.60 rows=1160 width=40) (actual time=0.004..0.004 rows=1 loops=1)
-> Sort (cost=362.07..362.57 rows=200 width=580) (actual time=1.879..1.883 rows=102 loops=1)
Sort Key: fp_1.name, fs_1.certname
Sort Method: quicksort Memory: 32kB
-> HashAggregate (cost=350.43..352.43 rows=200 width=580) (actual time=1.263..1.285 rows=102 loops=1)
-> Hash Join (cost=327.37..346.12 rows=575 width=580) (actual time=1.127..1.197 rows=102 loops=1)
Hash Cond: (fs_1.certname = certnames_1.name)
-> Sort (cost=274.91..277.79 rows=1150 width=747) (actual time=1.094..1.105 rows=102 loops=1)
Sort Key: fp_1.name, fs_1.certname
Sort Method: quicksort Memory: 40kB
-> Hash Join (cost=140.43..216.45 rows=1150 width=747) (actual time=0.332..0.450 rows=102 loops=1)
Hash Cond: (f_1.factset_id = fs_1.id)
-> Hash Join (cost=63.15..108.99 rows=1150 width=691) (actual time=0.310..0.378 rows=102 loops=1)
Hash Cond: (f_1.fact_value_id = fv_1.id)
-> Seq Scan on facts f_1 (cost=0.00..27.70 rows=1770 width=16) (actual time=0.004..0.011 rows=102 loops=1)
-> Hash (cost=59.90..59.90 rows=260 width=691) (actual time=0.296..0.296 rows=113 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Hash Join (cost=38.23..59.90 rows=260 width=691) (actual time=0.099..0.227 rows=113 loops=1)
Hash Cond: (fv_1.value_type_id = vt_1.id)
-> Hash Join (cost=12.93..31.03 rows=260 width=617) (actual time=0.083..0.163 rows=113 loops=1)
Hash Cond: (fv_1.path_id = fp_1.id)
-> Seq Scan on fact_values fv_1 (cost=0.00..14.00 rows=400 width=73) (actual time=0.002..0.016 rows=113 loops=1)
-> Hash (cost=11.30..11.30 rows=130 width=560) (actual time=0.073..0.073 rows=105 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 8kB
-> Seq Scan on fact_paths fp_1 (cost=0.00..11.30 rows=130 width=560) (actual time=0.003..0.032 rows=105 loops=1)
-> Hash (cost=16.80..16.80 rows=680 width=90) (actual time=0.004..0.004 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on value_types vt_1 (cost=0.00..16.80 rows=680 width=90) (actual time=0.001..0.001 rows=5 loops=1)
-> Hash (cost=66.53..66.53 rows=860 width=72) (actual time=0.013..0.013 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Hash Left Join (cost=36.10..66.53 rows=860 width=72) (actual time=0.012..0.013 rows=1 loops=1)
Hash Cond: (fs_1.environment_id = env_1.id)
-> Seq Scan on factsets fs_1 (cost=0.00..18.60 rows=860 width=48) (actual time=0.001..0.002 rows=1 loops=1)
-> Hash (cost=21.60..21.60 rows=1160 width=40) (actual time=0.002..0.002 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on environments env_1 (cost=0.00..21.60 rows=1160 width=40) (actual time=0.001..0.001 rows=1 loops=1)
-> Hash (cost=52.38..52.38 rows=6 width=32) (actual time=0.021..0.021 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> HashAggregate (cost=52.32..52.38 rows=6 width=32) (actual time=0.020..0.020 rows=1 loops=1)
-> Hash Left Join (cost=30.67..52.31 rows=6 width=32) (actual time=0.017..0.018 rows=1 loops=1)
Hash Cond: (certnames_1.name = reports_1.certname)
-> Seq Scan on certnames certnames_1 (cost=0.00..21.60 rows=6 width=32) (actual time=0.002..0.002 rows=1 loops=1)
Filter: (deactivated IS NULL)
-> Hash (cost=30.42..30.42 rows=20 width=32) (actual time=0.008..0.008 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 0kB
-> Hash Join (cost=27.27..30.42 rows=20 width=32) (actual time=0.008..0.008 rows=0 loops=1)
Hash Cond: ((latest_reports_1.report)::text = (reports_1.hash)::text)
-> HashAggregate (cost=16.38..18.38 rows=200 width=98) (never executed)
-> Seq Scan on latest_reports latest_reports_1 (cost=0.00..15.10 rows=510 width=98) (never executed)
-> Hash (cost=10.40..10.40 rows=40 width=134) (actual time=0.001..0.001 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 0kB
-> Seq Scan on reports reports_1 (cost=0.00..10.40 rows=40 width=134) (actual time=0.000..0.000 rows=0 loops=1)
-> Hash (cost=52.38..52.38 rows=6 width=32) (actual time=0.041..0.041 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> HashAggregate (cost=52.32..52.38 rows=6 width=32) (actual time=0.041..0.041 rows=1 loops=1)
-> Hash Left Join (cost=30.67..52.31 rows=6 width=32) (actual time=0.035..0.035 rows=1 loops=1)
Hash Cond: (certnames.name = reports.certname)
-> Seq Scan on certnames (cost=0.00..21.60 rows=6 width=32) (actual time=0.013..0.013 rows=1 loops=1)
Filter: (deactivated IS NULL)
-> Hash (cost=30.42..30.42 rows=20 width=32) (actual time=0.010..0.010 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 0kB
-> Hash Join (cost=27.27..30.42 rows=20 width=32) (actual time=0.010..0.010 rows=0 loops=1)
Hash Cond: ((latest_reports.report)::text = (reports.hash)::text)
-> HashAggregate (cost=16.38..18.38 rows=200 width=98) (never executed)
-> Seq Scan on latest_reports (cost=0.00..15.10 rows=510 width=98) (never executed)
-> Hash (cost=10.40..10.40 rows=40 width=134) (actual time=0.001..0.001 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 0kB
-> Seq Scan on reports (cost=0.00..10.40 rows=40 width=134) (actual time=0.001..0.001 rows=0 loops=1)
Total runtime: 4.308 ms
(102 rows)
Time: 20.755 ms
=======================================================
Test with 100 nodes worth of facts:
Expanded display is used automatically.
Timing is on.
Null display is "<<NULL>>".
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Semi Join (cost=4792.49..4975.83 rows=1 width=183) (actual time=779.846..994.715 rows=10200 loops=1)
-> Merge Semi Join (cost=4792.06..4971.32 rows=1 width=190) (actual time=779.800..881.944 rows=10200 loops=1)
Merge Cond: (((fp.name)::text = (fp_1.name)::text) AND (fs.certname = fs_1.certname))
Join Filter: (env.name = env_1.name)
-> Sort (cost=2298.05..2323.55 rows=10200 width=195) (actual time=240.772..263.506 rows=10200 loops=1)
Sort Key: fp.name, fs.certname
Sort Method: external merge Disk: 872kB
-> Hash Join (cost=76.42..642.42 rows=10200 width=195) (actual time=0.512..14.422 rows=10200 loops=1)
Hash Cond: (f.factset_id = fs.id)
-> Hash Join (cost=35.70..333.94 rows=10200 width=164) (actual time=0.377..8.591 rows=10200 loops=1)
Hash Cond: (f.fact_value_id = fv.id)
-> Seq Scan on facts f (cost=0.00..158.00 rows=10200 width=16) (actual time=0.013..1.820 rows=10200 loops=1)
-> Hash (cost=34.42..34.42 rows=102 width=164) (actual time=0.323..0.323 rows=102 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
-> Hash Join (cost=28.59..34.42 rows=102 width=164) (actual time=0.126..0.251 rows=102 loops=1)
Hash Cond: (fv.value_type_id = vt.id)
-> Hash Join (cost=3.29..7.72 rows=102 width=90) (actual time=0.104..0.178 rows=102 loops=1)
Hash Cond: (fv.path_id = fp.id)
-> Seq Scan on fact_values fv (cost=0.00..3.02 rows=102 width=68) (actual time=0.003..0.017 rows=102 loops=1)
-> Hash (cost=2.02..2.02 rows=102 width=38) (actual time=0.082..0.082 rows=102 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 8kB
-> Seq Scan on fact_paths fp (cost=0.00..2.02 rows=102 width=38) (actual time=0.004..0.031 rows=102 loops=1)
-> Hash (cost=16.80..16.80 rows=680 width=90) (actual time=0.009..0.009 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on value_types vt (cost=0.00..16.80 rows=680 width=90) (actual time=0.006..0.008 rows=5 loops=1)
-> Hash (cost=39.48..39.48 rows=100 width=47) (actual time=0.121..0.121 rows=100 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 6kB
-> Hash Left Join (cost=36.10..39.48 rows=100 width=47) (actual time=0.020..0.087 rows=100 loops=1)
Hash Cond: (fs.environment_id = env.id)
-> Seq Scan on factsets fs (cost=0.00..2.00 rows=100 width=23) (actual time=0.004..0.008 rows=100 loops=1)
-> Hash (cost=21.60..21.60 rows=1160 width=40) (actual time=0.005..0.005 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on environments env (cost=0.00..21.60 rows=1160 width=40) (actual time=0.004..0.004 rows=1 loops=1)
-> Sort (cost=2494.01..2494.26 rows=100 width=52) (actual time=539.004..542.410 rows=10200 loops=1)
Sort Key: fp_1.name, fs_1.certname
Sort Method: external sort Disk: 392kB
-> HashAggregate (cost=2488.69..2489.69 rows=100 width=52) (actual time=265.516..267.752 rows=10200 loops=1)
-> Hash Semi Join (cost=2332.55..2487.94 rows=100 width=52) (actual time=232.393..257.428 rows=10200 loops=1)
Hash Cond: (fs_1.certname = certnames_1.name)
-> Sort (cost=2298.05..2323.55 rows=10200 width=195) (actual time=232.241..250.614 rows=10200 loops=1)
Sort Key: fp_1.name, fs_1.certname
Sort Method: external merge Disk: 872kB
-> Hash Join (cost=76.42..642.42 rows=10200 width=195) (actual time=0.463..13.827 rows=10200 loops=1)
Hash Cond: (f_1.factset_id = fs_1.id)
-> Hash Join (cost=35.70..333.94 rows=10200 width=164) (actual time=0.331..8.141 rows=10200 loops=1)
Hash Cond: (f_1.fact_value_id = fv_1.id)
-> Seq Scan on facts f_1 (cost=0.00..158.00 rows=10200 width=16) (actual time=0.006..1.542 rows=10200 loops=1)
-> Hash (cost=34.42..34.42 rows=102 width=164) (actual time=0.311..0.311 rows=102 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
-> Hash Join (cost=28.59..34.42 rows=102 width=164) (actual time=0.121..0.247 rows=102 loops=1)
Hash Cond: (fv_1.value_type_id = vt_1.id)
-> Hash Join (cost=3.29..7.72 rows=102 width=90) (actual time=0.104..0.182 rows=102 loops=1)
Hash Cond: (fv_1.path_id = fp_1.id)
-> Seq Scan on fact_values fv_1 (cost=0.00..3.02 rows=102 width=68) (actual time=0.003..0.017 rows=102 loops=1)
-> Hash (cost=2.02..2.02 rows=102 width=38) (actual time=0.082..0.082 rows=102 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 8kB
-> Seq Scan on fact_paths fp_1 (cost=0.00..2.02 rows=102 width=38) (actual time=0.005..0.026 rows=102 loops=1)
-> Hash (cost=16.80..16.80 rows=680 width=90) (actual time=0.006..0.006 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on value_types vt_1 (cost=0.00..16.80 rows=680 width=90) (actual time=0.002..0.004 rows=5 loops=1)
-> Hash (cost=39.48..39.48 rows=100 width=47) (actual time=0.118..0.118 rows=100 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 6kB
-> Hash Left Join (cost=36.10..39.48 rows=100 width=47) (actual time=0.018..0.076 rows=100 loops=1)
Hash Cond: (fs_1.environment_id = env_1.id)
-> Seq Scan on factsets fs_1 (cost=0.00..2.00 rows=100 width=23) (actual time=0.004..0.016 rows=100 loops=1)
-> Hash (cost=21.60..21.60 rows=1160 width=40) (actual time=0.003..0.003 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on environments env_1 (cost=0.00..21.60 rows=1160 width=40) (actual time=0.002..0.002 rows=1 loops=1)
-> Hash (cost=33.25..33.25 rows=100 width=7) (actual time=0.120..0.120 rows=100 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 4kB
-> Hash Left Join (cost=30.67..33.25 rows=100 width=7) (actual time=0.036..0.088 rows=100 loops=1)
Hash Cond: (certnames_1.name = reports_1.certname)
-> Seq Scan on certnames certnames_1 (cost=0.00..2.00 rows=100 width=7) (actual time=0.011..0.022 rows=100 loops=1)
Filter: (deactivated IS NULL)
-> Hash (cost=30.42..30.42 rows=20 width=32) (actual time=0.010..0.010 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 0kB
-> Hash Join (cost=27.27..30.42 rows=20 width=32) (actual time=0.010..0.010 rows=0 loops=1)
Hash Cond: ((latest_reports_1.report)::text = (reports_1.hash)::text)
-> HashAggregate (cost=16.38..18.38 rows=200 width=98) (never executed)
-> Seq Scan on latest_reports latest_reports_1 (cost=0.00..15.10 rows=510 width=98) (never executed)
-> Hash (cost=10.40..10.40 rows=40 width=134) (actual time=0.001..0.001 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 0kB
-> Seq Scan on reports reports_1 (cost=0.00..10.40 rows=40 width=134) (actual time=0.001..0.001 rows=0 loops=1)
-> Nested Loop Left Join (cost=0.43..2.47 rows=1 width=7) (actual time=0.010..0.010 rows=1 loops=10200)
-> Index Scan using certnames_pkey on certnames (cost=0.14..0.16 rows=1 width=7) (actual time=0.008..0.008 rows=1 loops=10200)
Index Cond: (name = fs.certname)
Filter: (deactivated IS NULL)
-> Nested Loop Semi Join (cost=0.29..2.29 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=10200)
-> Index Scan using idx_reports_certname on reports (cost=0.14..0.60 rows=1 width=134) (actual time=0.001..0.001 rows=0 loops=10200)
Index Cond: (certnames.name = certname)
-> Index Only Scan using idx_latest_reports_report on latest_reports (cost=0.15..1.31 rows=3 width=98) (never executed)
Index Cond: (report = (reports.hash)::text)
Heap Fetches: 0
Total runtime: 1004.305 ms
(94 rows)
Time: 1022.271 ms
=======================================================
With 1000 nodes:
Expanded display is used automatically.
Timing is on.
Null display is "<<NULL>>".
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=69526.12..71089.49 rows=92 width=184) (actual time=6987.667..8144.871 rows=102000 loops=1)
Hash Cond: ((certnames.name = "ANY_subquery".certname) AND ((fp.name)::text = ("ANY_subquery".name)::text) AND (env.name = "ANY_subquery".environment))
-> Hash Semi Join (cost=33969.88..35524.95 rows=737 width=192) (actual time=2890.495..3783.233 rows=102000 loops=1)
Hash Cond: (fs.certname = certnames.name)
-> Sort (cost=33902.66..34158.34 rows=102272 width=196) (actual time=2889.490..3707.684 rows=102000 loops=1)
Sort Key: fp.name, fs.certname
Sort Method: external merge Disk: 8920kB
-> Hash Join (cost=135.98..5813.58 rows=102272 width=196) (actual time=1.548..154.415 rows=102000 loops=1)
Hash Cond: (f.factset_id = fs.id)
-> Hash Join (cost=35.70..3028.66 rows=102272 width=164) (actual time=0.348..93.384 rows=102000 loops=1)
Hash Cond: (f.fact_value_id = fv.id)
-> Seq Scan on facts f (cost=0.00..1586.72 rows=102272 width=16) (actual time=0.006..21.044 rows=102000 loops=1)
-> Hash (cost=34.42..34.42 rows=102 width=164) (actual time=0.323..0.323 rows=106 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Hash Join (cost=28.59..34.42 rows=102 width=164) (actual time=0.110..0.244 rows=106 loops=1)
Hash Cond: (fv.value_type_id = vt.id)
-> Hash Join (cost=3.29..7.72 rows=102 width=90) (actual time=0.093..0.178 rows=106 loops=1)
Hash Cond: (fv.path_id = fp.id)
-> Seq Scan on fact_values fv (cost=0.00..3.02 rows=102 width=68) (actual time=0.004..0.019 rows=106 loops=1)
-> Hash (cost=2.02..2.02 rows=102 width=38) (actual time=0.076..0.076 rows=102 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 8kB
-> Seq Scan on fact_paths fp (cost=0.00..2.02 rows=102 width=38) (actual time=0.004..0.029 rows=102 loops=1)
-> Hash (cost=16.80..16.80 rows=680 width=90) (actual time=0.009..0.009 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on value_types vt (cost=0.00..16.80 rows=680 width=90) (actual time=0.005..0.005 rows=5 loops=1)
-> Hash (cost=86.77..86.77 rows=1081 width=48) (actual time=1.189..1.189 rows=1000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 58kB
-> Hash Left Join (cost=36.10..86.77 rows=1081 width=48) (actual time=0.020..0.747 rows=1000 loops=1)
Hash Cond: (fs.environment_id = env.id)
-> Seq Scan on factsets fs (cost=0.00..35.81 rows=1081 width=24) (actual time=0.004..0.249 rows=1000 loops=1)
-> Hash (cost=21.60..21.60 rows=1160 width=40) (actual time=0.005..0.005 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on environments env (cost=0.00..21.60 rows=1160 width=40) (actual time=0.004..0.004 rows=1 loops=1)
-> Hash (cost=58.01..58.01 rows=737 width=8) (actual time=0.979..0.979 rows=1000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 40kB
-> Hash Left Join (cost=30.67..58.01 rows=737 width=8) (actual time=0.035..0.621 rows=1000 loops=1)
Hash Cond: (certnames.name = reports.certname)
-> Seq Scan on certnames (cost=0.00..24.37 rows=737 width=8) (actual time=0.012..0.273 rows=1000 loops=1)
Filter: (deactivated IS NULL)
-> Hash (cost=30.42..30.42 rows=20 width=32) (actual time=0.009..0.009 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 0kB
-> Hash Join (cost=27.27..30.42 rows=20 width=32) (actual time=0.009..0.009 rows=0 loops=1)
Hash Cond: ((latest_reports.report)::text = (reports.hash)::text)
-> HashAggregate (cost=16.38..18.38 rows=200 width=98) (never executed)
-> Seq Scan on latest_reports (cost=0.00..15.10 rows=510 width=98) (never executed)
-> Hash (cost=10.40..10.40 rows=40 width=134) (actual time=0.001..0.001 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 0kB
-> Seq Scan on reports (cost=0.00..10.40 rows=40 width=134) (actual time=0.001..0.001 rows=0 loops=1)
-> Hash (cost=35552.74..35552.74 rows=200 width=53) (actual time=4096.560..4096.560 rows=102000 loops=1)
Buckets: 1024 Batches: 8 (originally 1) Memory Usage: 1025kB
-> HashAggregate (cost=35550.74..35552.74 rows=200 width=53) (actual time=4002.764..4027.534 rows=102000 loops=1)
-> Subquery Scan on "ANY_subquery" (cost=35530.47..35545.21 rows=737 width=53) (actual time=3862.110..3922.854 rows=102000 loops=1)
-> HashAggregate (cost=35530.47..35537.84 rows=737 width=53) (actual time=3862.103..3902.084 rows=102000 loops=1)
-> Hash Semi Join (cost=33969.88..35524.95 rows=737 width=53) (actual time=2870.102..3767.592 rows=102000 loops=1)
Hash Cond: (fs_1.certname = certnames_1.name)
-> Sort (cost=33902.66..34158.34 rows=102272 width=196) (actual time=2869.195..3693.380 rows=102000 loops=1)
Sort Key: fp_1.name, fs_1.certname
Sort Method: external merge Disk: 8920kB
-> Hash Join (cost=135.98..5813.58 rows=102272 width=196) (actual time=1.464..149.222 rows=102000 loops=1)
Hash Cond: (f_1.factset_id = fs_1.id)
-> Hash Join (cost=35.70..3028.66 rows=102272 width=164) (actual time=0.360..89.192 rows=102000 loops=1)
Hash Cond: (f_1.fact_value_id = fv_1.id)
-> Seq Scan on facts f_1 (cost=0.00..1586.72 rows=102272 width=16) (actual time=0.006..17.030 rows=102000 loops=1)
-> Hash (cost=34.42..34.42 rows=102 width=164) (actual time=0.338..0.338 rows=106 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Hash Join (cost=28.59..34.42 rows=102 width=164) (actual time=0.145..0.279 rows=106 loops=1)
Hash Cond: (fv_1.value_type_id = vt_1.id)
-> Hash Join (cost=3.29..7.72 rows=102 width=90) (actual time=0.128..0.207 rows=106 loops=1)
Hash Cond: (fv_1.path_id = fp_1.id)
-> Seq Scan on fact_values fv_1 (cost=0.00..3.02 rows=102 width=68) (actual time=0.004..0.020 rows=106 loops=1)
-> Hash (cost=2.02..2.02 rows=102 width=38) (actual time=0.110..0.110 rows=102 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 8kB
-> Seq Scan on fact_paths fp_1 (cost=0.00..2.02 rows=102 width=38) (actual time=0.005..0.031 rows=102 loops=1)
-> Hash (cost=16.80..16.80 rows=680 width=90) (actual time=0.006..0.006 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on value_types vt_1 (cost=0.00..16.80 rows=680 width=90) (actual time=0.003..0.003 rows=5 loops=1)
-> Hash (cost=86.77..86.77 rows=1081 width=48) (actual time=1.091..1.091 rows=1000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 58kB
-> Hash Left Join (cost=36.10..86.77 rows=1081 width=48) (actual time=0.023..0.684 rows=1000 loops=1)
Hash Cond: (fs_1.environment_id = env_1.id)
-> Seq Scan on factsets fs_1 (cost=0.00..35.81 rows=1081 width=24) (actual time=0.005..0.162 rows=1000 loops=1)
-> Hash (cost=21.60..21.60 rows=1160 width=40) (actual time=0.003..0.003 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on environments env_1 (cost=0.00..21.60 rows=1160 width=40) (actual time=0.002..0.002 rows=1 loops=1)
-> Hash (cost=58.01..58.01 rows=737 width=8) (actual time=0.886..0.886 rows=1000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 40kB
-> Hash Left Join (cost=30.67..58.01 rows=737 width=8) (actual time=0.032..0.543 rows=1000 loops=1)
Hash Cond: (certnames_1.name = reports_1.certname)
-> Seq Scan on certnames certnames_1 (cost=0.00..24.37 rows=737 width=8) (actual time=0.010..0.206 rows=1000 loops=1)
Filter: (deactivated IS NULL)
-> Hash (cost=30.42..30.42 rows=20 width=32) (actual time=0.011..0.011 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 0kB
-> Hash Join (cost=27.27..30.42 rows=20 width=32) (actual time=0.011..0.011 rows=0 loops=1)
Hash Cond: ((latest_reports_1.report)::text = (reports_1.hash)::text)
-> HashAggregate (cost=16.38..18.38 rows=200 width=98) (never executed)
-> Seq Scan on latest_reports latest_reports_1 (cost=0.00..15.10 rows=510 width=98) (never executed)
-> Hash (cost=10.40..10.40 rows=40 width=134) (actual time=0.001..0.001 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 0kB
-> Seq Scan on reports reports_1 (cost=0.00..10.40 rows=40 width=134) (actual time=0.001..0.001 rows=0 loops=1)
Total runtime: 8192.334 ms
(100 rows)
Time: 8209.853 ms
=======================================================
With 10000 nodes:
Expanded display is used automatically.
Timing is on.
Null display is "<<NULL>>".
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=932150.73..949054.43 rows=1235 width=185) (actual time=96538.554..108253.471 rows=1020000 loops=1)
Hash Cond: ((certnames.name = "ANY_subquery".certname) AND ((fp.name)::text = ("ANY_subquery".name)::text) AND (env.name = "ANY_subquery".environment))
-> Hash Semi Join (cost=457492.65..474285.22 rows=9877 width=194) (actual time=42024.812..50745.262 rows=1020000 loops=1)
Hash Cond: (fs.certname = certnames.name)
-> Sort (cost=457125.50..459676.68 rows=1020473 width=197) (actual time=42014.969..49842.299 rows=1020000 loops=1)
Sort Key: fp.name, fs.certname
Sort Method: external merge Disk: 89416kB
-> Hash Join (cost=563.16..62286.18 rows=1020473 width=197) (actual time=13.761..1596.247 rows=1020000 loops=1)
Hash Cond: (f.factset_id = fs.id)
-> Hash Join (cost=35.70..29868.93 rows=1020473 width=164) (actual time=0.560..957.203 rows=1020000 loops=1)
Hash Cond: (f.fact_value_id = fv.id)
-> Seq Scan on facts f (cost=0.00..15801.73 rows=1020473 width=16) (actual time=0.011..228.511 rows=1020000 loops=1)
-> Hash (cost=34.42..34.42 rows=102 width=164) (actual time=0.515..0.515 rows=110 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Hash Join (cost=28.59..34.42 rows=102 width=164) (actual time=0.181..0.391 rows=110 loops=1)
Hash Cond: (fv.value_type_id = vt.id)
-> Hash Join (cost=3.29..7.72 rows=102 width=90) (actual time=0.150..0.275 rows=110 loops=1)
Hash Cond: (fv.path_id = fp.id)
-> Seq Scan on fact_values fv (cost=0.00..3.02 rows=102 width=68) (actual time=0.005..0.028 rows=110 loops=1)
-> Hash (cost=2.02..2.02 rows=102 width=38) (actual time=0.122..0.122 rows=102 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 8kB
-> Seq Scan on fact_paths fp (cost=0.00..2.02 rows=102 width=38) (actual time=0.007..0.040 rows=102 loops=1)
-> Hash (cost=16.80..16.80 rows=680 width=90) (actual time=0.013..0.013 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on value_types vt (cost=0.00..16.80 rows=680 width=90) (actual time=0.007..0.009 rows=5 loops=1)
-> Hash (cost=398.03..398.03 rows=10355 width=49) (actual time=13.174..13.174 rows=10000 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 586kB
-> Hash Left Join (cost=36.10..398.03 rows=10355 width=49) (actual time=0.032..8.391 rows=10000 loops=1)
Hash Cond: (fs.environment_id = env.id)
-> Seq Scan on factsets fs (cost=0.00..219.55 rows=10355 width=25) (actual time=0.009..2.323 rows=10000 loops=1)
-> Hash (cost=21.60..21.60 rows=1160 width=40) (actual time=0.009..0.009 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on environments env (cost=0.00..21.60 rows=1160 width=40) (actual time=0.007..0.008 rows=1 loops=1)
-> Hash (cost=243.68..243.68 rows=9877 width=9) (actual time=9.811..9.811 rows=10000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 410kB
-> Hash Left Join (cost=30.67..243.68 rows=9877 width=9) (actual time=0.033..5.960 rows=10000 loops=1)
Hash Cond: (certnames.name = reports.certname)
-> Seq Scan on certnames (cost=0.00..175.77 rows=9877 width=9) (actual time=0.011..2.477 rows=10000 loops=1)
Filter: (deactivated IS NULL)
-> Hash (cost=30.42..30.42 rows=20 width=32) (actual time=0.012..0.012 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 0kB
-> Hash Join (cost=27.27..30.42 rows=20 width=32) (actual time=0.012..0.012 rows=0 loops=1)
Hash Cond: ((latest_reports.report)::text = (reports.hash)::text)
-> HashAggregate (cost=16.38..18.38 rows=200 width=98) (never executed)
-> Seq Scan on latest_reports (cost=0.00..15.10 rows=510 width=98) (never executed)
-> Hash (cost=10.40..10.40 rows=40 width=134) (actual time=0.000..0.000 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 0kB
-> Seq Scan on reports (cost=0.00..10.40 rows=40 width=134) (actual time=0.000..0.000 rows=0 loops=1)
-> Hash (cost=474640.79..474640.79 rows=988 width=54) (actual time=54509.537..54509.537 rows=1020000 loops=1)
Buckets: 1024 Batches: 64 (originally 1) Memory Usage: 1025kB
-> HashAggregate (cost=474630.91..474640.79 rows=988 width=54) (actual time=53576.575..53836.795 rows=1020000 loops=1)
-> Subquery Scan on "ANY_subquery" (cost=474359.30..474556.84 rows=9877 width=54) (actual time=52049.575..52711.235 rows=1020000 loops=1)
-> HashAggregate (cost=474359.30..474458.07 rows=9877 width=54) (actual time=52049.567..52503.083 rows=1020000 loops=1)
-> Hash Semi Join (cost=457492.65..474285.22 rows=9877 width=54) (actual time=42225.592..50985.294 rows=1020000 loops=1)
Hash Cond: (fs_1.certname = certnames_1.name)
-> Sort (cost=457125.50..459676.68 rows=1020473 width=197) (actual time=42217.014..50105.542 rows=1020000 loops=1)
Sort Key: fp_1.name, fs_1.certname
Sort Method: external merge Disk: 89416kB
-> Hash Join (cost=563.16..62286.18 rows=1020473 width=197) (actual time=10.670..1538.808 rows=1020000 loops=1)
Hash Cond: (f_1.factset_id = fs_1.id)
-> Hash Join (cost=35.70..29868.93 rows=1020473 width=164) (actual time=0.346..904.564 rows=1020000 loops=1)
Hash Cond: (f_1.fact_value_id = fv_1.id)
-> Seq Scan on facts f_1 (cost=0.00..15801.73 rows=1020473 width=16) (actual time=0.007..175.417 rows=1020000 loops=1)
-> Hash (cost=34.42..34.42 rows=102 width=164) (actual time=0.319..0.319 rows=110 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Hash Join (cost=28.59..34.42 rows=102 width=164) (actual time=0.116..0.253 rows=110 loops=1)
Hash Cond: (fv_1.value_type_id = vt_1.id)
-> Hash Join (cost=3.29..7.72 rows=102 width=90) (actual time=0.098..0.175 rows=110 loops=1)
Hash Cond: (fv_1.path_id = fp_1.id)
-> Seq Scan on fact_values fv_1 (cost=0.00..3.02 rows=102 width=68) (actual time=0.004..0.021 rows=110 loops=1)
-> Hash (cost=2.02..2.02 rows=102 width=38) (actual time=0.079..0.079 rows=102 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 8kB
-> Seq Scan on fact_paths fp_1 (cost=0.00..2.02 rows=102 width=38) (actual time=0.005..0.025 rows=102 loops=1)
-> Hash (cost=16.80..16.80 rows=680 width=90) (actual time=0.006..0.006 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on value_types vt_1 (cost=0.00..16.80 rows=680 width=90) (actual time=0.002..0.003 rows=5 loops=1)
-> Hash (cost=398.03..398.03 rows=10355 width=49) (actual time=10.302..10.302 rows=10000 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 586kB
-> Hash Left Join (cost=36.10..398.03 rows=10355 width=49) (actual time=0.020..6.442 rows=10000 loops=1)
Hash Cond: (fs_1.environment_id = env_1.id)
-> Seq Scan on factsets fs_1 (cost=0.00..219.55 rows=10355 width=25) (actual time=0.005..1.406 rows=10000 loops=1)
-> Hash (cost=21.60..21.60 rows=1160 width=40) (actual time=0.004..0.004 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on environments env_1 (cost=0.00..21.60 rows=1160 width=40) (actual time=0.002..0.002 rows=1 loops=1)
-> Hash (cost=243.68..243.68 rows=9877 width=9) (actual time=8.558..8.558 rows=10000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 410kB
-> Hash Left Join (cost=30.67..243.68 rows=9877 width=9) (actual time=0.036..5.140 rows=10000 loops=1)
Hash Cond: (certnames_1.name = reports_1.certname)
-> Seq Scan on certnames certnames_1 (cost=0.00..175.77 rows=9877 width=9) (actual time=0.012..1.904 rows=10000 loops=1)
Filter: (deactivated IS NULL)
-> Hash (cost=30.42..30.42 rows=20 width=32) (actual time=0.011..0.011 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 0kB
-> Hash Join (cost=27.27..30.42 rows=20 width=32) (actual time=0.011..0.011 rows=0 loops=1)
Hash Cond: ((latest_reports_1.report)::text = (reports_1.hash)::text)
-> HashAggregate (cost=16.38..18.38 rows=200 width=98) (never executed)
-> Seq Scan on latest_reports latest_reports_1 (cost=0.00..15.10 rows=510 width=98) (never executed)
-> Hash (cost=10.40..10.40 rows=40 width=134) (actual time=0.001..0.001 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 0kB
-> Seq Scan on reports reports_1 (cost=0.00..10.40 rows=40 width=134) (actual time=0.001..0.001 rows=0 loops=1)
Total runtime: 108352.297 ms
(100 rows)
Time: 108371.168 ms
Raw
facts-stable
Test with 1 nodes worth of facts:
Expanded display is used automatically.
Timing is on.
Null display is "<<NULL>>".
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=123.31..159.74 rows=320 width=128) (actual time=0.125..0.286 rows=102 loops=1)
Hash Cond: (cf.certname = certnames.name)
-> Hash Left Join (cost=70.85..104.85 rows=640 width=160) (actual time=0.072..0.188 rows=102 loops=1)
Hash Cond: (cfm.environment_id = env.id)
-> Hash Join (cost=34.75..59.95 rows=640 width=132) (actual time=0.032..0.110 rows=102 loops=1)
Hash Cond: (cf.certname = cfm.certname)
-> Seq Scan on certname_facts cf (cost=0.00..16.40 rows=640 width=96) (actual time=0.009..0.026 rows=102 loops=1)
-> Hash (cost=21.00..21.00 rows=1100 width=36) (actual time=0.006..0.006 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on certname_facts_metadata cfm (cost=0.00..21.00 rows=1100 width=36) (actual time=0.005..0.006 rows=1 loops=1)
-> Hash (cost=21.60..21.60 rows=1160 width=40) (actual time=0.006..0.006 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on environments env (cost=0.00..21.60 rows=1160 width=40) (actual time=0.005..0.005 rows=1 loops=1)
-> Hash (cost=52.38..52.38 rows=6 width=32) (actual time=0.043..0.043 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> HashAggregate (cost=52.32..52.38 rows=6 width=32) (actual time=0.042..0.042 rows=1 loops=1)
-> Hash Left Join (cost=30.67..52.31 rows=6 width=32) (actual time=0.035..0.036 rows=1 loops=1)
Hash Cond: (certnames.name = reports.certname)
-> Seq Scan on certnames (cost=0.00..21.60 rows=6 width=32) (actual time=0.014..0.014 rows=1 loops=1)
Filter: (deactivated IS NULL)
-> Hash (cost=30.42..30.42 rows=20 width=32) (actual time=0.010..0.010 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 0kB
-> Hash Join (cost=27.27..30.42 rows=20 width=32) (actual time=0.010..0.010 rows=0 loops=1)
Hash Cond: ((latest_reports.report)::text = (reports.hash)::text)
-> HashAggregate (cost=16.38..18.38 rows=200 width=98) (never executed)
-> Seq Scan on latest_reports (cost=0.00..15.10 rows=510 width=98) (never executed)
-> Hash (cost=10.40..10.40 rows=40 width=134) (actual time=0.000..0.000 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 0kB
-> Seq Scan on reports (cost=0.00..10.40 rows=40 width=134) (actual time=0.000..0.000 rows=0 loops=1)
Total runtime: 0.464 ms
(30 rows)
Time: 6.685 ms
=======================================================
Test with 100 nodes worth of facts:
Expanded display is used automatically.
Timing is on.
Null display is "<<NULL>>".
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Hash Semi Join (cost=75.22..565.73 rows=10200 width=79) (actual time=0.274..12.117 rows=10200 loops=1)
Hash Cond: (cf.certname = certnames.name)
-> Hash Join (cost=40.73..390.98 rows=10200 width=86) (actual time=0.155..7.622 rows=10200 loops=1)
Hash Cond: (cf.certname = cfm.certname)
-> Seq Scan on certname_facts cf (cost=0.00..210.00 rows=10200 width=47) (actual time=0.005..1.576 rows=10200 loops=1)
-> Hash (cost=39.48..39.48 rows=100 width=39) (actual time=0.138..0.138 rows=100 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 5kB
-> Hash Left Join (cost=36.10..39.48 rows=100 width=39) (actual time=0.045..0.108 rows=100 loops=1)
Hash Cond: (cfm.environment_id = env.id)
-> Seq Scan on certname_facts_metadata cfm (cost=0.00..2.00 rows=100 width=11) (actual time=0.002..0.016 rows=100 loops=1)
-> Hash (cost=21.60..21.60 rows=1160 width=40) (actual time=0.006..0.006 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on environments env (cost=0.00..21.60 rows=1160 width=40) (actual time=0.005..0.005 rows=1 loops=1)
-> Hash (cost=33.25..33.25 rows=100 width=7) (actual time=0.106..0.106 rows=100 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 4kB
-> Hash Left Join (cost=30.67..33.25 rows=100 width=7) (actual time=0.028..0.072 rows=100 loops=1)
Hash Cond: (certnames.name = reports.certname)
-> Seq Scan on certnames (cost=0.00..2.00 rows=100 width=7) (actual time=0.007..0.027 rows=100 loops=1)
Filter: (deactivated IS NULL)
-> Hash (cost=30.42..30.42 rows=20 width=32) (actual time=0.010..0.010 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 0kB
-> Hash Join (cost=27.27..30.42 rows=20 width=32) (actual time=0.010..0.010 rows=0 loops=1)
Hash Cond: ((latest_reports.report)::text = (reports.hash)::text)
-> HashAggregate (cost=16.38..18.38 rows=200 width=98) (never executed)
-> Seq Scan on latest_reports (cost=0.00..15.10 rows=510 width=98) (never executed)
-> Hash (cost=10.40..10.40 rows=40 width=134) (actual time=0.000..0.000 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 0kB
-> Seq Scan on reports (cost=0.00..10.40 rows=40 width=134) (actual time=0.000..0.000 rows=0 loops=1)
Total runtime: 12.766 ms
(29 rows)
Time: 20.669 ms
=======================================================
1000 nodes:
Expanded display is used automatically.
Timing is on.
Null display is "<<NULL>>".
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Hash Semi Join (cost=175.16..5055.11 rows=102772 width=80) (actual time=2.263..123.095 rows=102000 loops=1)
Hash Cond: (cf.certname = certnames.name)
-> Hash Join (cost=112.65..3579.49 rows=102772 width=88) (actual time=1.275..77.211 rows=102000 loops=1)
Hash Cond: (cf.certname = cfm.certname)
-> Seq Scan on certname_facts cf (cost=0.00..2053.72 rows=102772 width=48) (actual time=0.005..19.670 rows=102000 loops=1)
-> Hash (cost=95.57..95.57 rows=1367 width=40) (actual time=1.253..1.253 rows=1000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 44kB
-> Hash Left Join (cost=36.10..95.57 rows=1367 width=40) (actual time=0.054..0.807 rows=1000 loops=1)
Hash Cond: (cfm.environment_id = env.id)
-> Seq Scan on certname_facts_metadata cfm (cost=0.00..40.67 rows=1367 width=12) (actual time=0.007..0.293 rows=1000 loops=1)
-> Hash (cost=21.60..21.60 rows=1160 width=40) (actual time=0.008..0.008 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on environments env (cost=0.00..21.60 rows=1160 width=40) (actual time=0.007..0.007 rows=1 loops=1)
-> Hash (cost=51.73..51.73 rows=862 width=8) (actual time=0.974..0.974 rows=1000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 40kB
-> Hash Left Join (cost=30.67..51.73 rows=862 width=8) (actual time=0.038..0.628 rows=1000 loops=1)
Hash Cond: (certnames.name = reports.certname)
-> Seq Scan on certnames (cost=0.00..17.62 rows=862 width=8) (actual time=0.010..0.263 rows=1000 loops=1)
Filter: (deactivated IS NULL)
-> Hash (cost=30.42..30.42 rows=20 width=32) (actual time=0.012..0.012 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 0kB
-> Hash Join (cost=27.27..30.42 rows=20 width=32) (actual time=0.012..0.012 rows=0 loops=1)
Hash Cond: ((latest_reports.report)::text = (reports.hash)::text)
-> HashAggregate (cost=16.38..18.38 rows=200 width=98) (never executed)
-> Seq Scan on latest_reports (cost=0.00..15.10 rows=510 width=98) (never executed)
-> Hash (cost=10.40..10.40 rows=40 width=134) (actual time=0.000..0.000 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 0kB
-> Seq Scan on reports (cost=0.00..10.40 rows=40 width=134) (actual time=0.000..0.000 rows=0 loops=1)
Total runtime: 128.281 ms
(29 rows)
Time: 138.416 ms
=======================================================
10000 nodes:
Expanded display is used automatically.
Timing is on.
Null display is "<<NULL>>".
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Semi Join (cost=906.98..72488.42 rows=1021074 width=80) (actual time=22.819..1407.243 rows=1020000 loops=1)
Hash Cond: (cf.certname = certnames.name)
-> Hash Join (cost=514.23..46568.82 rows=1021074 width=89) (actual time=13.585..923.521 rows=1020000 loops=1)
Hash Cond: (cf.certname = cfm.certname)
-> Seq Scan on certname_facts cf (cost=0.00..20527.74 rows=1021074 width=48) (actual time=0.477..299.478 rows=1020000 loops=1)
-> Hash (cost=386.25..386.25 rows=10238 width=41) (actual time=13.077..13.077 rows=10000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 449kB
-> Hash Left Join (cost=36.10..386.25 rows=10238 width=41) (actual time=0.632..9.107 rows=10000 loops=1)
Hash Cond: (cfm.environment_id = env.id)
-> Seq Scan on certname_facts_metadata cfm (cost=0.00..209.38 rows=10238 width=13) (actual time=0.518..4.274 rows=10000 loops=1)
-> Hash (cost=21.60..21.60 rows=1160 width=40) (actual time=0.013..0.013 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on environments env (cost=0.00..21.60 rows=1160 width=40) (actual time=0.008..0.009 rows=1 loops=1)
-> Hash (cost=269.00..269.00 rows=9900 width=9) (actual time=9.218..9.218 rows=10000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 410kB
-> Hash Left Join (cost=30.67..269.00 rows=9900 width=9) (actual time=0.040..5.676 rows=10000 loops=1)
Hash Cond: (certnames.name = reports.certname)
-> Seq Scan on certnames (cost=0.00..201.00 rows=9900 width=9) (actual time=0.014..2.505 rows=10000 loops=1)
Filter: (deactivated IS NULL)
-> Hash (cost=30.42..30.42 rows=20 width=32) (actual time=0.013..0.013 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 0kB
-> Hash Join (cost=27.27..30.42 rows=20 width=32) (actual time=0.013..0.013 rows=0 loops=1)
Hash Cond: ((latest_reports.report)::text = (reports.hash)::text)
-> HashAggregate (cost=16.38..18.38 rows=200 width=98) (never executed)
-> Seq Scan on latest_reports (cost=0.00..15.10 rows=510 width=98) (never executed)
-> Hash (cost=10.40..10.40 rows=40 width=134) (actual time=0.000..0.000 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 0kB
-> Seq Scan on reports (cost=0.00..10.40 rows=40 width=134) (actual time=0.000..0.000 rows=0 loops=1)
Total runtime: 1456.470 ms
(29 rows)
Time: 1501.713 ms
Raw
facts-stable-with-order
Expanded display is used automatically.
Timing is on.
Null display is "<<NULL>>".
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=310511.56..313064.24 rows=1021074 width=80) (actual time=40061.337..47512.002 rows=1020000 loops=1)
Sort Key: cf.name, cf.certname
Sort Method: external merge Disk: 64104kB
-> Hash Semi Join (cost=906.98..72488.42 rows=1021074 width=80) (actual time=20.308..1568.473 rows=1020000 loops=1)
Hash Cond: (cf.certname = certnames.name)
-> Hash Join (cost=514.23..46568.82 rows=1021074 width=89) (actual time=11.033..1021.079 rows=1020000 loops=1)
Hash Cond: (cf.certname = cfm.certname)
-> Seq Scan on certname_facts cf (cost=0.00..20527.74 rows=1021074 width=48) (actual time=0.015..238.428 rows=1020000 loops=1)
-> Hash (cost=386.25..386.25 rows=10238 width=41) (actual time=10.998..10.998 rows=10000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 449kB
-> Hash Left Join (cost=36.10..386.25 rows=10238 width=41) (actual time=0.061..6.927 rows=10000 loops=1)
Hash Cond: (cfm.environment_id = env.id)
-> Seq Scan on certname_facts_metadata cfm (cost=0.00..209.38 rows=10238 width=13) (actual time=0.006..1.938 rows=10000 loops=1)
-> Hash (cost=21.60..21.60 rows=1160 width=40) (actual time=0.007..0.007 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on environments env (cost=0.00..21.60 rows=1160 width=40) (actual time=0.006..0.006 rows=1 loops=1)
-> Hash (cost=269.00..269.00 rows=9900 width=9) (actual time=9.261..9.261 rows=10000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 410kB
-> Hash Left Join (cost=30.67..269.00 rows=9900 width=9) (actual time=0.035..5.716 rows=10000 loops=1)
Hash Cond: (certnames.name = reports.certname)
-> Seq Scan on certnames (cost=0.00..201.00 rows=9900 width=9) (actual time=0.013..2.350 rows=10000 loops=1)
Filter: (deactivated IS NULL)
-> Hash (cost=30.42..30.42 rows=20 width=32) (actual time=0.010..0.010 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 0kB
-> Hash Join (cost=27.27..30.42 rows=20 width=32) (actual time=0.010..0.010 rows=0 loops=1)
Hash Cond: ((latest_reports.report)::text = (reports.hash)::text)
-> HashAggregate (cost=16.38..18.38 rows=200 width=98) (never executed)
-> Seq Scan on latest_reports (cost=0.00..15.10 rows=510 width=98) (never executed)
-> Hash (cost=10.40..10.40 rows=40 width=134) (actual time=0.000..0.000 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 0kB
-> Seq Scan on reports (cost=0.00..10.40 rows=40 width=134) (actual time=0.000..0.000 rows=0 loops=1)
Total runtime: 47706.395 ms
(32 rows)
Time: 47715.571 ms
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment