#	exclusive	inclusive	rows x	rows	loops	node
1.	0.015	825.939	↑ 1.7	12	1	→ Limit (cost=0.00..31543.64 rows=20 width=25) (actual time=200.229..825.939 rows=12 loops=1)
2.	0.060	825.924	↑ 6.6	12	1	→ Unique (cost=0.00..124597.39 rows=79 width=25) (actual time=200.227..825.924 rows=12 loops=1)
3.	0.094	825.864	↑ 8.2	45	1	→ Nested Loop Left Join (cost=0.00..124596.46 rows=370 width=25) (actual time=200.217..825.864 rows=45 loops=1)
4.	0.089	825.185	↑ 8.2	45	1	→ Nested Loop (cost=0.00..124486.58 rows=370 width=31) (actual time=200.159..825.185 rows=45 loops=1)
5.	0.043	824.263	↑ 8.3	49	1	→ Nested Loop (cost=0.00..124296.15 rows=407 width=35) (actual time=200.112..824.263 rows=49 loops=1)
6.	0.046	823.353	↑ 8.6	51	1	→ Nested Loop (cost=0.00..124086.40 rows=438 width=35) (actual time=200.048..823.353 rows=51 loops=1)
7.	0.447	822.119	↑ 9.1	54	1	→ Nested Loop (cost=0.00..123069.46 rows=490 width=29) (actual time=200.013..822.119 rows=54 loops=1)
8.	812.454	812.454	↑ 2.8	838	1	→ Index Scan using idx_taxo_records_name on taxo_records tr (cost=0.00..105560.81 rows=2338 width=29) (actual time=65.540..812.454 rows=838 loops=1) Filter: ((status = 1) AND (lower((name)::text) ~~* '%util%'::text))
9.	9.218	9.218	↓ 0.0	0	838	→ Index Scan using idx_service_post_taxonomy_id on service_post sp (cost=0.00..7.43 rows=5 width=8) (actual time=0.011..0.011 rows=0 loops=838) Index Cond: (sp.taxonomy_id_fk = tr.record_id) Filter: (sp.serverid = 'os164'::bpchar)
10.	1.188	1.188	↑ 1.0	1	54	→ Index Scan using service_dbs_pkey on service_dbs sd (cost=0.00..2.06 rows=1 width=14) (actual time=0.021..0.022 rows=1 loops=54) Index Cond: (sd.record_id = sp.service_id_fk) Filter: sd.visible
11.	0.867	0.867	↑ 1.0	1	51	→ Index Scan using program_dbs_pkey on program_dbs pr (cost=0.00..0.47 rows=1 width=8) (actual time=0.016..0.017 rows=1 loops=51) Index Cond: (pr.record_id = sd.program_code) Filter: pr.visible
12.	0.833	0.833	↑ 1.0	1	49	→ Index Scan using agency_dbs_pkey on agency_dbs ag (cost=0.00..0.46 rows=1 width=4) (actual time=0.017..0.017 rows=1 loops=49) Index Cond: (ag.record_id = pr.extra2) Filter: ag.visible
13.	0.585	0.585	↑ 1.0	1	45	→ Index Scan using locatorload_pkey on locator lctr (cost=0.00..0.28 rows=1 width=6) (actual time=0.013..0.013 rows=1 loops=45) Index Cond: ((lctr.zip)::text = "substring"((sd.contact_mailing_zip)::text, 1, 5))

                                                                                          QUERY PLAN                                                                                           
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..31543.64 rows=20 width=25) (actual time=200.229..825.939 rows=12 loops=1)
   ->  Unique  (cost=0.00..124597.39 rows=79 width=25) (actual time=200.227..825.924 rows=12 loops=1)
         ->  Nested Loop Left Join  (cost=0.00..124596.46 rows=370 width=25) (actual time=200.217..825.864 rows=45 loops=1)
               ->  Nested Loop  (cost=0.00..124486.58 rows=370 width=31) (actual time=200.159..825.185 rows=45 loops=1)
                     ->  Nested Loop  (cost=0.00..124296.15 rows=407 width=35) (actual time=200.112..824.263 rows=49 loops=1)
                           ->  Nested Loop  (cost=0.00..124086.40 rows=438 width=35) (actual time=200.048..823.353 rows=51 loops=1)
                                 ->  Nested Loop  (cost=0.00..123069.46 rows=490 width=29) (actual time=200.013..822.119 rows=54 loops=1)
                                       ->  Index Scan using idx_taxo_records_name on taxo_records tr  (cost=0.00..105560.81 rows=2338 width=29) (actual time=65.540..812.454 rows=838 loops=1)
                                             Filter: ((status = 1) AND (lower((name)::text) ~~* '%util%'::text))
                                       ->  Index Scan using idx_service_post_taxonomy_id on service_post sp  (cost=0.00..7.43 rows=5 width=8) (actual time=0.011..0.011 rows=0 loops=838)
                                             Index Cond: (sp.taxonomy_id_fk = tr.record_id)
                                             Filter: (sp.serverid = 'os164'::bpchar)
                                 ->  Index Scan using service_dbs_pkey on service_dbs sd  (cost=0.00..2.06 rows=1 width=14) (actual time=0.021..0.022 rows=1 loops=54)
                                       Index Cond: (sd.record_id = sp.service_id_fk)
                                       Filter: sd.visible
                           ->  Index Scan using program_dbs_pkey on program_dbs pr  (cost=0.00..0.47 rows=1 width=8) (actual time=0.016..0.017 rows=1 loops=51)
                                 Index Cond: (pr.record_id = sd.program_code)
                                 Filter: pr.visible
                     ->  Index Scan using agency_dbs_pkey on agency_dbs ag  (cost=0.00..0.46 rows=1 width=4) (actual time=0.017..0.017 rows=1 loops=49)
                           Index Cond: (ag.record_id = pr.extra2)
                           Filter: ag.visible
               ->  Index Scan using locatorload_pkey on locator lctr  (cost=0.00..0.28 rows=1 width=6) (actual time=0.013..0.013 rows=1 loops=45)
                     Index Cond: ((lctr.zip)::text = "substring"((sd.contact_mailing_zip)::text, 1, 5))
 Total runtime: 826.205 ms

Per node type stats

node type	count	sum of times	% of query
Index Scan	6	825.145 ms	99.9 %
Limit	1	0.015 ms	0.0 %
Nested Loop	4	0.625 ms	0.1 %
Nested Loop Left Join	1	0.094 ms	0.0 %
Unique	1	0.060 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
agency_dbs	1	0.833 ms	0.1 %
Index Scan	1	0.833 ms	100.0 %
locator	1	0.585 ms	0.1 %
Index Scan	1	0.585 ms	100.0 %
program_dbs	1	0.867 ms	0.1 %
Index Scan	1	0.867 ms	100.0 %
service_dbs	1	1.188 ms	0.1 %
Index Scan	1	1.188 ms	100.0 %
service_post	1	9.218 ms	1.1 %
Index Scan	1	9.218 ms	100.0 %
taxo_records	1	812.454 ms	98.4 %
Index Scan	1	812.454 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: Ups

Colorize:

Columns visibility:

Per node type stats

Per table stats