| Steps To Reproduce | we have detected that the following query takes 80 seconds to be executed.
select 1 as id from dual where exists ( select 1 from ( SELECT a.Paymentdate as dateacct from FIN_Payment a, c_periodcontrol, c_period WHERE a.ad_client_id = '9718AC9640AE46A995EA3906D8577B57' AND a.ad_org_id in ('78ECECB031B040D8B92F2A386122EB8A', 'A7C52F17532B421A85510826CC533A56', 'A8428F67B4FB4DE39858ED7961664642', '12D699E251A14BACA4AD467C0C7BF740', 'C4E6CDEDB7E846468E49AD857074E05F', 'CA2C1E01F3F8429DB53A4B38B0359584', '9B37FD5895F6423AA7057CC98BEDF8F2', '6B1E650FE6B14B6D8B1077321ACD2E33', '1E92FDE556754B20A5006347DCD774C8', 'D2281E3729FC46E6823447B86F7A93CD', '2E405F05605F4DFE962B645DAA9B1BED', '789F6A29473C40A89286644E541FE27F', '0FCC1AF0592645C787D440672831DD3F', 'BA947E63D9A14A6BBC5FEC0390211F14', 'AFBE100B46334B5689E0BF3DE184B347', 'E009ABF05825407597475082CC94F730', '8EB0E39BAB82401FA2B588C3C7E72E21', '8718431D9D354531BD8738CC6D68DDB3', 'FE508C08DE544254B2F05FC7209F3822', 'AFEC47C91EBD476AB15E57BF1F6CBC13', 'C84FC775D2314020966726D0D6F67E62', 'C3D8332AAFA541C681BAD55F88578201', 'B8EBDAC4EAA3443687CE90017308A7B0', '7C1EA8EDB5604C75940C7B7BB01924F7', '0', 'FE6382F18D3B43F18FC1AB2715A4D581', 'C34408E3763C40BCAD126A1F68254E90', '3F567EF8CABB4D999331B911585D1778', 'E26A832E0FD940F09633DB7FD061C97E', '23ADA885129149DC972870124B59A72E', '782A82AED7984D5A803C0633B7A5B3D2', '35EE4B7E0DB244CA9795E288C5665DCD', '1CC2678CAE0D4D21B87C9533E0BADD2C', 'FEA5E2E616684B86BD35ED5DDF554052', '841DB3F9C1B84DE99C75534C08B21BDE', '1C5F9E2C8AAF4476A59502F3163968E9', '7657FC7FF6B647689B1034847130ADAB', '967469128B5A4F14A0F8F7DEEA50F6BF', 'A4524702F9A9470E84CD478A7F1FA87D', '768E33F2A4544D1E8F1B79CB737F5D73', '16803346D1B441E6BDCC95C5A6262165', 'E0A04220DF9C4CC7974CAA1AB2B4E33B', 'C6600C3D9DAD4A4086B5E7DEED8F43CC', '9425B0E5552A446BA7FA51CEEF790220', 'FC5438951DB04D4B861C546E33D9873A', '53B00F0067B84C15BF25FBA1C57407FB', '51D2661E032D4BB7B1DB52188A594791', '3086199143774727BCF56CBF0FCA4548', '51F9ECC16D1C46B89A17FABF017EC48E', '9795575573F141078C085C1931938718', '202294ACE8504545BF17A1661E3B10E8', '8A5FB32697D04C66921046E5DA04144F', 'CD57526F95934A0B9DAD8531A3E11781', '34D18289081A453B99D090059264EBEF', '48830FF3B878455498AAD7DCB3DB821D', 'D60C026E4C704BDBA1ED6A47868554C0', '93362394887045C39E0804D13654EBB3', '140461D18F474F05852320F228DF1765', '67BF6D38B523421483E3E68BB61B2199', 'F0487725FAB84F4AB61F4E4750FE481E', '09D0E20020B048EC81E730F641896FC5', 'D5ED79BC46AA4C9494400EAE07A73C25', '049154C13FD044178B3DCA9854DA7097', '4BF951FCB1E347EAA021A5B1E8597EC4', 'C34FC9B26126418693723213FB9F110F') AND a.posted = 'N' AND a.processed = 'Y' AND (a.processing = 'N' OR a.processing IS NULL) and c_periodcontrol.c_period_id = c_period.c_period_id AND c_periodcontrol.docbasetype = 'ARR' AND c_periodcontrol.periodstatus = 'O' AND ad_org_getperiodcontrolallow(a.ad_org_id) = c_periodcontrol.ad_org_id AND a.Paymentdate >= c_period.StartDate AND a.Paymentdate < c_period.EndDate + 1 ) b where 1=1 );
We have created an index for the posted column, and the query takes 3 seconds:
CREATE INDEX fin_payment_posted
ON fin_payment
USING btree
(posted COLLATE pg_catalog."default"); |