Skip to content

Instantly share code, notes, and snippets.

@rgarner

rgarner/count_distinct_ids.sql

Last active September 23, 2018 18:16
Show Gist options
  • Save rgarner/4276e198f084e1574505b9bd9e56f0be to your computer and use it in GitHub Desktop.
Save rgarner/4276e198f084e1574505b9bd9e56f0be to your computer and use it in GitHub Desktop.
Download ZIP
Raw
count_distinct_ids.sql
psql DataSubmissionServiceApi_development on [local] as russ
=> EXPLAIN ANALYZE
SELECT submissions.*,
COUNT(DISTINCT (orders.id)) AS _order_entry_count,
COUNT(DISTINCT (invoices.id)) AS _invoice_entry_count,
MIN(blobs.filename)::text AS _first_filename
FROM "submissions"
LEFT JOIN submission_entries orders
ON (
orders.submission_id = submissions.id
AND orders.entry_type = 'order'
)
LEFT JOIN submission_entries invoices
ON (
invoices.submission_id = submissions.id
AND invoices.entry_type = 'invoice'
)
LEFT JOIN submission_files
ON submission_files.submission_id = submissions.id
LEFT JOIN active_storage_attachments att
ON (
att.record_type = 'SubmissionFile'
AND att.record_id = submission_files.id
)
LEFT JOIN active_storage_blobs blobs
ON blobs.id = att.blob_id
WHERE "submissions"."aasm_state" IN ('completed', 'validation_failed')
GROUP BY submissions.id
ORDER BY submissions.id
;
QUERY PLAN
──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
GroupAggregate (cost=3356.36..4755.76 rows=144 width=178) (actual time=9.067..147.357 rows=144 loops=1)
Group Key: submissions.id
-> Merge Left Join (cost=3356.36..4196.96 rows=55736 width=194) (actual time=9.054..53.896 rows=188159 loops=1)
Merge Cond: (submissions.id = invoices.submission_id)
-> Sort (cost=1228.32..1230.61 rows=913 width=178) (actual time=2.133..2.271 rows=975 loops=1)
Sort Key: submissions.id
Sort Method: quicksort Memory: 277kB
-> Hash Left Join (cost=16.04..1183.43 rows=913 width=178) (actual time=0.266..1.756 rows=975 loops=1)
Hash Cond: (submission_files.id = att.record_id)
-> Hash Left Join (cost=11.67..1175.56 rows=913 width=162) (actual time=0.194..1.438 rows=975 loops=1)
Hash Cond: (submissions.id = submission_files.submission_id)
-> Hash Right Join (cost=6.92..1159.81 rows=913 width=146) (actual time=0.144..1.097 rows=975 loops=1)
Hash Cond: (orders.submission_id = submissions.id)
-> Index Scan using index_submission_entries_on_entry_type on submission_entries orders (cost=0.29..1150.66 rows=932 width=32) (actual time=0.023..0.592 rows=932 loops=1)
Index Cond: ((entry_type)::text = 'order'::text)
-> Hash (cost=4.84..4.84 rows=144 width=130) (actual time=0.114..0.114 rows=144 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 28kB
-> Seq Scan on submissions (cost=0.00..4.84 rows=144 width=130) (actual time=0.016..0.084 rows=144 loops=1)
Filter: ((aasm_state)::text = ANY ('{completed,validation_failed}'::text[]))
Rows Removed by Filter: 3
-> Hash (cost=3.22..3.22 rows=122 width=32) (actual time=0.046..0.046 rows=122 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 16kB
-> Seq Scan on submission_files (cost=0.00..3.22 rows=122 width=32) (actual time=0.007..0.024 rows=122 loops=1)
-> Hash (cost=4.36..4.36 rows=1 width=48) (actual time=0.069..0.069 rows=51 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Hash Right Join (cost=1.65..4.36 rows=1 width=48) (actual time=0.032..0.058 rows=51 loops=1)
Hash Cond: (blobs.id = att.blob_id)
-> Seq Scan on active_storage_blobs blobs (cost=0.00..2.51 rows=51 width=48) (actual time=0.004..0.011 rows=51 loops=1)
-> Hash (cost=1.64..1.64 rows=1 width=32) (actual time=0.025..0.025 rows=51 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
-> Seq Scan on active_storage_attachments att (cost=0.00..1.64 rows=1 width=32) (actual time=0.005..0.015 rows=51 loops=1)
Filter: ((record_type)::text = 'SubmissionFile'::text)
-> Sort (cost=2128.04..2150.47 rows=8974 width=32) (actual time=6.918..18.168 rows=188417 loops=1)
Sort Key: invoices.submission_id
Sort Method: quicksort Memory: 1086kB
-> Seq Scan on submission_entries invoices (cost=0.00..1538.83 rows=8974 width=32) (actual time=0.005..4.529 rows=8974 loops=1)
Filter: ((entry_type)::text = 'invoice'::text)
Rows Removed by Filter: 932
Planning time: 1.139 ms
Execution time: 147.486 ms
(40 rows)
Time: 149.442 ms
Raw
count_distinct_whole_record.sql
=> EXPLAIN ANALYZE
psql DataSubmissionServiceApi_development on [local] as russ
=> EXPLAIN ANALYZE
SELECT submissions.*,
COUNT(DISTINCT orders) AS _order_entry_count,
COUNT(DISTINCT invoices) AS _invoice_entry_count,
MIN(blobs.filename)::text AS _first_filename
FROM "submissions"
LEFT JOIN submission_entries orders
ON (
orders.submission_id = submissions.id
AND orders.entry_type = 'order'
)
LEFT JOIN submission_entries invoices
ON (
invoices.submission_id = submissions.id
AND invoices.entry_type = 'invoice'
)
LEFT JOIN submission_files
ON submission_files.submission_id = submissions.id
LEFT JOIN active_storage_attachments att
ON (
att.record_type = 'SubmissionFile'
AND att.record_id = submission_files.id
)
LEFT JOIN active_storage_blobs blobs
ON blobs.id = att.blob_id
WHERE "submissions"."aasm_state" IN ('completed', 'validation_failed')
GROUP BY submissions.id
ORDER BY submissions.id
;
QUERY PLAN
────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
GroupAggregate (cost=3356.36..4755.76 rows=144 width=178) (actual time=12.715..4929.512 rows=144 loops=1)
Group Key: submissions.id
-> Merge Left Join (cost=3356.36..4196.96 rows=55736 width=2950) (actual time=12.698..65.353 rows=188159 loops=1)
Merge Cond: (submissions.id = invoices.submission_id)
-> Sort (cost=1228.32..1230.61 rows=913 width=1556) (actual time=2.716..2.933 rows=975 loops=1)
Sort Key: submissions.id
Sort Method: quicksort Memory: 1450kB
-> Hash Left Join (cost=16.04..1183.43 rows=913 width=1556) (actual time=0.239..2.205 rows=975 loops=1)
Hash Cond: (submission_files.id = att.record_id)
-> Hash Left Join (cost=11.67..1175.56 rows=913 width=1540) (actual time=0.170..1.888 rows=975 loops=1)
Hash Cond: (submissions.id = submission_files.submission_id)
-> Hash Right Join (cost=6.92..1159.81 rows=913 width=1524) (actual time=0.122..1.526 rows=975 loops=1)
Hash Cond: (orders.submission_id = submissions.id)
-> Index Scan using index_submission_entries_on_entry_type on submission_entries orders (cost=0.29..1150.66 rows=932 width=1410) (actual time=0.026..1.071 rows=932 loops=1)
Index Cond: ((entry_type)::text = 'order'::text)
-> Hash (cost=4.84..4.84 rows=144 width=130) (actual time=0.090..0.091 rows=144 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 28kB
-> Seq Scan on submissions (cost=0.00..4.84 rows=144 width=130) (actual time=0.032..0.069 rows=144 loops=1)
Filter: ((aasm_state)::text = ANY ('{completed,validation_failed}'::text[]))
Rows Removed by Filter: 3
-> Hash (cost=3.22..3.22 rows=122 width=32) (actual time=0.044..0.044 rows=122 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 16kB
-> Seq Scan on submission_files (cost=0.00..3.22 rows=122 width=32) (actual time=0.006..0.023 rows=122 loops=1)
-> Hash (cost=4.36..4.36 rows=1 width=48) (actual time=0.066..0.066 rows=51 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Hash Right Join (cost=1.65..4.36 rows=1 width=48) (actual time=0.031..0.055 rows=51 loops=1)
Hash Cond: (blobs.id = att.blob_id)
-> Seq Scan on active_storage_blobs blobs (cost=0.00..2.51 rows=51 width=48) (actual time=0.004..0.010 rows=51 loops=1)
-> Hash (cost=1.64..1.64 rows=1 width=32) (actual time=0.024..0.024 rows=51 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
-> Seq Scan on active_storage_attachments att (cost=0.00..1.64 rows=1 width=32) (actual time=0.005..0.015 rows=51 loops=1)
Filter: ((record_type)::text = 'SubmissionFile'::text)
-> Sort (cost=2128.04..2150.47 rows=8974 width=1410) (actual time=9.979..24.238 rows=188417 loops=1)
Sort Key: invoices.submission_id
Sort Method: quicksort Memory: 13127kB
-> Seq Scan on submission_entries invoices (cost=0.00..1538.83 rows=8974 width=1410) (actual time=0.009..6.112 rows=8974 loops=1)
Filter: ((entry_type)::text = 'invoice'::text)
Rows Removed by Filter: 932
Planning time: 1.123 ms
Execution time: 4932.947 ms
(40 rows)
Time: 4934.908 ms (00:04.935)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment