explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wFy7

Settings
# exclusive inclusive rows x rows loops node
1. 20.843 126,201.842 ↑ 1.0 14,000 1

Group (cost=248,481,840.12..248,483,520.12 rows=14,000 width=274) (actual time=126,172.382..126,201.842 rows=14,000 loops=1)

  • Group Key: p._id, (count(pu_2.preferred_option)), (CASE WHEN (p.type = 'POLL'::enum_polls_type) THEN (((count(pu_2.preferred_option)))::double precision * p.consensus_rule) ELSE NULL::double precision END), (mode() WITHIN GROUP (ORDER BY puz.preferred_option)), (CASE WHEN (p.type = 'POLL'::enum_polls_type) THEN count(pu.preferred_option) ELSE NULL::bigint END), (CASE WHEN ((p.min_consensus <= (CASE WHEN (p.type = 'POLL'::enum_polls_type) THEN count(pu.preferred_option) ELSE NULL::bigint END)) AND (CASE WHEN (p.type = 'POLL'::enum_polls_type) THEN (((count(pu_2.preferred_option)))::double precision * p.consensus_rule) ELSE NULL::double precision END <= ((CASE WHEN (p.type = 'POLL'::enum_polls_type) THEN count(pu.preferred_option) ELSE NULL::bigint END))::double precision)) THEN true WHEN ((p.min_consensus > (CASE WHEN (p.type = 'POLL'::enum_polls_type) THEN count(pu.preferred_option) ELSE NULL::bigint END)) OR (CASE WHEN (p.type = 'POLL'::enum_polls_type) THEN (((count(pu_2.preferred_option)))::double precision * p.consensus_rule) ELSE NULL::double precision END > ((CASE WHEN (p.type = 'POLL'::enum_polls_type) THEN count(pu.preferred_option) ELSE NULL::bigint END))::double precision)) THEN false ELSE NULL::boolean END), (CASE WHEN (p.type = 'QUIZ'::enum_polls_type) THEN count(pu_1.preferred_option) ELSE NULL::bigint END)
2. 119.028 126,180.999 ↑ 1.0 14,000 1

Sort (cost=248,481,840.12..248,481,875.12 rows=14,000 width=234) (actual time=126,172.374..126,180.999 rows=14,000 loops=1)

  • Sort Key: p._id, (count(pu_2.preferred_option)), (CASE WHEN (p.type = 'POLL'::enum_polls_type) THEN (((count(pu_2.preferred_option)))::double precision * p.consensus_rule) ELSE NULL::double precision END), (mode() WITHIN GROUP (ORDER BY puz.preferred_option)), (CASE WHEN (p.type = 'POLL'::enum_polls_type) THEN count(pu.preferred_option) ELSE NULL::bigint END), (CASE WHEN ((p.min_consensus <= (CASE WHEN (p.type = 'POLL'::enum_polls_type) THEN count(pu.preferred_option) ELSE NULL::bigint END)) AND (CASE WHEN (p.type = 'POLL'::enum_polls_type) THEN (((count(pu_2.preferred_option)))::double precision * p.consensus_rule) ELSE NULL::double precision END <= ((CASE WHEN (p.type = 'POLL'::enum_polls_type) THEN count(pu.preferred_option) ELSE NULL::bigint END))::double precision)) THEN true WHEN ((p.min_consensus > (CASE WHEN (p.type = 'POLL'::enum_polls_type) THEN count(pu.preferred_option) ELSE NULL::bigint END)) OR (CASE WHEN (p.type = 'POLL'::enum_polls_type) THEN (((count(pu_2.preferred_option)))::double precision * p.consensus_rule) ELSE NULL::double precision END > ((CASE WHEN (p.type = 'POLL'::enum_polls_type) THEN count(pu.preferred_option) ELSE NULL::bigint END))::double precision)) THEN false ELSE NULL::boolean END), (CASE WHEN (p.type = 'QUIZ'::enum_polls_type) THEN count(pu_1.preferred_option) ELSE NULL::bigint END)
  • Sort Method: external merge Disk: 3480kB
3. 75.544 126,061.971 ↑ 1.0 14,000 1

Nested Loop (cost=17,748.43..248,480,876.00 rows=14,000 width=234) (actual time=11.852..126,061.971 rows=14,000 loops=1)

4. 58.613 98,406.427 ↑ 1.0 14,000 1

Nested Loop (cost=13,312.82..186,381,391.00 rows=14,000 width=217) (actual time=9.916..98,406.427 rows=14,000 loops=1)

5. 72.812 92,187.814 ↑ 1.0 14,000 1

Nested Loop (cost=8,874.22..124,240,501.00 rows=14,000 width=209) (actual time=9.632..92,187.814 rows=14,000 loops=1)

6. 87.517 74,559.002 ↑ 1.0 14,000 1

Nested Loop (cost=4,435.61..62,099,611.00 rows=14,000 width=201) (actual time=8.410..74,559.002 rows=14,000 loops=1)

7. 33.485 33.485 ↑ 1.0 14,000 1

Seq Scan on polls p (cost=0.00..616.00 rows=14,000 width=199) (actual time=2.158..33.485 rows=14,000 loops=1)

8. 14,280.000 74,438.000 ↑ 1.0 1 14,000

Aggregate (cost=4,435.61..4,435.62 rows=1 width=2) (actual time=5.316..5.317 rows=1 loops=14,000)

9. 60,158.000 60,158.000 ↑ 1.2 2,000 14,000

Index Scan using polls_users_pkey on polls_users puz (cost=0.56..4,429.63 rows=2,393 width=2) (actual time=1.189..4.297 rows=2,000 loops=14,000)

  • Index Cond: ((poll_id)::text = (p._id)::text)
10. 6,328.000 17,556.000 ↑ 1.0 1 14,000

Aggregate (cost=4,438.60..4,438.62 rows=1 width=8) (actual time=1.254..1.254 rows=1 loops=14,000)

11. 11,228.000 11,228.000 ↑ 1.2 1,018 14,000

Index Scan using polls_users_pkey on polls_users pu (cost=0.56..4,435.61 rows=1,197 width=2) (actual time=0.011..0.802 rows=1,018 loops=14,000)

  • Index Cond: ((poll_id)::text = (p._id)::text)
  • Filter: (preferred_option = (mode() WITHIN GROUP (ORDER BY puz.preferred_option)))
  • Rows Removed by Filter: 982
12. 924.000 6,160.000 ↑ 1.0 1 14,000

Aggregate (cost=4,438.60..4,438.62 rows=1 width=8) (actual time=0.440..0.440 rows=1 loops=14,000)

13. 5,236.000 5,236.000 ↑ 8.4 143 14,000

Index Scan using polls_users_pkey on polls_users pu_1 (cost=0.56..4,435.61 rows=1,197 width=2) (actual time=0.265..0.374 rows=143 loops=14,000)

  • Index Cond: ((poll_id)::text = (p._id)::text)
  • Filter: (preferred_option = p.correct_option)
  • Rows Removed by Filter: 1857
14. 12,698.000 27,580.000 ↑ 1.0 1 14,000

Aggregate (cost=4,435.61..4,435.62 rows=1 width=8) (actual time=1.969..1.970 rows=1 loops=14,000)

15. 14,882.000 14,882.000 ↑ 1.2 2,000 14,000

Index Scan using polls_users_pkey on polls_users pu_2 (cost=0.56..4,429.63 rows=2,393 width=2) (actual time=0.006..1.063 rows=2,000 loops=14,000)

  • Index Cond: ((poll_id)::text = (p._id)::text)