HTML
SOURCE
QUERY
REFORMATTED QUERY
COMMENTS
STATS

#	exclusive	inclusive	rows x	rows	loops	read	written	node
1.	105.176	5,064.373	↑ 27.7	233	1	0	0	→ Gather (cost=15,319.62..419,171.62 rows=6,464 width=71) (actual time=198.031..5,064.373 rows=233 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=43,564 read=255,910, temp read=40,423 written=40,580
2.	0.030	4,959.197	↑ 82.9	234	3 / 3	0	0	→ Parallel Append (cost=14,319.62..417,525.22 rows=6,464 width=71) (actual time=2,030.033..4,959.197 rows=78 loops=3) Buffers: shared hit=43,564 read=255,910, temp read=40,423 written=40,580
3.	0.089	2,017.240	↑ 35.0	153	3 / 3	0	0	→ Hash Left Join (cost=40,784.05..133,727.61 rows=1,784 width=71) (actual time=1,165.705..2,017.240 rows=51 loops=3) Hash Cond: (category4.provisioner = category4provisioner.id) Buffers: shared hit=10,906 read=78,309, temp read=17,890 written=17,984
4.	1,267.344	2,014.460	↑ 35.0	153 - 1,279,386	3 / 3	140 MB	78 MB	→ Parallel Hash Join (cost=40,585.26..133,524.13 rows=1,784 width=32) (actual time=1,162.998..2,014.460 rows=51 loops=3) Hash Cond: (gid.gid = category4.gid) Join Filter: ((lower(category4.placement1) ~~ '%12345%'::text) OR (lower(gid.placement2) ~~ '%12345%'::text)) Rows Removed by Join Filter: 426,462 Buffers: shared hit=10,783 read=78,309, temp read=17,890 written=17,984
5.	349.157	349.157	↓ 2.4	8,552,103	1 / 3	433 MB	0	→ Parallel Seq Scan on gid (cost=0.00..76,725.92 rows=1,187,792 width=7) (actual time=0.014..1,047.471 rows=2,850,701 loops=1) Buffers: shared hit=9,479 read=55,369
6.	199.090	397.959	↑ 1.2	1,339,335	3 / 3	0	64 MB	→ Parallel Hash (cost=29,794.56..29,794.56 rows=558,056 width=29) (actual time=397.959..397.959 rows=446,445 loops=3) Buckets: 65,536 Batches: 32 Memory Usage: 3,200kB Buffers: shared hit=1,274 read=22,940, temp written=8,124
7.	198.869	198.869	↓ 2.4	4,018,002	1 / 3	180 MB	0	→ Parallel Seq Scan on category4 (cost=0.00..29,794.56 rows=558,056 width=29) (actual time=67.702..596.606 rows=1,339,334 loops=1) Buffers: shared hit=1,274 read=22,940
8.	1.614	2.691	↑ 1.0	21,039	3 / 3	0	0	→ Hash (cost=111.13..111.13 rows=7,013 width=15) (actual time=2.691..2.691 rows=7,013 loops=3) Buckets: 8,192 Batches: 1 Memory Usage: 399kB Buffers: shared hit=123
9.	1.077	1.077	↑ 1.0	21,039	3 / 3	0	0	→ Seq Scan on category4provisioner (cost=0.00..111.13 rows=7,013 width=15) (actual time=0.022..1.077 rows=7,013 loops=3) Buffers: shared hit=123
10.	0.040	1,257.696	↑ 10.0	150	1 / 3	0	0	→ Nested Loop Left Join (cost=14,319.62..104,513.35 rows=500 width=71) (actual time=2,556.211..3,773.088 rows=50 loops=1) Buffers: shared hit=10,945 read=63,945, temp read=11,560 written=11,580
11.	0.023	1,257.556	↑ 10.0	150	1 / 3	0	0	→ Hash Left Join (cost=14,319.34..104,360.52 rows=500 width=32) (actual time=2,556.194..3,772.667 rows=50 loops=1) Hash Cond: (category1.mfr = category1provisioner.id) Buffers: shared hit=10,794 read=63,945, temp read=11,560 written=11,580
12.	767.362	1,257.390	↑ 10.0	150 - 975,708	1 / 3	91 MB	77 MB	→ Parallel Hash Join (cost=14,287.32..104,327.19 rows=500 width=25) (actual time=2,555.754..3,772.169 rows=50 loops=1) Hash Cond: (gid_1.gid = category1.gid) Join Filter: ((lower(category1.placement1) ~~ '%12345%'::text) OR (lower(gid_1.placement2) ~~ '%12345%'::text)) Rows Removed by Join Filter: 325,236 Buffers: shared hit=10,787 read=63,945, temp read=11,560 written=11,580
13.	354.798	354.798	↓ 2.4	8,552,103	1 / 3	433 MB	0	→ Parallel Seq Scan on gid gid_1 (cost=0.00..76,725.92 rows=1,187,792 width=7) (actual time=0.009..1,064.394 rows=2,850,701 loops=1) Buffers: shared hit=9,509 read=55,339
14.	58.945	135.230	↓ 2.4	1,125,543	1 / 3	0	14 MB	→ Parallel Hash (cost=11,417.25..11,417.25 rows=156,325 width=22) (actual time=405.689..405.690 rows=375,181 loops=1) Buckets: 65,536 Batches: 8 Memory Usage: 3,200kB Buffers: shared hit=1,248 read=8,606, temp written=1,764
15.	76.285	76.285	↓ 2.4	1,125,543	1 / 3	68 MB	0	→ Parallel Seq Scan on category1 (cost=0.00..11,417.25 rows=156,325 width=22) (actual time=102.842..228.855 rows=375,181 loops=1) Buffers: shared hit=1,248 read=8,606
16.	0.079	0.143	↑ 1.0	3,336	1 / 3	0	0	→ Hash (cost=18.12..18.12 rows=1,112 width=15) (actual time=0.430..0.430 rows=1,112 loops=1) Buckets: 2,048 Batches: 1 Memory Usage: 70kB Buffers: shared hit=7
17.	0.064	0.064	↑ 1.0	3,336	1 / 3	0	0	→ Seq Scan on category1provisioner (cost=0.00..18.12 rows=1,112 width=15) (actual time=0.020..0.192 rows=1,112 loops=1) Buffers: shared hit=7
18.	0.100	0.100	↑ 1.0	3	50 / 3	0	0	→ Index Scan using category1model_pkey on category1model (cost=0.29..0.31 rows=1 width=15) (actual time=0.006..0.006 rows=1 loops=50) Index Cond: (id = category1.model) Buffers: shared hit=151
19.	0.031	1,112.234	↑ 30.9	30	2 / 3	0	0	→ Nested Loop Left Join (cost=7,103.28..96,983.85 rows=309 width=70) (actual time=1,211.430..1,668.351 rows=10 loops=2) Buffers: shared hit=10,793 read=58,371, temp read=10,973 written=11,016
20.	0.018	1,112.154	↑ 30.9	30	2 / 3	0	0	→ Hash Left Join (cost=7,102.99..96,889.68 rows=309 width=34) (actual time=1,211.418..1,668.231 rows=10 loops=2) Hash Cond: (category2.provisioner = category2provisioner.id) Buffers: shared hit=10,729 read=58,371, temp read=10,973 written=11,016
21.	673.450	1,111.919	↑ 30.9	30 - 284,136	2 / 3	86 MB	77 MB	→ Parallel Hash Join (cost=7,080.60..96,866.47 rows=309 width=29) (actual time=1,211.083..1,667.879 rows=10 loops=2) Hash Cond: (gid_2.gid = category2.gid) Join Filter: ((lower(category2.placement1) ~~ '%12345%'::text) OR (lower(gid_2.placement2) ~~ '%12345%'::text)) Rows Removed by Join Filter: 94,712 Buffers: shared hit=10,719 read=58,371, temp read=10,973 written=11,016
22.	380.321	380.321	↓ 1.2	4,276,050	2 / 3	433 MB	0	→ Parallel Seq Scan on gid gid_2 (cost=0.00..76,725.92 rows=1,187,792 width=7) (actual time=0.032..570.482 rows=1,425,350 loops=2) Buffers: shared hit=9,439 read=55,409
23.	30.561	58.148	↓ 1.2	348,255	2 / 3	0	9.2 MB	→ Parallel Hash (cost=5,209.38..5,209.38 rows=96,738 width=26) (actual time=87.222..87.222 rows=116,085 loops=2) Buckets: 65,536 Batches: 8 Memory Usage: 2,272kB Buffers: shared hit=1,280 read=2,962, temp written=1,176
24.	27.587	27.587	↓ 2.4	696,510	1 / 3	24 MB	0	→ Parallel Seq Scan on category2 (cost=0.00..5,209.38 rows=96,738 width=26) (actual time=0.042..82.760 rows=232,170 loops=1) Buffers: shared hit=1,280 read=2,962
25.	0.114	0.217	↑ 1.0	2,319	2 / 3	0	0	→ Hash (cost=12.73..12.73 rows=773 width=13) (actual time=0.325..0.325 rows=773 loops=2) Buckets: 1,024 Batches: 1 Memory Usage: 44kB Buffers: shared hit=10
26.	0.103	0.103	↑ 1.0	2,319	2 / 3	0	0	→ Seq Scan on category2provisioner (cost=0.00..12.73 rows=773 width=13) (actual time=0.019..0.154 rows=773 loops=2) Buffers: shared hit=10
27.	0.049	0.049	↑ 1.0	3	21 / 3	0	0	→ Index Scan using category2model_pkey on category2model (cost=0.29..0.30 rows=1 width=12) (actual time=0.007..0.007 rows=1 loops=21) Index Cond: (id = category2.model) Buffers: shared hit=64
28.	0.008	571.997	↑ 12.5	24	1 / 3	0	0	→ Nested Loop Left Join (cost=2,329.19..82,203.45 rows=100 width=71) (actual time=196.802..1,715.990 rows=8 loops=1) Buffers: shared hit=10,920 read=55,285
29.	0.006	571.973	↑ 12.5	24	1 / 3	0	0	→ Hash Left Join (cost=2,328.91..82,173.04 rows=100 width=33) (actual time=196.786..1,715.919 rows=8 loops=1) Hash Cond: (category3.provisioner = category3provisioner.id) Buffers: shared hit=10,896 read=55,285
30.	341.709	571.931	↑ 12.5	24 - 185,586	1 / 3	0	0	→ Parallel Hash Join (cost=2,321.24..82,165.11 rows=100 width=26) (actual time=196.670..1,715.794 rows=8 loops=1) Hash Cond: (gid_3.gid = category3.gid) Join Filter: ((lower(category3.placement1) ~~ '%12345%'::text) OR (lower(gid_3.placement2) ~~ '%12345%'::text)) Rows Removed by Join Filter: 61,862 Buffers: shared hit=10,894 read=55,285
31.	180.312	180.312	↓ 2.4	8,552,103	1 / 3	432 MB	0	→ Parallel Seq Scan on gid gid_3 (cost=0.00..76,725.92 rows=1,187,792 width=7) (actual time=0.016..540.935 rows=2,850,701 loops=1) Buffers: shared hit=9,563 read=55,285
32.	16.214	49.910	↓ 1.7	225,132	1 / 3	0	0	→ Parallel Hash (cost=1,769.44..1,769.44 rows=44,144 width=23) (actual time=149.729..149.731 rows=75,044 loops=1) Buckets: 131,072 Batches: 1 Memory Usage: 5,312kB Buffers: shared hit=1,328
33.	33.696	33.696	↓ 1.7	225,132	1 / 3	0	0	→ Parallel Seq Scan on category3 (cost=0.00..1,769.44 rows=44,144 width=23) (actual time=73.373..101.088 rows=75,044 loops=1) Buffers: shared hit=1,328
34.	0.019	0.036	↑ 1.0	756	1 / 3	0	0	→ Hash (cost=4.52..4.52 rows=252 width=15) (actual time=0.106..0.107 rows=252 loops=1) Buckets: 1,024 Batches: 1 Memory Usage: 20kB Buffers: shared hit=2
35.	0.017	0.017	↑ 1.0	756	1 / 3	0	0	→ Seq Scan on category3provisioner (cost=0.00..4.52 rows=252 width=15) (actual time=0.014..0.051 rows=252 loops=1) Buffers: shared hit=2
36.	0.016	0.016	↑ 1.0	3	8 / 3	0	0	→ Index Scan using category3model_pkey on category3model (cost=0.28..0.30 rows=1 width=14) (actual time=0.006..0.006 rows=1 loops=8) Index Cond: (id = category3.model) Buffers: shared hit=24

Planning I/O	:	Buffers: shared hit=593
Planning time	:	3.651 ms
Execution time	:	5,120.511 ms

JIT:?
Functions:	291
Options:
Deforming	✓ (true)
Expressions	✓ (true)
Inlining	✗ (false)
Optimization	✗ (false)
Timing:
Emission	233.305 ms
Generation	64.980 ms
Inlining	0.000 ms
Optimization	7.771 ms
Total	306.056 ms

Gather  (cost=15319.62..419171.62 rows=6464 width=71) (actual time=198.031..5064.373 rows=233 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=43564 read=255910, temp read=40423 written=40580
  ->  Parallel Append  (cost=14319.62..417525.22 rows=6464 width=71) (actual time=2030.033..4959.197 rows=78 loops=3)
        Buffers: shared hit=43564 read=255910, temp read=40423 written=40580
        ->  Hash Left Join  (cost=40784.05..133727.61 rows=1784 width=71) (actual time=1165.705..2017.240 rows=51 loops=3)
              Hash Cond: (category4.provisioner = category4provisioner.id)
              Buffers: shared hit=10906 read=78309, temp read=17890 written=17984
              ->  Parallel Hash Join  (cost=40585.26..133524.13 rows=1784 width=32) (actual time=1162.998..2014.460 rows=51 loops=3)
                    Hash Cond: (gid.gid = category4.gid)
                    Join Filter: ((lower(category4.placement1) ~~ '%12345%'::text) OR (lower(gid.placement2) ~~ '%12345%'::text))
                    Rows Removed by Join Filter: 426462
                    Buffers: shared hit=10783 read=78309, temp read=17890 written=17984
                    ->  Parallel Seq Scan on gid  (cost=0.00..76725.92 rows=1187792 width=7) (actual time=0.014..1047.471 rows=2850701 loops=1)
                          Buffers: shared hit=9479 read=55369
                    ->  Parallel Hash  (cost=29794.56..29794.56 rows=558056 width=29) (actual time=397.959..397.959 rows=446445 loops=3)
                          Buckets: 65536  Batches: 32  Memory Usage: 3200kB
                          Buffers: shared hit=1274 read=22940, temp written=8124
                          ->  Parallel Seq Scan on category4  (cost=0.00..29794.56 rows=558056 width=29) (actual time=67.702..596.606 rows=1339334 loops=1)
                                Buffers: shared hit=1274 read=22940
              ->  Hash  (cost=111.13..111.13 rows=7013 width=15) (actual time=2.691..2.691 rows=7013 loops=3)
                    Buckets: 8192  Batches: 1  Memory Usage: 399kB
                    Buffers: shared hit=123
                    ->  Seq Scan on category4provisioner  (cost=0.00..111.13 rows=7013 width=15) (actual time=0.022..1.077 rows=7013 loops=3)
                          Buffers: shared hit=123
        ->  Nested Loop Left Join  (cost=14319.62..104513.35 rows=500 width=71) (actual time=2556.211..3773.088 rows=50 loops=1)
              Buffers: shared hit=10945 read=63945, temp read=11560 written=11580
              ->  Hash Left Join  (cost=14319.34..104360.52 rows=500 width=32) (actual time=2556.194..3772.667 rows=50 loops=1)
                    Hash Cond: (category1.mfr = category1provisioner.id)
                    Buffers: shared hit=10794 read=63945, temp read=11560 written=11580
                    ->  Parallel Hash Join  (cost=14287.32..104327.19 rows=500 width=25) (actual time=2555.754..3772.169 rows=50 loops=1)
                          Hash Cond: (gid_1.gid = category1.gid)
                          Join Filter: ((lower(category1.placement1) ~~ '%12345%'::text) OR (lower(gid_1.placement2) ~~ '%12345%'::text))
                          Rows Removed by Join Filter: 325236
                          Buffers: shared hit=10787 read=63945, temp read=11560 written=11580
                          ->  Parallel Seq Scan on gid gid_1  (cost=0.00..76725.92 rows=1187792 width=7) (actual time=0.009..1064.394 rows=2850701 loops=1)
                                Buffers: shared hit=9509 read=55339
                          ->  Parallel Hash  (cost=11417.25..11417.25 rows=156325 width=22) (actual time=405.689..405.690 rows=375181 loops=1)
                                Buckets: 65536  Batches: 8  Memory Usage: 3200kB
                                Buffers: shared hit=1248 read=8606, temp written=1764
                                ->  Parallel Seq Scan on category1  (cost=0.00..11417.25 rows=156325 width=22) (actual time=102.842..228.855 rows=375181 loops=1)
                                      Buffers: shared hit=1248 read=8606
                    ->  Hash  (cost=18.12..18.12 rows=1112 width=15) (actual time=0.430..0.430 rows=1112 loops=1)
                          Buckets: 2048  Batches: 1  Memory Usage: 70kB
                          Buffers: shared hit=7
                          ->  Seq Scan on category1provisioner  (cost=0.00..18.12 rows=1112 width=15) (actual time=0.020..0.192 rows=1112 loops=1)
                                Buffers: shared hit=7
              ->  Index Scan using category1model_pkey on category1model  (cost=0.29..0.31 rows=1 width=15) (actual time=0.006..0.006 rows=1 loops=50)
                    Index Cond: (id = category1.model)
                    Buffers: shared hit=151
        ->  Nested Loop Left Join  (cost=7103.28..96983.85 rows=309 width=70) (actual time=1211.430..1668.351 rows=10 loops=2)
              Buffers: shared hit=10793 read=58371, temp read=10973 written=11016
              ->  Hash Left Join  (cost=7102.99..96889.68 rows=309 width=34) (actual time=1211.418..1668.231 rows=10 loops=2)
                    Hash Cond: (category2.provisioner = category2provisioner.id)
                    Buffers: shared hit=10729 read=58371, temp read=10973 written=11016
                    ->  Parallel Hash Join  (cost=7080.60..96866.47 rows=309 width=29) (actual time=1211.083..1667.879 rows=10 loops=2)
                          Hash Cond: (gid_2.gid = category2.gid)
                          Join Filter: ((lower(category2.placement1) ~~ '%12345%'::text) OR (lower(gid_2.placement2) ~~ '%12345%'::text))
                          Rows Removed by Join Filter: 94712
                          Buffers: shared hit=10719 read=58371, temp read=10973 written=11016
                          ->  Parallel Seq Scan on gid gid_2  (cost=0.00..76725.92 rows=1187792 width=7) (actual time=0.032..570.482 rows=1425350 loops=2)
                                Buffers: shared hit=9439 read=55409
                          ->  Parallel Hash  (cost=5209.38..5209.38 rows=96738 width=26) (actual time=87.222..87.222 rows=116085 loops=2)
                                Buckets: 65536  Batches: 8  Memory Usage: 2272kB
                                Buffers: shared hit=1280 read=2962, temp written=1176
                                ->  Parallel Seq Scan on category2  (cost=0.00..5209.38 rows=96738 width=26) (actual time=0.042..82.760 rows=232170 loops=1)
                                      Buffers: shared hit=1280 read=2962
                    ->  Hash  (cost=12.73..12.73 rows=773 width=13) (actual time=0.325..0.325 rows=773 loops=2)
                          Buckets: 1024  Batches: 1  Memory Usage: 44kB
                          Buffers: shared hit=10
                          ->  Seq Scan on category2provisioner  (cost=0.00..12.73 rows=773 width=13) (actual time=0.019..0.154 rows=773 loops=2)
                                Buffers: shared hit=10
              ->  Index Scan using category2model_pkey on category2model  (cost=0.29..0.30 rows=1 width=12) (actual time=0.007..0.007 rows=1 loops=21)
                    Index Cond: (id = category2.model)
                    Buffers: shared hit=64
        ->  Nested Loop Left Join  (cost=2329.19..82203.45 rows=100 width=71) (actual time=196.802..1715.990 rows=8 loops=1)
              Buffers: shared hit=10920 read=55285
              ->  Hash Left Join  (cost=2328.91..82173.04 rows=100 width=33) (actual time=196.786..1715.919 rows=8 loops=1)
                    Hash Cond: (category3.provisioner = category3provisioner.id)
                    Buffers: shared hit=10896 read=55285
                    ->  Parallel Hash Join  (cost=2321.24..82165.11 rows=100 width=26) (actual time=196.670..1715.794 rows=8 loops=1)
                          Hash Cond: (gid_3.gid = category3.gid)
                          Join Filter: ((lower(category3.placement1) ~~ '%12345%'::text) OR (lower(gid_3.placement2) ~~ '%12345%'::text))
                          Rows Removed by Join Filter: 61862
                          Buffers: shared hit=10894 read=55285
                          ->  Parallel Seq Scan on gid gid_3  (cost=0.00..76725.92 rows=1187792 width=7) (actual time=0.016..540.935 rows=2850701 loops=1)
                                Buffers: shared hit=9563 read=55285
                          ->  Parallel Hash  (cost=1769.44..1769.44 rows=44144 width=23) (actual time=149.729..149.731 rows=75044 loops=1)
                                Buckets: 131072  Batches: 1  Memory Usage: 5312kB
                                Buffers: shared hit=1328
                                ->  Parallel Seq Scan on category3  (cost=0.00..1769.44 rows=44144 width=23) (actual time=73.373..101.088 rows=75044 loops=1)
                                      Buffers: shared hit=1328
                    ->  Hash  (cost=4.52..4.52 rows=252 width=15) (actual time=0.106..0.107 rows=252 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 20kB
                          Buffers: shared hit=2
                          ->  Seq Scan on category3provisioner  (cost=0.00..4.52 rows=252 width=15) (actual time=0.014..0.051 rows=252 loops=1)
                                Buffers: shared hit=2
              ->  Index Scan using category3model_pkey on category3model  (cost=0.28..0.30 rows=1 width=14) (actual time=0.006..0.006 rows=1 loops=8)
                    Index Cond: (id = category3.model)
                    Buffers: shared hit=24
Planning:
  Buffers: shared hit=593
Planning Time: 3.651 ms
JIT:
  Functions: 291
  Options: Inlining false, Optimization false, Expressions true, Deforming true
  Timing: Generation 64.980 ms, Inlining 0.000 ms, Optimization 7.771 ms, Emission 233.305 ms, Total 306.056 ms
Execution Time: 5120.511 ms

explain (analyze, buffers)
Select 'Category1' as ItemType, Category1.GID as GID, Category1.Placement1 as SerialNo, GID.Placement2 as Placement2, Category1Provisioner.Provisioner as Mfr, Category1Model.Model as Model From Category1 JOIN GID ON GID.GID = Category1.GID LEFT OUTER JOIN Category1Provisioner ON Category1Provisioner.ID = Category1.Mfr LEFT OUTER JOIN Category1Model ON Category1Model.ID = Category1.Model WHERE lower(Category1.Placement1) LIKE '%12345%' OR lower(GID.Placement2) LIKE '%12345%'
    union all
Select 'Category2' as ItemType, Category2.GID as GID, Category2.Placement1 as SerialNo, GID.Placement2 as Placement2, Category2Provisioner.Category2Provisioner as Mfr, Category2Model.Model as Model From Category2 JOIN GID ON GID.GID = Category2.GID LEFT OUTER JOIN Category2Provisioner ON Category2Provisioner.ID = Category2.Provisioner LEFT OUTER JOIN Category2Model ON Category2Model.ID = Category2.Model WHERE lower(Category2.Placement1) LIKE '%12345%' OR lower(GID.Placement2) LIKE '%12345%'
    union all
Select 'Category3' as ItemType, Category3.GID as GID, Category3.Placement1 as SerialNo, GID.Placement2 as Placement2, Category3Provisioner.Provisioner as Mfr, Category3Model.Model as Model From Category3 JOIN GID ON GID.GID = Category3.GID LEFT OUTER JOIN Category3Provisioner ON Category3Provisioner.ID = Category3.Provisioner LEFT OUTER JOIN Category3Model ON Category3Model.ID = Category3.Model WHERE lower(Category3.Placement1) LIKE '%12345%' OR lower(GID.Placement2) LIKE '%12345%'
    union all
Select 'Category4' as ItemType, Category4.GID as GID, Category4.Placement1 as SerialNo, GID.Placement2 as Placement2, Category4Provisioner.Provisioner as Mfr, Category4.Model as Model From Category4 JOIN GID ON GID.GID = Category4.GID LEFT OUTER JOIN Category4Provisioner ON Category4Provisioner.ID = Category4.Provisioner WHERE lower(Category4.Placement1) LIKE '%12345%' OR lower(GID.Placement2) LIKE '%12345%'
;

EXPLAIN (
    analyze,
    buffers
)
(
    SELECT
        'Category1' AS itemtype,
        category1.gid AS gid,
        category1.placement1 AS serialno,
        gid.placement2 AS placement2,
        category1provisioner.provisioner AS mfr,
        category1model.model AS model
    FROM
        category1
        JOIN gid ON gid.gid = category1.gid
        LEFT JOIN category1provisioner ON category1provisioner.id = category1.mfr
        LEFT JOIN category1model ON category1model.id = category1.model
    WHERE
        lower( category1.placement1 ) LIKE '%12345%' OR
        lower( gid.placement2 ) LIKE '%12345%'
)
UNION ALL
(
    SELECT
        'Category2' AS itemtype,
        category2.gid AS gid,
        category2.placement1 AS serialno,
        gid.placement2 AS placement2,
        category2provisioner.category2provisioner AS mfr,
        category2model.model AS model
    FROM
        category2
        JOIN gid ON gid.gid = category2.gid
        LEFT JOIN category2provisioner ON category2provisioner.id = category2.provisioner
        LEFT JOIN category2model ON category2model.id = category2.model
    WHERE
        lower( category2.placement1 ) LIKE '%12345%' OR
        lower( gid.placement2 ) LIKE '%12345%'
)
UNION ALL
(
    SELECT
        'Category3' AS itemtype,
        category3.gid AS gid,
        category3.placement1 AS serialno,
        gid.placement2 AS placement2,
        category3provisioner.provisioner AS mfr,
        category3model.model AS model
    FROM
        category3
        JOIN gid ON gid.gid = category3.gid
        LEFT JOIN category3provisioner ON category3provisioner.id = category3.provisioner
        LEFT JOIN category3model ON category3model.id = category3.model
    WHERE
        lower( category3.placement1 ) LIKE '%12345%' OR
        lower( gid.placement2 ) LIKE '%12345%'
)
UNION ALL
(
    SELECT
        'Category4' AS itemtype,
        category4.gid AS gid,
        category4.placement1 AS serialno,
        gid.placement2 AS placement2,
        category4provisioner.provisioner AS mfr,
        category4.model AS model
    FROM
        category4
        JOIN gid ON gid.gid = category4.gid
        LEFT JOIN category4provisioner ON category4provisioner.id = category4.provisioner
    WHERE
        lower( category4.placement1 ) LIKE '%12345%' OR
        lower( gid.placement2 ) LIKE '%12345%'
);
-- Formatted by Pg::SQL::PrettyPrinter

                            Table "public.category1"
    Column     |           Type           | Collation | Nullable | Default 
---------------+--------------------------+-----------+----------+---------
 gid           | integer                  |           | not null | 
 category1mfr        | integer                  |           |          | 
 category1type       | integer                  |           |          | 
 category1speed      | integer                  |           |          | 
 tester         | integer                  |           |          | 
 mfr           | integer                  |           |          | 
 placement1  | text                     |           |          | 
Indexes:
    "category1_pkey" PRIMARY KEY, btree (gid)
    "category1_placement1_trigram_lower" gin (lower(placement1) gin_trgm_ops)


                            Table "public.category2"
      Column      |           Type           | Collation | Nullable | Default 
------------------+--------------------------+-----------+----------+---------
 gid              | integer                  |           | not null | 
 type             | integer                  |           |          | 
 tester            | integer                  |           |          | 
 placement1     | text                     |           |          | 
 provisioner     | integer                  |           |          | 
Indexes:
    "category2_pkey" PRIMARY KEY, btree (gid)
    "category2_placement1_trigram_lower" gin (lower(placement1) gin_trgm_ops)


                             Table "public.category3"
       Column       |           Type           | Collation | Nullable | Default 
--------------------+--------------------------+-----------+----------+---------
 gid                | integer                  |           | not null | 
 provisioner       | integer                  |           |          | 
 tester              | integer                  |           |          | 
 placement1       | text                     |           |          | 
 type               | integer                  |           |          | 
Indexes:
    "category3_pkey" PRIMARY KEY, btree (gid)
    "category3_placement1_trigram_lower" gin (lower(placement1) gin_trgm_ops)


                               Table "public.category4"
         Column          |           Type           | Collation | Nullable | Default 
-------------------------+--------------------------+-----------+----------+---------
 gid                     | integer                  |           | not null | 
 provisioner            | integer                  |           |          | 
 tester                   | text                     |           |          | 
 placement1            | text                     |           |          | 
 category                | text                     |           |          | 
Indexes:
    "category4_pkey" PRIMARY KEY, btree (gid)
    "category4_placement1_trigram_lower" gin (lower(placement1) gin_trgm_ops)


                                           Table "public.gid"
      Column       |           Type           | Collation | Nullable |             Default              
-------------------+--------------------------+-----------+----------+----------------------------------
 gid               | integer                  |           | not null | nextval('gid_gid_seq'::regclass)
 placement2          | text                     |           |          | 
 description       | text                     |           |          | 
 category          | text                     |           |          | 
 parentgid         | integer                  |           |          | 
Indexes:
    "gid_pkey" PRIMARY KEY, btree (gid)
    "gid_placement2_trigram_lower" gin (lower(placement2) gin_trgm_ops)
    "gid_category_lower" btree (lower(category))

I/O stats

Query read 2.3 GB bytes from disk (or system disk cache)
Query wrote 318 MB bytes to disk

Per node type stats

node type	count	sum of times	% of query
Gather	1	105.176 ms	2.1 %
Hash	4	1.826 ms	0.0 %
Hash Left Join	4	0.136 ms	0.0 %
Index Scan	3	0.165 ms	0.0 %
Nested Loop Left Join	3	0.079 ms	0.0 %
Parallel Append	1	0.030 ms	0.0 %
Parallel Hash	4	304.811 ms	6.0 %
Parallel Hash Join	4	3,049.865 ms	60.2 %
Parallel Seq Scan	8	1,601.024 ms	31.6 %
Seq Scan	4	1.261 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
category1	1	76.285 ms	1.5 %
Parallel Seq Scan	1	76.285 ms	100.0 %
category1model	1	0.100 ms	0.0 %
Index Scan	1	0.100 ms	100.0 %
category1provisioner	1	0.064 ms	0.0 %
Seq Scan	1	0.064 ms	100.0 %
category2	1	27.587 ms	0.5 %
Parallel Seq Scan	1	27.587 ms	100.0 %
category2model	1	0.049 ms	0.0 %
Index Scan	1	0.049 ms	100.0 %
category2provisioner	1	0.103 ms	0.0 %
Seq Scan	1	0.103 ms	100.0 %
category3	1	33.696 ms	0.7 %
Parallel Seq Scan	1	33.696 ms	100.0 %
category3model	1	0.016 ms	0.0 %
Index Scan	1	0.016 ms	100.0 %
category3provisioner	1	0.017 ms	0.0 %
Seq Scan	1	0.017 ms	100.0 %
category4	1	198.869 ms	3.9 %
Parallel Seq Scan	1	198.869 ms	100.0 %
category4provisioner	1	1.077 ms	0.0 %
Seq Scan	1	1.077 ms	100.0 %
gid	4	1,264.588 ms	25.0 %
Parallel Seq Scan	4	1,264.588 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Hjk23

Color mode:

Visible columns:

I/O stats

Per node type stats

Per table stats