explain.depesz.com

PostgreSQL's explain analyze made readable

Result: r1j6 : With Lower

Settings
# exclusive inclusive rows x rows loops node
1. 1,053.840 24,267.000 ↑ 8,012.5 998 1

Merge Join (cost=3,254,483.75..4,689,618.53 rows=7,996,496 width=12) (actual time=16,743.896..24,267.000 rows=998 loops=1)

  • Merge Cond: (document.c_documentid = "tblDocument".c_documentid)
2.          

CTE innerquery

3. 7.034 13,303.381 ↑ 1,222.8 366 1

HashAggregate (cost=2,245,524.86..2,250,000.32 rows=447,546 width=8) (actual time=13,297.599..13,303.381 rows=366 loops=1)

  • Group Key: "docProperty_1".c_docfamilyid
4. 2,934.314 13,296.347 ↑ 15,994.7 993 1

Hash Join (cost=842,858.85..2,205,817.92 rows=15,882,776 width=8) (actual time=10,363.151..13,296.347 rows=993 loops=1)

  • Hash Cond: ("tblDocument_1".c_documentid = "docProperty_1".c_documentid)
5. 0.119 9.503 ↑ 15,994.7 993 1

Append (cost=0.56..1,118,665.33 rows=15,882,776 width=8) (actual time=1.099..9.503 rows=993 loops=1)

6. 9.384 9.384 ↑ 15,994.7 993 1

