explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gbMU

Settings
# exclusive inclusive rows x rows loops node
1. 0.317 19,565.176 ↓ 4.6 129 1

Sort (cost=3,472,881.86..3,472,881.93 rows=28 width=206) (actual time=19,565.159..19,565.176 rows=129 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: 44kB
2. 148.791 19,564.859 ↓ 4.6 129 1

Nested Loop (cost=3,472,468.49..3,472,881.18 rows=28 width=206) (actual time=19,416.132..19,564.859 rows=129 loops=1)

3. 8,087.626 19,414.778 ↓ 2.4 129 1

HashAggregate (cost=3,472,468.21..3,472,469.15 rows=54 width=52) (actual time=19,414.626..19,414.778 rows=129 loops=1)

  • Filter: ((sum(CASE WHEN ((f.dateacct < '2018-10-02 00:00:00'::timestamp without time zone) OR ((f.dateacct = '2018-10-02 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-02 00:00:00'::timestamp without time zone) AND ((f.factaccttype)::text <> ALL ('{O,R,C}'::text[]))) OR ((f.dateacct = '2018-10-02 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-02 00:00:00'::timestamp without time zone) AND ((f.factaccttype)::text <> ALL ('{O,R,C}'::text[]))) OR ((f.dateacct = '2018-10-02 00:00:00'::timestamp without time zone) AND NULL::boolean)) THEN f.amtacctdr ELSE 0::numeric END) <> 0::numeric))
4. 10,640.324 11,327.152 ↓ 1.3 5,340,039 1

Bitmap Heap Scan on fact_acct f (cost=199,817.90..3,169,928.75 rows=4,172,958 width=52) (actual time=693.964..11,327.152 rows=5,340,039 loops=1)

  • Recheck Cond: (dateacct < '2018-11-02 00:00:00'::timestamp without time zone)
  • Rows Removed by Index Recheck: 375147
  • 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: 900268
5. 686.828 686.828 ↓ 1.0 6,240,307 1

Bitmap Index Scan on fact_acct_dateacct (cost=0.00..198,774.66 rows=6,204,563 width=0) (actual time=686.828..686.828 rows=6,240,307 loops=1)

  • Index Cond: (dateacct < '2018-11-02 00:00:00'::timestamp without time zone)
6. 1.290 1.290 ↑ 1.0 1 129

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

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