explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mteh

Settings
# exclusive inclusive rows x rows loops node
1. 0.332 19,934.805 ↓ 4.9 127 1

Sort (cost=3,483,281.33..3,483,281.40 rows=26 width=206) (actual time=19,934.787..19,934.805 rows=127 loops=1)

  • Sort Key: ev.value, ev.name, f.account_id, (CASE ad_column_identifier(''::character varying, (''::character varying), 'en_US'::character varying) WHEN '**'::text THEN ''::character varying ELSE ad_column_identifier(''::character varying, (''::character varying), 'en_US'::character varying) END)
  • Sort Method: quicksort Memory: 43kB
2. 148.699 19,934.473 ↓ 4.9 127 1

Nested Loop (cost=3,482,881.74..3,483,280.72 rows=26 width=206) (actual time=19,785.785..19,934.473 rows=127 loops=1)

3. 8,065.547 19,784.377 ↓ 2.4 127 1

HashAggregate (cost=3,482,881.46..3,482,882.37 rows=52 width=52) (actual time=19,784.216..19,784.377 rows=127 loops=1)

  • Filter: ((sum(CASE WHEN ((f.dateacct < '2018-10-01 00:00:00'::timestamp without time zone) OR ((f.dateacct = '2018-10-01 00:00:00'::timestamp without time zone) AND ((f.factaccttype)::text = 'O'::text))) THEN (f.amtacctdr - f.amtacctcr) ELSE 0::numeric END) <> 0::numeric) OR (sum(CASE WHEN (((f.dateacct >= '2018-10-01 00:00:00'::timestamp without time zone) AND ((f.factaccttype)::text <> ALL ('{O,R,C}'::text[]))) OR ((f.dateacct = '2018-10-01 00:00:00'::timestamp without time zone) AND NULL::boolean)) THEN f.amtacctcr ELSE 0::numeric END) <> 0::numeric) OR (sum(CASE WHEN (((f.dateacct >= '2018-10-01 00:00:00'::timestamp without time zone) AND ((f.factaccttype)::text <> ALL ('{O,R,C}'::text[]))) OR ((f.dateacct = '2018-10-01 00:00:00'::timestamp without time zone) AND NULL::boolean)) THEN f.amtacctdr ELSE 0::numeric END) <> 0::numeric))
4. 10,780.762 11,718.830 ↓ 1.3 5,287,141 1