Index Scan using t_e20so1_document_datastore_default_lower_idx on t_e20so1_document_datastore_default "tblDocument_1" (cost=0.56..1,039,251.46 rows=15,882,776 width=8) (actual time=1.098..9.384 rows=993 loops=1)

  • Index Cond: (lower((c_docdisplayid)::text) = ANY ('{1998_en,1999_en,2003_en,2004_en,2006_en,2008_en,2010_en,2011_en,2012_en,2013_en,2014_en,"658863 satok",allied_5085,allied_5086,allied_5087,allied_6915,avx_0020190,avx_0020192,avx_0020193,avx_0020194,avx_0020195,avx_0020196,avx_0020197,avx_0020198,avx_0020199,avx_0020200,avx_0020201,avx_0020202,avx_0020203,avx_0020204,avx_0020206,avx_0020207,avx_0020208,avx_0020209,avx_0020210,avx_0020211,avx_0020212,avx_0020213,avx_0020214,avx_0020215,avx_0020216,avx_0020217,avx_0020218,avx_0020219,avx_0020220,avx_0020221,avx_0020222,avx_0020224,avx_0020225,avx_0020226,avx_0020227,avx_0020228,avx_0020229,avx_0020230,avx_0020231,avx_0020234,avx_0020235,avx_0020238,avx_0020239,avx_0020242,avx_0020243,avx_0020247,avx_0020248,avx_0020249,avx_0020250,avx_0020251,avx_0020252,avx_0020253,avx_0020254,avx_0020255,avx_0020256,avx_0020257,avx_0020258,avx_0020259,avx_0020260,avx_0020261,avx_0020262,avx_0020264,avx_0020265,avx_0020266,avx_0020267,avx_0020268,avx_0020269,avx_0020270,avx_0020271,avx_0020272,avx_0020273,avx_0020274,avx_0020275,avx_0020276,avx_0020277,avx_0020279,avx_0020280,avx_0020281,avx_0020282,avx_0020283,avx_0020284,avx_0020285,avx_0020286,avx_0020287,avx_0020288,avx_0020289,avx_0020290,avx_0020291,avx_0020292,avx_0020293,avx_0020294,avx_0020295,avx_0020296,avx_0020297,avx_0020298,avx_0020300,avx_0020301,avx_0020303,avx_0020304,avx_0020307,avx_0020308,avx_0020309,avx_0020310,avx_0020311,avx_0020314,avx_0020320,avx_0020322,avx_0020324,avx_0020329,avx_0020330,avx_0020333,avx_0020336,avx_0020337,avx_0020338,avx_0020339,avx_0020343,avx_0020360,avx_0020363,avx_0020371,avx_0020372,avx_0020373,avx_0020374,avx_0020375,avx_0020376,avx_0020377,avx_0020378,avx_0020409,avx_0020410,avx_0020411,avx_0020412,avx_0020413,avx_0020414,avx_0020415,avx_0020416,avx_0020417,avx_0020418,avx_0020419,avx_0020420,avx_0020421,avx_0020422,avx_0020423,avx_0020424,avx_0020426,avx_0020427,avx_0020428,avx_0020429,avx_0020430,avx_0020431,avx_0020432,avx_0020433,avx_0020434,avx_0020435,avx_0020436,avx_0020437,avx_0020438,avx_0020439,avx_0020440,avx_0020441,avx_0020442,avx_0020443,avx_0020444,avx_0020445,avx_0020446,avx_0020447,avx_0020448,avx_0020449,avx_0020450,avx_0020451,avx_0020452,avx_0020453,avx_0020454,avx_0020455,avx_0020456,avx_0020457,avx_0020458,avx_0020459,avx_0020460,avx_0020468,avx_0020469,avx_0020476,avx_0020477,avx_0020478,avx_0020479,avx_0020480,avx_0020481,avx_0020482,avx_0020483,avx_0020484,avx_0020485,avx_0020486,avx_0020487,avx_0020491,avx_0020492,avx_0020493,avx_0020494,avx_0020495,avx_0020496,avx_0020497,avx_0020498,avx_0020499,avx_0020500,avx_0020501,avx_0020502,avx_0020503,avx_0020504,avx_0020505,avx_0020506,avx_0020507,avx_0020508,avx_0020509,avx_0020510,avx_0020511,avx_0020512,avx_0020513,avx_0020514,avx_0020515,avx_0020516,avx_0020517,avx_0020518,avx_0020519,avx_0020520,avx_0020521,avx_0020522,avx_0020523,avx_0020524,avx_0020525,avx_0020526,avx_0020527,avx_0020528,avx_0020529,avx_0020530,avx_0020531,avx_0020532,avx_0020533,avx_0020534,avx_0020535,avx_0020551,avx_0020574,avx_0020575,avx_0020582,avx_0020584,avx_0020615,avx_0020616,avx_0020617,avx_0020618,avx_0020619,avx_0020620,avx_0020621,avx_0020622,avx_0020623,avx_0020624,avx_0020625,avx_0020626,avx_0020627,avx_0020628,avx_0020632,avx_0020633,avx_0020634,avx_0020635,avx_0020636,avx_0020637,avx_0020638,avx_0020641,avx_0020647,avx_0020648,avx_0020651,avx_0020654,avx_0020656,avx_0020658,avx_0020659,avx_0020663,avx_0020671,avx_0020675,avx_0020680,avx_0020681,avx_0020682,avx_0020686,avx_0020707,avx_0020708,avx_0020709,avx_0020710,avx_0020714,avx_0020715,avx_0020716,avx_0020717,avx_0020718,avx_0020719,avx_0020720,avx_0020724,avx_0020725,avx_0020726,avx_0020727,avx_0020728,avx_0020729,avx_0020730,avx_0020731,avx_0020732,avx_0020733,avx_0020734,avx_0020737,avx_0020738,avx_0020739,avx_0020740,avx_0020741,avx_0020742,avx_0020743,avx_0020744,avx_0020745,avx_0020746,avx_0020748,avx_0020749,avx_0020750,avx_0020751,avx_0020752,avx_0020753,avx_0020754,avx_0020756,avx_0020757,avx_0020758,avx_0020759,avx_0020760,avx_0020761,avx_0020762,avx_0020763,avx_0020764,avx_0020765,avx_0020766,avx_0020770,avx_0020771,avx_0020772,avx_0020773,avx_0020774,avx_0020775,avx_0020776,avx_0020777,avx_0020778,avx_0020779,avx_0020780,avx_0020781,avx_0020782,avx_0020783,avx_0020784,avx_0020785,avx_0020786,avx_0020788,avx_0020798,avx_0020799,avx_0020800,avx_0020801,avx_0020802,avx_0020803,avx_0020804,avx_0020805,avx_0020806,avx_0020807,avx_0020808,avx_0020809,avx_0020810,avx_0020811,avx_0020812,avx_0020813,avx_0020814,avx_0020815,avx_0020816,avx_0020817,avx_0020818,avx_0020819,avx_0020820,avx_0020822,avx_0020832,avx_0020833,avx_0020834,avx_0020835,avx_0020836,avx_0020837,avx_0020838,avx_0020839,avx_0020840,avx_0020841,avx_0020842,avx_0020843,avx_0020844,avx_0020845,avx_0020846,avx_0020847,avx_0020848,avx_0020849,avx_0020850,avx_0020851,avx_0020852,avx_0020853,avx_0020854,avx_0020858,avx_0020859,avx_0020860,avx_0020861,avx_0020862,avx_0020863,avx_0020864,avx_0020865,avx_0020866,avx_0020867,avx_0020868,avx_0020869,avx_0020870,avx_0020871,avx_0020872,avx_0020873,avx_0020874,avx_0020875,avx_0020876,avx_0020877,avx_0020878,avx_0020879,avx_0020880,avx_0020881,avx_0020882,avx_0020883,avx_0020884,avx_0020885,avx_0020886,avx_0020887,avx_0020888,avx_0020889,avx_0020890,avx_0020891,avx_0020892,avx_0020893,avx_0020894,avx_0020895,avx_0020896,avx_0020897,avx_0020898,avx_0020899,avx_0020900,avx_0020901,avx_0020902,avx_0020903,avx_0020904,avx_0020905,avx_0020906,avx_0020907,avx_0020908,avx_0020909,avx_0020910,avx_0020911,avx_0020912,avx_0020913,avx_0020914,avx_0020915,avx_0020916,avx_0020917,avx_0020918,avx_0020919,avx_0020920,avx_0020921,avx_0020922,avx_0020923,avx_0020924,avx_0020925,avx_0020926,avx_0020927,avx_0020928,avx_0020929,avx_0020930,avx_0020931,avx_0020932,avx_0020933,avx_0020934,avx_0020935,avx_0020936,avx_0020937,avx_0020938,avx_0020939,avx_0020940,avx_0020941,avx_0020942,avx_0020943,avx_0020944,avx_0020945,avx_0020946,avx_0020947,avx_0020948,avx_0020949,avx_0020950,avx_0020951,avx_0020952,avx_0020953,avx_0020954,avx_0020955,avx_0020957,avx_0020958,avx_0020959,avx_0020960,avx_0020961,avx_0020962,avx_0020963,avx_0020964,avx_0020965,avx_0020966,avx_0020967,avx_0020968,avx_0020969,avx_0020970,avx_0020971,avx_0020972,avx_0020973,avx_0020974,avx_0020976,avx_0020977,avx_0020979,avx_0020980,avx_0020981,avx_0020982,avx_0020983,avx_0020984,avx_0020985,avx_0020986,avx_0020987,avx_0020988,avx_0020989,avx_0020990,avx_0020991,avx_0020992,avx_0020993,avx_0020994,avx_0020995,avx_0020996,avx_0020998,avx_0020999,avx_0021000,avx_0021001,avx_0021002,avx_0021003,avx_0021004,avx_0021005,avx_0021006,avx_0021007,avx_0021008,avx_0021009,avx_0021010,avx_0021011,avx_0021012,avx_0021013,avx_0021014,avx_0021015,avx_0021016,avx_0021017,avx_0021018,avx_0021019,avx_0021020,avx_0021021,avx_0021022,avx_0021023,avx_0021024,avx_0021026,avx_0021038,avx_0021039,avx_0021051,avx_0021052,avx_0021053,avx_0021054,avx_0021055,avx_0021056,avx_0021057,avx_0021058,avx_0021059,avx_0021060,avx_0021061,avx_0021062,avx_0021063,avx_0021064,avx_0021065,avx_0021066,avx_0021067,avx_0021068,avx_0021069,avx_0021070,avx_0021071,avx_0021072,avx_0021073,avx_0021074,avx_0021075,avx_0021076,avx_0021077,avx_0021078,avx_0021079,avx_0021080,avx_0021081,avx_0021082,avx_0021083,avx_0021084,avx_0021085,avx_0021086,avx_0021087,avx_0021088,avx_0021089,avx_0021090,avx_0021093,avx_0021094,avx_0021095,avx_0021100,avx_0021101,avx_0021102,avx_0021103,avx_0021108,avx_0021172,avx_0021173,avx_0021174,avx_0021175,avx_0021176,avx_0021177,avx_0021178,avx_0021179,avx_0021180,avx_0021181,avx_0021182,avx_0021183,avx_0021184,avx_0021185,avx_0021186,avx_0021187,avx_0021188,avx_0021189,avx_0021190,avx_0021191,avx_0021192,avx_0021193,avx_0021194,avx_0021195,avx_0021198,avx_0021199,avx_0021200,avx_0021201,avx_0021202,avx_0021203,avx_0021207,avx_0021208,avx_0021209,avx_0021210,avx_0021211,avx_0021212,avx_0021213,avx_0021214,avx_0021215,avx_0021216,avx_0021217,avx_0021218,avx_0021219,avx_0021220,avx_0021221,avx_0021222,avx_0021223,avx_0021224,avx_0021225,avx_0021226,avx_0021227,avx_0021228,avx_0021229,avx_0021230,avx_0021231,avx_0021232,avx_0021233,avx_0021234,avx_0021235,avx_0021236,avx_0021237,avx_0021238,avx_0021239,avx_0021240,avx_0021241,avx_0021242,avx_0021243,avx_0021244,avx_0021245,avx_0021246,avx_0021247,avx_0021248,avx_0021249,avx_0021250,avx_0021252,avx_0021253,avx_0021254,avx_0021257,avx_0021258,avx_0021259,avx_0021260,avx_0021261,avx_0021262,avx_0021265,avx_0021266,avx_0021267,avx_0021268,avx_0021269,avx_0021270,avx_0021271,avx_0021272,avx_0021273,avx_0021274,avx_0021275,avx_0021276,avx_0021277,avx_0021278,avx_0021279,avx_0021280,avx_0021281,avx_0021282,avx_0021283,avx_0021284,avx_0021285,avx_0021286,avx_0021287,avx_0021288,avx_0021289,avx_0021290,avx_0021291,avx_0021292,avx_0021293,avx_0021294,avx_0021295,avx_0021296,avx_0021297,avx_0021298,avx_0021299,avx_0021300,avx_0021301,avx_0021302,avx_0021303,avx_0021304,avx_0021305,avx_0021306,avx_0021307,avx_0021308,avx_0021309,avx_0021310,avx_0021311,avx_0021312,avx_0021313,avx_0021314,avx_0021315,avx_0021316,avx_0021317,avx_0021318,avx_0021319,avx_0021320,avx_0021321,avx_0021322,avx_0021323,avx_0021324,avx_0021325,avx_0021326,avx_0021327,avx_0021328,avx_0021329,avx_0021330,avx_0021331,avx_0021332,avx_0021333,avx_0021334,avx_0021335,avx_0021336,avx_0021337,avx_0021338,avx_0021339,avx_0021340,avx_0021341,avx_0021342,avx_0021344,avx_0021345,avx_0021347,avx_0021348,avx_0021350,avx_0021351,avx_0021352,avx_0021353,avx_0021354,avx_0021355,avx_0021356,avx_0021357,avx_0021358,avx_0021359,avx_0021360,avx_0021361,avx_0021362,avx_0021363,avx_0021364,avx_0021365,avx_0021366,avx_0021367,avx_0021368,avx_0021369,avx_0021370,avx_0021371,avx_0021372,avx_0021373,avx_0021374,avx_0021375,avx_0021376,avx_0021377,avx_0021378,avx_0021379,avx_0021380,avx_0021381,avx_0021382,avx_0021383,avx_0021384,avx_0021385,avx_0021386,avx_0021387,avx_0021388,avx_0021389,avx_0021390,avx_0021391,avx_0021392,avx_0021393,avx_0021394,avx_0021395,avx_0021396,avx_0021397,avx_0021398,avx_0021399,avx_0021400,avx_0021401,avx_0021402,avx_0021403,avx_0021404,avx_0021405,avx_0021406,avx_0021407,avx_0021411,avx_0021472,avx_0021475,avx_0021536,avx_0021537,avx_0021538,avx_0021541,avx_0021545,avx_0021546,avx_0021548,avx_0021549,avx_0021550,avx_0021551,avx_0021552,avx_0021553,avx_0021554,avx_0021555,avx_0021556,avx_0021557,avx_0021558,avx_0021566,avx_0021567,avx_0021568,avx_0021576,avx_0021577,avx_0021578,avx_0021581,avx_0021582,avx_0021583,avx_0021584,avx_0021585,avx_0021586,avx_0021587,avx_0021588,avx_0021589,avx_0021590,avx_0021591,avx_0021592,avx_0021593,avx_0021594,avx_0021599,avx_0021605,avx_0021608,avx_0021611,avx_0021614,avx_0021617,avx_0021618,avx_0021620,avx_0021622,avx_0021623,avx_0021624,avx_0021632,avx_0021637,avx_0021641,avx_0021643,avx_0021647,avx_0021650,avx_0021653,avx_0021657,avx_0021664,avx_0021668,avx_0021671,avx_0021680,avx_0021681,avx_0021683,avx_0021684,avx_0021685,avx_0021693,avx_0021694,avx_0021695,avx_0021696,avx_0021697,avx_0021698,avx_0021699,avx_0021700,avx_0021701,avx_0021702,avx_0021703,avx_0021704,avx_0021705,avx_0021706,avx_0021707,avx_0021709,avx_0021710,avx_0021711,avx_0021712,avx_0021713,avx_0021714,avx_0021715,avx_0021716,avx_0021721,avx_0021722,avx_0021723,avx_0021724,avx_0021725,avx_0021726,avx_0021727,avx_0021728,avx_0021729,avx_0021730,avx_0021731,avx_0021732,avx_0021733,avx_0021734,avx_0021735,avx_0021736,avx_0021737,avx_0021738,avx_0021739,avx_0021740,avx_0021741,avx_0021747,avx_0021748,avx_0021749,avx_0021750,avx_0021751,avx_0021752,avx_0021753,avx_0021754,avx_0021755,avx_0021756,avx_0021757,avx_0021759,avx_0021760,avx_0021761,avx_0021762,avx_0021763,avx_0021764,avx_0021765,avx_0021766,avx_0021768,avx_0021769,avx_0021770,avx_0021771,avx_0021772,avx_0021773,avx_0021774,avx_0021775,avx_0021776,avx_0021777,avx_0021778,avx_0021779,avx_0021780,avx_0021781,avx_0021782,avx_0021783,avx_0021784,avx_0021785}'::text[]))
  • Filter: (c_datastoresid = 11)
