#	exclusive	inclusive	rows x	rows	loops	node
1.	24.132	199.319	↓ 1.9	5046	1	→ Hash Semi Join (cost=10199.17..12379.90 rows=2617 width=198) (actual time=164.955..199.319 rows=5046 loops=1) Hash Cond: (sys.id = apxi.sys_id)
2.	10.269	10.269	↑ 1.0	50000	1	→ Seq Scan on sys (cost=0.00..1966.00 rows=50000 width=198) (actual time=0.003..10.269 rows=50000 loops=1)
3.	3.307	164.918	↓ 2.0	5293	1	→ Hash (cost=10166.45..10166.45 rows=2617 width=4) (actual time=164.918..164.918 rows=5293 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 187kB
4.	2.558	161.611	↓ 2.0	5293	1	→ Nested Loop (cost=122.04..10166.45 rows=2617 width=4) (actual time=59.519..161.611 rows=5293 loops=1)
5.	0.009	7.141	↓ 2.0	2	1	→ HashAggregate (cost=64.03..64.04 rows=1 width=4) (actual time=7.139..7.141 rows=2 loops=1)
6.	0.120	7.132	↓ 2.0	2	1	→ Bitmap Heap Scan on app (cost=60.01..64.03 rows=1 width=4) (actual time=7.091..7.132 rows=2 loops=1) Recheck Cond: ((name)::text ~~* '%HP CART 2%'::text) Filter: ((version)::text ~~* '%Unknown%'::text)
7.	7.012	7.012	↓ 13.0	13	1	→ Bitmap Index Scan on trgm_app_name (cost=0.00..60.01 rows=1 width=0) (actual time=7.012..7.012 rows=13 loops=1) Index Cond: ((name)::text ~~* '%HP CART 2%'::text)
8.	83.662	151.912	↓ 1.0	2646	2	→ Bitmap Heap Scan on apxi (cost=58.02..10069.70 rows=2617 width=8) (actual time=64.659..75.956 rows=2646 loops=2) Recheck Cond: (app_id = app.id)
9.	68.250	68.250	↓ 1.0	2646	2	→ Bitmap Index Scan on apxi_appid_idx (cost=0.00..57.36 rows=2617 width=0) (actual time=34.125..34.125 rows=2646 loops=2) Index Cond: (app_id = app.id)

Hash Semi Join  (cost=10199.17..12379.90 rows=2617 width=198) (actual time=164.955..199.319 rows=5046 loops=1)
  Hash Cond: (sys.id = apxi.sys_id)
  ->  Seq Scan on sys  (cost=0.00..1966.00 rows=50000 width=198) (actual time=0.003..10.269 rows=50000 loops=1)
  ->  Hash  (cost=10166.45..10166.45 rows=2617 width=4) (actual time=164.918..164.918 rows=5293 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 187kB
        ->  Nested Loop  (cost=122.04..10166.45 rows=2617 width=4) (actual time=59.519..161.611 rows=5293 loops=1)
              ->  HashAggregate  (cost=64.03..64.04 rows=1 width=4) (actual time=7.139..7.141 rows=2 loops=1)
                    ->  Bitmap Heap Scan on app  (cost=60.01..64.03 rows=1 width=4) (actual time=7.091..7.132 rows=2 loops=1)
                          Recheck Cond: ((name)::text ~~* '%HP CART 2%'::text)
                          Filter: ((version)::text ~~* '%Unknown%'::text)
                          ->  Bitmap Index Scan on trgm_app_name  (cost=0.00..60.01 rows=1 width=0) (actual time=7.012..7.012 rows=13 loops=1)
                                Index Cond: ((name)::text ~~* '%HP CART 2%'::text)
              ->  Bitmap Heap Scan on apxi  (cost=58.02..10069.70 rows=2617 width=8) (actual time=64.659..75.956 rows=2646 loops=2)
                    Recheck Cond: (app_id = app.id)
                    ->  Bitmap Index Scan on apxi_appid_idx  (cost=0.00..57.36 rows=2617 width=0) (actual time=34.125..34.125 rows=2646 loops=2)
                          Index Cond: (app_id = app.id)
Total runtime: 199.925 ms

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	2	83.782 ms	42.0 %
Bitmap Index Scan	2	75.262 ms	37.8 %
Hash	1	3.307 ms	1.7 %
Hash Semi Join	1	24.132 ms	12.1 %
HashAggregate	1	0.009 ms	0.0 %
Nested Loop	1	2.558 ms	1.3 %
Seq Scan	1	10.269 ms	5.2 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
app	1	0.120 ms	0.1 %
Bitmap Heap Scan	1	0.120 ms	100.0 %
apxi	1	83.662 ms	42.0 %
Bitmap Heap Scan	1	83.662 ms	100.0 %
sys	1	10.269 ms	5.2 %
Seq Scan	1	10.269 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: gSg

Colorize:

Columns visibility:

Per node type stats

Per table stats