Bitmap Heap Scan on fact_acct f (cost=201,720.32..3,178,816.17 rows=4,194,004 width=52) (actual time=945.787..11,718.830 rows=5,287,141 loops=1)

  • Recheck Cond: (dateacct < '2018-11-01 00:00:00'::timestamp without time zone)
  • Rows Removed by Index Recheck: 438537
  • Filter: (((ad_client_id)::text = ANY ('{0,E8CB1F6B0B244CA1A8B461979EA1D539}'::text[])) AND ((c_acctschema_id)::text = '0DCC39FCD8DC47159ED82C3F74ECA5CD'::text) AND (isactive = 'Y'::bpchar) AND ((ad_org_id)::text = ANY ('{F582CF8DBD3742368C199A65A237B8F4,F4C9677DCC114845A3FF587D466D9E7D,F3B23FC042CC4F29868786E3B7537C7B,E1B3C6BCD75341C7BD474AFFEB442302,E1B070E26BDE4FC7A2073B67B8B92FFE,D9494166296D46C991205D10509093B8,CCA773813C5E4265BC247324ED76C844,BAB56EA4A5A942EEAF0BA9CAFF34F74D,B36E9058EE514C709B537A354C91C3BD,AD8A3C99638D4EE49170F0EB5BCEB05F,8C2067090AC6494289E7683EA3C68A06,816AEB46E1594978AC707D5372F5B25A,7DAB23B2A5064CBFAB016596E843E038,763A2587F23B4709A3F41FA0662F6BD4,6EAACA1F8ED8495DB8D4C2F872728B92,680FA1C76ABD478DA0F084218F586180,6683194C80DD411D9BADCA606FA55F88,6444775637C448538259BCB0BE09CC96,5BECB36DD8C7496B9CEB62F8C3564CA7,560EC6383D64405A92D388FD68A59878,3FD0BEE64018490B823B15FE495DE82D,39C13F6DDCCB4092A05357C3A8CC1436,3433EC959DC149FC87926853661EA9EA,31BA876EA1214AD9AE685FE01375881D,15E38FD0F10C4102BCC80D1132B4EC06,14ACCD687FA8480F9999ED50B2BD9E65,142C67F408344EFCB29E4E797F97E3FC,142A633EDBFB4FDC9E515228D37AEAC5,0BB733D8961B4D329EBFC585B8A1E19A,0B70A6C91ABE454485D1BA97FB9BA93A,004EC793539645E9BFB015514F96E650}'::text[])) AND ((ad_org_id)::text = ANY ('{31BA876EA1214AD9AE685FE01375881D,B36E9058EE514C709B537A354C91C3BD,8A65213769B941B2ADF9DE2BBFC12C8F,3433EC959DC149FC87926853661EA9EA,3FD0BEE64018490B823B15FE495DE82D,004EC793539645E9BFB015514F96E650,015463B9728E4C2A8D551EC42D3A81FB,5BECB36DD8C7496B9CEB62F8C3564CA7,560EC6383D64405A92D388FD68A59878,C6F497D981C943D09A367AA9426496C6,6EAACA1F8ED8495DB8D4C2F872728B92,F3B23FC042CC4F29868786E3B7537C7B,8C2067090AC6494289E7683EA3C68A06,816AEB46E1594978AC707D5372F5B25A,0BB733D8961B4D329EBFC585B8A1E19A,0B70A6C91ABE454485D1BA97FB9BA93A,DA4D50C5862C42738AC3F9A1631FF4FD,142A633EDBFB4FDC9E515228D37AEAC5,763A2587F23B4709A3F41FA0662F6BD4,CDDCA9B5E08C455F9EBC5BD2F203FA3B,3CFBC50EB81747CAB8C1F0BFD30C92E3,142C67F408344EFCB29E4E797F97E3FC,BA960085040B4F4A98A692FC0A3B48C2,E1B070E26BDE4FC7A2073B67B8B92FFE,9478FB272E1B48D2844EF338DCCBA925,680FA1C76ABD478DA0F084218F586180,17EDF8ED55BF47D69F1334FD8BDAAE0D,F582CF8DBD3742368C199A65A237B8F4,0FEA6B0CE29E41C0A266E62B1BB318BD,6444775637C448538259BCB0BE09CC96,AE540A9683B34231AE3531785A456ACE,D9494166296D46C991205D10509093B8,CCA773813C5E4265BC247324ED76C844,4E4BE778176042268AE79C80EA90022C,6802ECD342934670A6D88F7C55185D8D,15E38FD0F10C4102BCC80D1132B4EC06,30716C8BDA154DE78EFC2C5DCB7D5CEB,AD8A3C99638D4EE49170F0EB5BCEB05F,0,F4C9677DCC114845A3FF587D466D9E7D,6683194C80DD411D9BADCA606FA55F88,7DAB23B2A5064CBFAB016596E843E038,14ACCD687FA8480F9999ED50B2BD9E65,39C13F6DDCCB4092A05357C3A8CC1436,BAB56EA4A5A942EEAF0BA9CAFF34F74D,A565E147F9BE462C876B41AA00EAE7CA,E1B3C6BCD75341C7BD474AFFEB442302,D2A08659AF20457A81F4E6CBA4BF9D2F}'::text[])))
  • Rows Removed by Filter: 891395
5. 938.068 938.068 ↓ 1.4 8,531,884 1

Bitmap Index Scan on fact_acct_dateacct (cost=0.00..200,671.82 rows=6,263,384 width=0) (actual time=938.068..938.068 rows=8,531,884 loops=1)

  • Index Cond: (dateacct < '2018-11-01 00:00:00'::timestamp without time zone)
6. 1.397 1.397 ↑ 1.0 1 127

Index Scan using c_elementvalue_key on c_elementvalue ev (cost=0.29..7.39 rows=1 width=78) (actual time=0.010..0.011 rows=1 loops=127)

  • Index Cond: ((c_elementvalue_id)::text = (f.account_id)::text)
  • Filter: (((value)::text <= 'T10002'::text) AND ((elementlevel)::text = 'S'::text))