explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aTtp

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 185,024.607 ↑ 2,000.0 1 1

Limit (cost=1,209,246.91..1,209,251.91 rows=2,000 width=1,061) (actual time=185,024.607..185,024.607 rows=1 loops=1)

2. 0.014 185,024.605 ↑ 9,925.0 1 1

Sort (cost=1,209,246.91..1,209,271.72 rows=9,925 width=1,061) (actual time=185,024.605..185,024.605 rows=1 loops=1)

  • Sort Key: contacts.id
  • Sort Method: quicksort Memory: 25kB
3. 753.903 185,024.591 ↑ 9,925.0 1 1

Hash Semi Join (cost=737,619.87..1,208,653.10 rows=9,925 width=1,061) (actual time=31,943.399..185,024.591 rows=1 loops=1)

  • Hash Cond: (contacts.id = contacts_1.id)
4. 184,238.702 184,255.064 ↓ 1.1 3,811,980 1

Seq Scan on contacts (cost=535,042.82..994,623.33 rows=3,615,249 width=1,061) (actual time=16.421..184,255.064 rows=3,811,980 loops=1)

  • Filter: ((deleted_at IS NULL) AND (state <> 2) AND (NOT (hashed SubPlan 1)) AND (id > 36085))
  • Rows Removed by Filter: 252,744
5.          

SubPlan (for Seq Scan)

6. 0.005 16.362 ↑ 4,180.0 2 1

Nested Loop Anti Join (cost=201,890.66..535,021.92 rows=8,360 width=4) (actual time=16.256..16.362 rows=2 loops=1)

7. 0.007 1.846 ↑ 6,674.3 3 1

Nested Loop (cost=201,890.23..375,929.61 rows=20,023 width=4) (actual time=1.780..1.846 rows=3 loops=1)

8. 0.064 1.794 ↑ 7,048.7 3 1

HashAggregate (cost=201,889.80..202,101.26 rows=21,146 width=4) (actual time=1.764..1.794 rows=3 loops=1)

9. 0.005 1.730 ↑ 7,048.7 3 1

Nested Loop (cost=1.00..201,836.93 rows=21,146 width=4) (actual time=1.486..1.730 rows=3 loops=1)

10. 1.470 1.470 ↑ 7,905.7 3 1

Index Scan using index_contacts_lists_on_list_id on contacts_lists contacts_lists_1 (cost=0.56..4,921.24 rows=23,717 width=4) (actual time=1.469..1.470 rows=3 loops=1)

  • Index Cond: (list_id = 327)
11. 0.255 0.255 ↑ 1.0 1 3

Index Scan using contacts_pkey on contacts contacts_3 (cost=0.43..8.29 rows=1 width=4) (actual time=0.083..0.085 rows=1 loops=3)

  • Index Cond: (id = contacts_lists_1.contact_id)
  • Filter: ((deleted_at IS NULL) AND (state <> 2) AND (state = 1))
12. 0.045 0.045 ↑ 1.0 1 3

Index Scan using contacts_pkey on contacts contacts_2 (cost=0.43..8.20 rows=1 width=4) (actual time=0.014..0.015 rows=1 loops=3)

  • Index Cond: (id = contacts_3.id)
  • Filter: ((deleted_at IS NULL) AND (state <> 2))
13. 14.511 14.511 ↓ 0.0 0 3

Index Only Scan using index_contact_classifications_on_contact_id on contact_classifications (cost=0.43..7.94 rows=1 width=4) (actual time=4.837..4.837 rows=0 loops=3)

  • Index Cond: (contact_id = contacts_2.id)
  • Heap Fetches: 1
14. 0.002 15.624 ↑ 7,048.7 3 1

Hash (cost=202,312.72..202,312.72 rows=21,146 width=4) (actual time=15.624..15.624 rows=3 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 1kB
15. 0.053 15.622 ↑ 7,048.7 3 1

HashAggregate (cost=201,889.80..202,101.26 rows=21,146 width=4) (actual time=15.602..15.622 rows=3 loops=1)

16. 0.007 15.569 ↑ 7,048.7 3 1

Nested Loop (cost=1.00..201,836.93 rows=21,146 width=4) (actual time=14.939..15.569 rows=3 loops=1)

17. 6.064 6.064 ↑ 7,905.7 3 1

Index Scan using index_contacts_lists_on_list_id on contacts_lists (cost=0.56..4,921.24 rows=23,717 width=4) (actual time=6.063..6.064 rows=3 loops=1)

  • Index Cond: (list_id = 327)
18. 9.498 9.498 ↑ 1.0 1 3

Index Scan using contacts_pkey on contacts contacts_1 (cost=0.43..8.29 rows=1 width=4) (actual time=3.166..3.166 rows=1 loops=3)

  • Index Cond: (id = contacts_lists.contact_id)
  • Filter: ((deleted_at IS NULL) AND (state <> 2) AND (state = 1))