explain.depesz.com

PostgreSQL's explain analyze made readable

Result: B6JX : Optimization for: plan #mCvo

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.004 5,919.418 ↑ 1.0 1 1

Limit (cost=1,619,246.24..1,619,246.25 rows=1 width=8) (actual time=5,919.415..5,919.418 rows=1 loops=1)

2. 11.555 5,919.414 ↑ 1.0 1 1

Aggregate (cost=1,619,246.24..1,619,246.25 rows=1 width=8) (actual time=5,919.414..5,919.414 rows=1 loops=1)

3. 0.000 5,907.859 ↓ 15.2 181,640 1

Nested Loop (cost=1,523,218.44..1,619,216.44 rows=11,920 width=0) (actual time=4,300.931..5,907.859 rows=181,640 loops=1)

4. 701.673 5,558.054 ↓ 15.1 181,659 1

GroupAggregate (cost=1,523,218.02..1,560,428.70 rows=12,017 width=4) (actual time=4,300.908..5,558.054 rows=181,659 loops=1)

  • Group Key: pos_transactions.member_id
  • Filter: (sum(pos_transactions.amount) >= '0'::numeric)
5. 2,314.910 4,856.381 ↑ 1.0 4,870,681 1

Sort (cost=1,523,218.02..1,535,441.33 rows=4,889,325 width=10) (actual time=4,300.889..4,856.381 rows=4,870,681 loops=1)

  • Sort Key: pos_transactions.member_id
  • Sort Method: external merge Disk: 97560kB
6. 2,541.471 2,541.471 ↑ 1.0 4,870,681 1

Index Scan using pos_transactions_store_id_index on pos_transactions (cost=0.44..812,866.58 rows=4,889,325 width=10) (actual time=0.596..2,541.471 rows=4,870,681 loops=1)

  • Index Cond: ((store_id)::text = ANY ('{10125,10126,10127,10128,10129,10130,10131,10132,10133,10134,10135,10136,10137,10138,10139,10140,10141,10142,10143,10144,10145,10146,10147,10148,10149,10150,10151,10152,10153,10154,10155,10156,10157,10158,10159,10160,10161,10162,10163,10164,10165,10166,10167,10168,10169,10170,10171,10172,10173,10174,10175,10176,10177,10178,10179,10180,10181,10182,10183,10184,10185,10186,10187,10188,10189,10190,10191,10192,10193,10194,10195,10196,10197,10198,10199,10200,10201,10202,10203,10204,10205,10206,10207,10208,10209,10210,10211,10212,10213,10214,10215,10216,10217,10218,10219,10220,10221,10222,10223,10224,10225,10226,10227,10228,10229,10230,10231,10232,10233,10234,10235,10236,10237,10238,10239,10240,10241,10242,10243,10244,10245,10246,10247,10248,10249,10250,10251,10252,10253,10254,10255,10256,10257,10258,10259,10260,10261,10262,10263,10264,10265,10266,10267,10268,10269,10270,10271,10272,10273,10274,10275,10276,10277,10278,10279,10280,10281,10282,10283,10284,10285,10286,10287,10288,10289,10290,10291,10292,10293,10294,10295,10296,10297,10298,10299,10300,10301,10302,10303,10304,10305,10306,10307,10308,10309,10310,10311,10312,10313,10314,10315,10316,10317,10318,10319,10320,10321,10322,10323,10324,10325,10326,10327,10328,10329,10330,10331,10332,10333,10334,10335,10336,10337,10338,10339,10340,10341,10342,10343,10350,11184,11195,11219,11220,11221,11222,11223,11225,11226,11228,11257,11258,11392,11393,11394,11409,11410,11432,11433,11434,11435,11443,11452,11453,11456,11464,11480,11484,11691,11698,11721,11728,11731,11739,11764,11765,11793,11797,11803,11808,11817,11820,11834,11839,11871,11878,11891,11895,11925,11936,11937,11944,11976,11977,11998,12000,12023,12025,12033,12046,12052,12056,12062,12069,12077,12084,12086,12087,12324,12344,12355,12364,12386,12390,12396,12403,12418,12458,12459,12460,12461,12529,12550,12551,12555,12591,12595,12607,12609,12650,12651,12662,12663,12680,12690,12709,12711,12743,12744,12750,12751,12754,12756,12758,12770,12760,12765,12767,12781,12785,12802,12804,12814,12815}'::text[]))
  • Filter: (member_id IS NOT NULL)
7. 363.318 363.318 ↑ 1.0 1 181,659

Index Scan using profiles_member_id_index on profiles (cost=0.42..4.88 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=181,659)

  • Index Cond: (member_id = pos_transactions.member_id)
  • Filter: (community_id = 4)
Planning time : 1.867 ms
Execution time : 5,934.819 ms