7. 3,679.355 10,352.530 ↑ 1.0 15,991,301 1

Hash (cost=563,343.24..563,343.24 rows=16,079,924 width=16) (actual time=10,352.530..10,352.530 rows=15,991,301 loops=1)

  • Buckets: 1,048,576 Batches: 32 Memory Usage: 31,661kB
8. 6,673.175 6,673.175 ↑ 1.0 15,991,301 1

Seq Scan on t_e20so1_document_property "docProperty_1" (cost=0.00..563,343.24 rows=16,079,924 width=16) (actual time=0.031..6,673.175 rows=15,991,301 loops=1)

9. 1,486.569 4,437.024 ↑ 1.0 15,983,410 1

Merge Append (cost=0.57..507,555.38 rows=15,992,993 width=8) (actual time=0.062..4,437.024 rows=15,983,410 loops=1)

  • Sort Key: document.c_documentid
10. 2,950.455 2,950.455 ↑ 1.0 15,983,410 1

Index Only Scan using t_e20so1_document_datastore_default_pkey on t_e20so1_document_datastore_default document (cost=0.56..347,625.43 rows=15,992,993 width=8) (actual time=0.061..2,950.455 rows=15,983,410 loops=1)

  • Index Cond: (c_datastoresid = 11)
  • Heap Fetches: 0
