explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YOR

Settings
# exclusive inclusive rows x rows loops node
1. 11.473 6,818.672 ↑ 4.1 6,573 1

Finalize GroupAggregate (cost=1,281,026.07..1,282,779.51 rows=26,976 width=42) (actual time=6,802.889..6,818.672 rows=6,573 loops=1)

  • Group Key: patients_indications.patient_id, patients_indications.site_id, patients_indications.practice, patients_indications.active, (CASE WHEN ((patients.birth_year <= 2001) AND (patients.birth_year >= 1954)) THEN true ELSE false END)
2. 13.610 6,807.199 ↑ 8.2 6,573 1

Sort (cost=1,281,026.07..1,281,160.95 rows=53,952 width=42) (actual time=6,802.879..6,807.199 rows=6,573 loops=1)

  • Sort Key: patients_indications.patient_id, patients_indications.site_id, patients_indications.practice, patients_indications.active, (CASE WHEN ((patients.birth_year <= 2001) AND (patients.birth_year >= 1954)) THEN true ELSE false END)
  • Sort Method: quicksort Memory: 970kB
3. 16.835 6,793.589 ↑ 8.2 6,573 1

Gather (cost=1,270,985.76..1,276,785.60 rows=53,952 width=42) (actual time=6,782.226..6,793.589 rows=6,573 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 1,278.211 6,776.754 ↑ 12.3 2,191 3

Partial HashAggregate (cost=1,269,985.76..1,270,390.40 rows=26,976 width=42) (actual time=6,773.904..6,776.754 rows=2,191 loops=3)

  • Group Key: patients_indications.patient_id, patients_indications.site_id, patients_indications.practice, patients_indications.active, CASE WHEN ((patients.birth_year <= 2001) AND (patients.birth_year >= 1954)) THEN true ELSE false END
5. 1,438.842 5,498.543 ↓ 2.0 805,155 3

Hash Left Join (cost=21,194.50..1,256,823.43 rows=404,995 width=46) (actual time=282.021..5,498.543 rows=805,155 loops=3)

  • Hash Cond: (codes.omop_concept_id = protocol_mapper.concept_id)
6. 2,678.612 3,817.693 ↓ 2.0 804,067 3

Nested Loop (cost=2,625.69..1,234,710.89 rows=404,995 width=43) (actual time=39.993..3,817.693 rows=804,067 loops=3)

  • Join Filter: (patients_indications.patient_id = codes.patient_id)
7. 389.573 1,138.454 ↑ 1.3 2,191 3

Merge Join (cost=2,625.12..55,512.03 rows=2,810 width=43) (actual time=39.888..1,138.454 rows=2,191 loops=3)

  • Merge Cond: (patients.id = patients_indications.patient_id)
8. 703.714 703.714 ↑ 1.2 450,579 3

Parallel Index Scan using pk_patients on patients (cost=0.43..51,420.11 rows=562,731 width=6) (actual time=0.045..703.714 rows=450,579 loops=3)

9. 12.846 45.167 ↑ 1.0 6,570 3

Sort (cost=2,624.69..2,641.55 rows=6,744 width=37) (actual time=39.444..45.167 rows=6,570 loops=3)

  • Sort Key: patients_indications.patient_id
  • Sort Method: quicksort Memory: 901kB
10. 32.321 32.321 ↑ 1.0 6,573 3

Seq Scan on patients_indications (cost=0.00..2,195.79 rows=6,744 width=37) (actual time=0.024..32.321 rows=6,573 loops=3)

  • Filter: ulcerative_colitis
  • Rows Removed by Filter: 106206
11. 0.627 0.627 ↓ 2.5 367 6,573

Index Scan using codes_patient_id_index on codes (cost=0.57..417.84 rows=144 width=8) (actual time=0.029..0.627 rows=367 loops=6,573)

  • Index Cond: (patient_id = patients.id)
12. 21.386 242.008 ↓ 21,082.0 21,082 3

Hash (cost=18,568.80..18,568.80 rows=1 width=12) (actual time=242.007..242.008 rows=21,082 loops=3)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1080kB
13. 220.622 220.622 ↓ 21,082.0 21,082 3

Seq Scan on protocol_mapper (cost=0.00..18,568.80 rows=1 width=12) (actual time=168.438..220.622 rows=21,082 loops=3)

  • Filter: ((protocol)::text = 'nibr_uc_clys'::text)
  • Rows Removed by Filter: 461674
Planning time : 7.218 ms
Execution time : 6,822.888 ms