在我的应用程序中，我有一个条件，当将数据放置在特定的搜索字段中时，该应用程序必须在某些表中查找。 执行此工作的查询太慢，我找不到更好的写方法。
SELECT DISTINCT viagem0_.id AS id1_134_,viagem0_.BASE_ID AS BASE_ID21_134_,viagem0_.categoriaPedagio AS categori2_134_,viagem0_.consumoKmL AS consumoK3_134_,viagem0_.custoCombustivel AS custoCom4_134_,viagem0_.custoLitro AS custoLit5_134_,viagem0_.custoPedagio AS custoPed6_134_,viagem0_.custoTotal AS custoTot7_134_,viagem0_.date AS date8_134_,viagem0_.dispara_id AS dispara22_134_,viagem0_.distanciaTotal AS distanci9_134_,viagem0_.embarcadora_id AS embarca23_134_,viagem0_.EMPRESA_ID AS EMPRESA24_134_,viagem0_.gastoCombustivel AS gastoCo10_134_,viagem0_.gerenciadora AS gerenci11_134_,viagem0_.kmPlanejada AS kmPlane12_134_,viagem0_.localidade_id AS localid25_134_,viagem0_.MACROGrupO_ID AS MACROGR26_134_,viagem0_.manifesto AS manifes13_134_,viagem0_.monitorada AS monitor14_134_,viagem0_.numLiberacao AS numLibe15_134_,viagem0_.numero AS numero16_134_,viagem0_.seguradora AS segurad17_134_,viagem0_.status AS status18_134_,viagem0_.telefoneContato_id AS telefon27_134_,viagem0_.tempo AS tempo19_134_,viagem0_.tipo AS tipo20_134_,viagem0_.veiculo_id AS veiculo28_134_
FROM Viagem viagem0_
LEFT OUTER JOIN Automovel veiculo1_ ON viagem0_.veiculo_id=veiculo1_.id
LEFT OUTER JOIN Viagem_Automovel composicoe2_ ON viagem0_.id=composicoe2_.Viagem_id
LEFT OUTER JOIN Automovel composicao3_ ON composicoe2_.composicoes_id=composicao3_.id
LEFT OUTER JOIN viagem_motorista motoristas4_ ON viagem0_.id=motoristas4_.VIAGEM_ID
LEFT OUTER JOIN Motorista motorista5_ ON motoristas4_.MOTORISTAS_ID=motorista5_.id
LEFT OUTER JOIN Pessoa pessoa6_ ON motorista5_.PESSOA_ID=pessoa6_.id
LEFT OUTER JOIN ElementoViagem elementosv7_ ON viagem0_.id=elementosv7_.viagem_id
LEFT OUTER JOIN PontoViagem pontoviage8_ ON elementosv7_.ponto_id=pontoviage8_.id
LEFT OUTER JOIN Documento documentos9_ ON elementosv7_.id=documentos9_.elementoViagem_id
CROSS JOIN Localidade localidade10_
CROSS JOIN Empresa empresa11_
WHERE viagem0_.localidade_id=localidade10_.id
AND viagem0_.EMPRESA_ID=empresa11_.id
AND (upper(veiculo1_.placa) LIKE '1336536'
OR upper(composicao3_.placa) LIKE '1336536'
OR upper(pessoa6_.nome) LIKE '1336536'
OR upper(pontoviage8_.nome) LIKE '1336536'
OR upper(localidade10_.nome) LIKE '1336536'
OR upper(pontoviage8_.cnpj) LIKE '1336536'
OR upper(pessoa6_.cpfCnpj) LIKE '1336536'
OR (viagem0_.id||'') LIKE '1336536'
OR upper(empresa11_.nome) LIKE '1336536'
OR upper(empresa11_.cnpj) LIKE '1336536'
OR upper(documentos9_.numero) LIKE '1336536')
AND viagem0_.status<>'C'
AND viagem0_.status<>'N'
ORDER BY viagem0_.id DESC
LIMIT 10;
QUERY PLAN
Limit (cost=1335604.19..1335604.91 rows=10 width=171) (actual time=68256.100..68256.254 rows=1 loops=1)
Buffers: shared hit=175517 read=197905,temp read=167046 written=167046
I/O Timings: read=88397.284
-> Unique (cost=1335604.19..1352666.55 rows=235343 width=171) (actual time=64815.780..64815.933 rows=1 loops=1)
Buffers: shared hit=175517 read=197905,temp read=167046 written=167046
I/O Timings: read=88397.284
-> Sort (cost=1335604.19..1336192.54 rows=235343 width=171) (actual time=64815.770..64815.771 rows=2 loops=1)
Sort Key: viagem0_.id DESC,viagem0_.base_id,viagem0_.categoriapedagio,viagem0_.consumokml,viagem0_.custocombustivel,viagem0_.custolitro,viagem0_.custopedagio,viagem0_.custototal,viagem0_.date,viagem0_.dispara_id,viagem0_.distanciatotal,viagem0_.embarcadora_id,viagem0_.empresa_id,viagem0_.gastocombustivel,viagem0_.gerenciadora,viagem0_.kmplanejada,viagem0_.localidade_id,viagem0_.macrogrupo_id,viagem0_.manifesto,viagem0_.monitorada,viagem0_.numliberacao,viagem0_.numero,viagem0_.seguradora,viagem0_.status,viagem0_.telefonecontato_id,viagem0_.tempo,viagem0_.tipo,viagem0_.veiculo_id
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=175517 read=197905,temp read=167046 written=167046
I/O Timings: read=88397.284
-> Hash Join (cost=919755.65..1314606.41 rows=235343 width=171) (actual time=48490.327..64815.593 rows=2 loops=1)
Hash Cond: (viagem0_.empresa_id = empresa11_.id)
Join Filter: ((upper((veiculo1_.placa)::text) ~~ '1336536'::text) OR (upper((composicao3_.placa)::text) ~~ '1336536'::text) OR (upper((pessoa6_.nome)::text) ~~ '1336536'::text) OR (upper((pontoviage8_.nome)::text) ~~ '1336536'::text) OR (upper((localidade10_.nome)::text) ~~ '1336536'::text) OR (upper((pontoviage8_.cnpj)::text) ~~ '1336536'::text) OR (upper((pessoa6_.cpfcnpj)::text) ~~ '1336536'::text) OR (((viagem0_.id)::text || ''::text) ~~ '1336536'::text) OR (upper((empresa11_.nome)::text) ~~ '1336536'::text) OR (upper((empresa11_.cnpj)::text) ~~ '1336536'::text) OR (upper((documentos9_.numero)::text) ~~ '1336536'::text))
Rows Removed by Join Filter: 4156758
Buffers: shared hit=175494 read=197905,temp read=167046 written=167046
I/O Timings: read=88397.284
-> Hash Join (cost=919717.73..1303030.32 rows=4387011 width=294) (actual time=42997.607..53546.036 rows=4156762 loops=1)
Hash Cond: (viagem0_.localidade_id = localidade10_.id)
Buffers: shared hit=175473 read=197905,temp read=167046 written=167046
I/O Timings: read=88397.284
-> Hash Left Join (cost=919655.25..1291424.82 rows=4387011 width=274) (actual time=42996.718..52705.086 rows=4157811 loops=1)
Hash Cond: (elementosv7_.ponto_id = pontoviage8_.id)
Buffers: shared hit=175451 read=197905,temp read=167046 written=167046
I/O Timings: read=88397.284
-> Hash Right Join (cost=911165.81..1271419.25 rows=4387011 width=241) (actual time=42873.957..50950.728 rows=4157811 loops=1)
Hash Cond: (documentos9_.elementoviagem_id = elementosv7_.id)
Buffers: shared hit=171935 read=197905,temp read=167046 written=167046
I/O Timings: read=88397.284
-> Seq Scan on documento documentos9_ (cost=0.00..106054.72 rows=4466472 width=18) (actual time=0.128..1886.821 rows=4465911 loops=1)
Buffers: shared hit=40725 read=20666
I/O Timings: read=766.279
-> Hash (cost=714949.17..714949.17 rows=4387011 width=239) (actual time=42870.431..42870.431 rows=3378013 loops=1)
Buckets: 262144 Batches: 32 Memory Usage: 30276kB
Buffers: shared hit=131210 read=177239,temp read=45560 written=150470
I/O Timings: read=87631.004
-> Hash Right Join (cost=406354.84..714949.17 rows=4387011 width=239) (actual time=34157.894..39838.762 rows=3378013 loops=1)
Hash Cond: (elementosv7_.viagem_id = viagem0_.id)
Buffers: shared hit=131210 read=177239,temp read=45560 written=45560
I/O Timings: read=87631.004
-> Seq Scan on elementoviagem elementosv7_ (cost=0.00..143291.35 rows=5959135 width=24) (actual time=0.321..1671.419 rows=5948261 loops=1)
Buffers: shared hit=311 read=83389
I/O Timings: read=943.516
-> Hash (cost=364305.53..364305.53 rows=983065 width=223) (actual time=34154.455..34154.456 rows=983599 loops=1)
Buckets: 262144 Batches: 4 Memory Usage: 63059kB
Buffers: shared hit=130899 read=93850,temp written=21839
I/O Timings: read=86687.488
-> Gather (cost=76427.09..364305.53 rows=983065 width=223) (actual time=3986.350..33084.201 rows=983599 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=130899 read=93850
I/O Timings: read=86687.488
-> Parallel Hash Left Join (cost=75427.09..264999.03 rows=409610 width=223) (actual time=3957.158..33401.629 rows=327866 loops=3)
Hash Cond: (motorista5_.pessoa_id = pessoa6_.id)
Buffers: shared hit=130899 read=93850
I/O Timings: read=86687.488
-> Parallel Hash Left Join (cost=69392.60..257889.29 rows=409610 width=195) (actual time=2273.920..31402.992 rows=327866 loops=3)
Hash Cond: (motoristas4_.motoristas_id = motorista5_.id)
Buffers: shared hit=129909 read=91824
I/O Timings: read=86510.625
-> Parallel Hash Left Join (cost=63154.12..250575.56 rows=409610 width=195) (actual time=2219.195..31069.151 rows=327866 loops=3)
Hash Cond: (viagem0_.id = motoristas4_.viagem_id)
Buffers: shared hit=125419 read=91824
I/O Timings: read=86510.625
-> Parallel Hash Left Join (cost=43254.05..227449.27 rows=406264 width=187) (actual time=240.988..28715.994 rows=324951 loops=3)
Hash Cond: (composicoe2_.composicoes_id = composicao3_.id)
Buffers: shared hit=123874 read=86091
I/O Timings: read=81423.789
-> Hash Left Join (cost=23974.57..207103.31 rows=406264 width=187) (actual time=111.888..28458.723 rows=324951 loops=3)
Hash Cond: (viagem0_.id = composicoe2_.viagem_id)
Buffers: shared hit=106097 read=86091
I/O Timings: read=81423.789
-> Parallel Hash Left Join (cost=19279.48..200372.93 rows=406264 width=179) (actual time=51.363..28130.849 rows=322332 loops=3)
Hash Cond: (viagem0_.veiculo_id = veiculo1_.id)
Buffers: shared hit=103367 read=86091
I/O Timings: read=81423.789
-> Parallel Seq Scan on viagem viagem0_ (cost=0.00..180026.97 rows=406264 width=171) (actual time=0.118..27775.297 rows=322332 loops=3)
Filter: ((status <> 'C'::bpchar) AND (status <> 'N'::bpchar))
Rows Removed by Filter: 122711
Buffers: shared hit=85590 read=86091
I/O Timings: read=81423.789
-> Parallel Hash (cost=18444.77..18444.77 rows=66777 width=16) (actual time=50.185..50.187 rows=53422 loops=3)
Buckets: 262144 Batches: 1 Memory Usage: 10848kB
Buffers: shared hit=17777
-> Parallel Seq Scan on automovel veiculo1_ (cost=0.00..18444.77 rows=66777 width=16) (actual time=0.033..28.806 rows=53422 loops=3)
Buffers: shared hit=17777
-> Hash (cost=2592.26..2592.26 rows=168226 width=16) (actual time=57.307..57.307 rows=168231 loops=3)
Buckets: 262144 Batches: 1 Memory Usage: 9934kB
Buffers: shared hit=2730
-> Seq Scan on viagem_automovel composicoe2_ (cost=0.00..2592.26 rows=168226 width=16) (actual time=0.035..21.807 rows=168231 loops=3)
Buffers: shared hit=2730
-> Parallel Hash (cost=18444.77..18444.77 rows=66777 width=16) (actual time=127.303..127.304 rows=53422 loops=3)
Buckets: 262144 Batches: 1 Memory Usage: 10848kB
Buffers: shared hit=17777
-> Parallel Seq Scan on automovel composicao3_ (cost=0.00..18444.77 rows=66777 width=16) (actual time=0.082..96.533 rows=53422 loops=3)
Buffers: shared hit=17777
-> Parallel Hash (cost=12887.81..12887.81 rows=560981 width=16) (actual time=1967.507..1967.507 rows=447826 loops=3)
Buckets: 2097152 Batches: 1 Memory Usage: 79456kB
Buffers: shared hit=1545 read=5733
I/O Timings: read=5086.836
-> Parallel Seq Scan on viagem_motorista motoristas4_ (cost=0.00..12887.81 rows=560981 width=16) (actual time=1.735..1757.868 rows=447826 loops=3)
Buffers: shared hit=1545 read=5733
I/O Timings: read=5086.836
-> Parallel Hash (cost=5267.10..5267.10 rows=77710 width=16) (actual time=53.544..53.544 rows=62030 loops=3)
Buckets: 262144 Batches: 1 Memory Usage: 10816kB
Buffers: shared hit=4490
-> Parallel Seq Scan on motorista motorista5_ (cost=0.00..5267.10 rows=77710 width=16) (actual time=0.096..31.112 rows=62030 loops=3)
Buffers: shared hit=4490
-> Parallel Hash (cost=4303.11..4303.11 rows=138511 width=44) (actual time=1681.787..1681.788 rows=78485 loops=3)
Buckets: 262144 Batches: 1 Memory Usage: 20704kB
Buffers: shared hit=892 read=2026
I/O Timings: read=176.863
-> Parallel Seq Scan on pessoa pessoa6_ (cost=0.00..4303.11 rows=138511 width=44) (actual time=1099.692..1175.619 rows=78485 loops=3)
Buffers: shared hit=892 read=2026
I/O Timings: read=176.863
-> Hash (cost=5726.42..5726.42 rows=221042 width=49) (actual time=121.324..121.324 rows=221000 loops=1)
Buckets: 262144 Batches: 1 Memory Usage: 19628kB
Buffers: shared hit=3516
-> Seq Scan on pontoviagem pontoviage8_ (cost=0.00..5726.42 rows=221042 width=49) (actual time=0.042..61.813 rows=221000 loops=1)
Buffers: shared hit=3516
-> Hash (cost=39.99..39.99 rows=1799 width=28) (actual time=0.795..0.795 rows=1799 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 124kB
Buffers: shared hit=22
-> Seq Scan on localidade localidade10_ (cost=0.00..39.99 rows=1799 width=28) (actual time=0.026..0.564 rows=1799 loops=1)
Buffers: shared hit=22
-> Hash (cost=28.52..28.52 rows=752 width=32) (actual time=0.599..0.599 rows=752 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 56kB
Buffers: shared hit=21
-> Seq Scan on empresa empresa11_ (cost=0.00..28.52 rows=752 width=32) (actual time=0.058..0.480 rows=752 loops=1)
Buffers: shared hit=21
计划时间：12.660毫秒 JIT： 功能：276 选项：内联true，最优化true，表达式true，变形true 时间：生成50.704 ms，内联171.346 ms，优化4109.569 ms，发射2446.882 ms，总计6778.500 ms 执行时间：68332.475 ms （136行）