11. 0.235 18,776.136 ↑ 8,012.5 998 1

Unique (cost=1,004,482.86..1,712,159.19 rows=7,996,496 width=8) (actual time=15,043.192..18,776.136 rows=998 loops=1)

12. 1,082.152 18,775.901 ↑ 8,012.5 998 1

Merge Join (cost=1,004,482.86..1,692,167.95 rows=7,996,496 width=8) (actual time=15,043.182..18,775.901 rows=998 loops=1)

  • Merge Cond: ("tblDocument".c_documentid = "docProperty".c_documentid)
13. 1,406.590 4,387.623 ↑ 1.0 15,983,410 1

Merge Append (cost=0.57..507,555.38 rows=15,992,993 width=8) (actual time=0.010..4,387.623 rows=15,983,410 loops=1)

  • Sort Key: "tblDocument".c_documentid
14. 2,981.033 2,981.033 ↑ 1.0 15,983,410 1

Index Only Scan using t_e20so1_document_datastore_default_pkey on t_e20so1_document_datastore_default "tblDocument" (cost=0.56..347,625.43 rows=15,992,993 width=8) (actual time=0.010..2,981.033 rows=15,983,410 loops=1)

  • Index Cond: (c_datastoresid = 11)
  • Heap Fetches: 0
15. 0.150 13,306.126 ↑ 8,056.1 998 1

