Pessoal, bom dia!!!!
preciso de uma ajuda pra melhorar essa query, alguem pode me ajudar?
SELECT
crcid, crcdatvct, crcdocger, crcvlr, clitel, clicobctt, clissp,
clinomraz, cliid, clicobtel
FROM
cli, crc , ctr
WHERE
(cliid = crccli) and (crcbxd = 'N') and
(((crcdocger in (select fatctrfat from fatctr where fatctrctr = ctrid))
or (crcdocger = ctrid))) and
(crcdatvct >= '2009-11-01') and
(crcdatvct <= '2009-11-20') and
(crcvlr > '0.00')
ORDER BY
crcdocger ASC, crcdatvct DESC;
Explain da query:
Sort (cost=201321897.89..201339880.49 rows=7193041 width=227) (actual
time=409485.814..409490.144 rows=1328 loops=1)
Sort Key: crc.crcdocger, crc.crcdatvct
-> Nested Loop (cost=0.00..198929195.32 rows=7193041 width=227) (actual
time=33.990..409472.934 rows=1328 loops=1)
Join Filter: ((subplan) OR ((crc.crcdocger)::text =
(ctr.ctrid)::text))
-> Nested Loop (cost=0.00..4704.73 rows=805 width=227) (actual
time=0.056..1001.017 rows=814 loops=1)
-> Seq Scan on crc (cost=0.00..76.90 rows=805 width=46)
(actual time=0.024..35.293 rows=814 loops=1)
Filter: ((crcbxd = 'N'::bpchar) AND (crcdatvct >=
'2009-11-01'::date) AND (crcdatvct <= '2009-11-20'::date) AND (crcvlr >
0.00))
-> Index Scan using cli_pkey on cli (cost=0.00..5.74 rows=1
width=185) (actual time=1.159..1.166 rows=1 loops=814)
Index Cond: (cli.cliid = crc.crccli)
-> Seq Scan on ctr (cost=0.00..1447.82 rows=17782 width=4)
(actual time=0.007..69.695 rows=17782 loops=814)
SubPlan
-> Bitmap Heap Scan on fatctr (cost=4.30..23.26 rows=7 width=4)
(actual time=0.013..0.014 rows=0 loops=14474548)
Recheck Cond: (fatctrctr = $0)
-> Bitmap Index Scan on fatctrctr (cost=0.00..4.30 rows=7
width=0) (actual time=0.006..0.006 rows=0 loops=14474548)
Index Cond: (fatctrctr = $0)
Total runtime: 409494.564 ms
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