Materialize (cost=1,004,427.55..1,044,627.36 rows=8,039,962 width=8) (actual time=13,305.843..13,306.126 rows=998 loops=1)

16. 0.399 13,305.976 ↑ 8,056.1 998 1

Sort (cost=1,004,427.55..1,024,527.45 rows=8,039,962 width=8) (actual time=13,305.837..13,305.976 rows=998 loops=1)

  • Sort Key: "docProperty".c_documentid
  • Sort Method: quicksort Memory: 71kB
17. 0.396 13,305.577 ↑ 8,056.1 998 1

Nested Loop (cost=10,070.34..17,909.51 rows=8,039,962 width=8) (actual time=13,303.688..13,305.577 rows=998 loops=1)

18. 0.223 13,303.717 ↓ 1.8 366 1

HashAggregate (cost=10,069.78..10,071.78 rows=200 width=8) (actual time=13,303.632..13,303.717 rows=366 loops=1)

  • Group Key: innerquery.c_docfamilyid
19. 13,303.494 13,303.494 ↑ 1,222.8 366 1

CTE Scan on innerquery (cost=0.00..8,950.92 rows=447,546 width=8) (actual time=13,297.606..13,303.494 rows=366 loops=1)

20. 1.464 1.464 ↑ 12.0 3 366

Index Scan using t_e20so1_document_property_c_docfamilyid_idx on t_e20so1_document_property "docProperty" (cost=0.56..38.83 rows=36 width=16) (actual time=0.003..0.004 rows=3 loops=366)

  • Index Cond: (c_docfamilyid = innerquery.c_docfamilyid)