Hello All!
Can you please assist me with highlighting the bad area in the execution plan attached. I am kind of new to PgSQL and trying to get some help with troubleshooting a query.
Little background. DB was migrated from Oracle to AlloyDB. The SQL that used to take 2 seconds is now taking around 8 - 10secs in AlloyDB. We are trying to close this gap. Any help would be appreciated.
Regards
Siraj
[
{
"Plan": {
"Node Type": "Hash Join",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Left",
"Startup Cost": 7315.71,
"Total Cost": 16890.47,
"Plan Rows": 25401,
"Plan Width": 254,
"Actual Startup Time": 7043.408,
"Actual Total Time": 7066.073,
"Actual Rows": 480,
"Actual Loops": 1,
"Output": ["buvo.bunge_voyage_id", "xold.vessel_id", "xold.constraints", "xold.slot_from", "xold.slot_text", "xold.new_remarks_cnt", "xold.freight_covered", "CASE WHEN (xold.on_hold IS NOT NULL) THEN (xold.on_hold)::boolean ELSE false END", "xold.freight_comments", "xold.planning_comments", "xold.location_changed", "xold.cargo_changed", "xold.new_schedules_added", "xold.new_cargos_added", "xold.planned_slot_from", "xold.planned_slot_text", "CASE WHEN (xold.bunge_voyage_id IS NULL) THEN true ELSE false END", "CASE WHEN ((min(CASE WHEN ((dense_rank() OVER (?)) = 1) THEN (logistics_voyage.freight_covered)::integer ELSE NULL::integer END)) IS NOT NULL) THEN ((min(CASE WHEN ((dense_rank() OVER (?)) = 1) THEN (logistics_voyage.freight_covered)::integer ELSE NULL::integer END)))::boolean ELSE false END", "CASE WHEN (plvo.on_hold_0 IS NOT NULL) THEN (plvo.on_hold_0)::boolean ELSE false END"],
"Inner Unique": false,
"Hash Cond": "(buvo.bunge_voyage_id = xold.bunge_voyage_id)",
"Shared Hit Blocks": 8799087,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Index Scan",
"Parent Relationship": "InitPlan",
"Subplan Name": "CTE bunge2logistics",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "idx_bunge_voyage_id",
"Relation Name": "bunge_voyage",
"Schema": "voyage_own",
"Alias": "bunge_voyage",
"Startup Cost": 0.42,
"Total Cost": 656.09,
"Plan Rows": 480,
"Plan Width": 5,
"Actual Startup Time": 0.119,
"Actual Total Time": 1.245,
"Actual Rows": 480,
"Actual Loops": 1,
"Output": ["bunge_voyage.logistics_voyage_id"],
"Index Cond": "(bunge_voyage.bunge_voyage_id = ANY ('{226167,226168,226171,226172,226173,226174,226175,226176,226178,226186,226188,226197,226213,226219,226231,226170,209930,209399,208856,208908,208285,201878,206722,209815,206989,208808,205769,207558,208186,208527,208851,206395,203040,203258,205333,207477,207780,202501,202765,206000,208892,226257,209124,209125,209161,208821,205687,208894,205786,198204,208495,208652,208939,209805,207621,207367,204035,207464,206319,204482,204588,207938,209298,209162,208407,202596,209817,204066,208665,208946,209057,205780,207559,207846,207319,205559,206344,206856,207033,209465,210103,210356,210296,202764,206886,209058,206858,208496,208497,208498,210480,210247,206855,204946,205633,205638,206774,207772,209720,226269,206436,209721,210067,208848,208796,203868,207816,209344,205781,210203,203039,203898,208388,208493,208576,208890,210248,210923,210768,202595,210675,208857,204067,207079,205583,209463,209464,209479,209480,209481,210680,210450,208368,208367,209011,210627,210628,207994,210172,204015,207622,211177,208542,209565,208168,203785,208681,209482,205675,206394,207736,210948,208940,209488,210278,203259,205521,226283,210250,210017,210245,209490,209492,209796,208845,205639,207640,226284,208456,210770,209984,208682,209483,209486,205788,207273,207295,210306,208653,209489,209491,209797,210283,210285,210286,210373,210964,205561,206775,207773,208650,210043,210251,211544,210297,210000,204042,208169,208661,209986,210104,210249,210281,209518,203899,211418,210734,208406,210257,211722,204068,207908,209293,209484,205783,205787,198213,207737,210949,210282,210284,212102,209494,208288,209876,211230,203059,204948,205916,206301,208122,211824,208287,210037,211274,212020,208458,208460,212047,210648,211570,210613,210044,206518,210872,205782,207774,210769,211572,212066,210477,210677,210678,210866,210867,211316,206826,211416,207155,207728,209571,210947,226310,226335,208457,208459,210252,210253,210255,210256,212092,204069,208170,209254,210474,211564,208786,210869,210870,212579,210479,210860,208807,202924,211046,205640,210646,209166,210771,211096,212313,212366,205777,205779,211583,212502,208871,210863,211228,205563,211380,212840,207641,210732,213137,211857,209532,210505,205579,210871,210873,211203,205789,208847,211695,212403,212907,210862,210868,211514,208094,207552,211444,212657,204949,211259,211394,211426,210905,209334,211227,208813,210874,211802,207993,210566,212015,210317,210864,212872,206382,207091,209163,210254,211430,212048,212049,212050,212422,211546,210298,212982,211571,158734,210865,211825,211888,212413,203901,209474,211868,211852,214478,213455,214264,211216,213060,212480,212482,212800,207142,210357,210717,211808,213141,211995,211197,211215,208289,211466,212934,207035,209572,209745,214476,212151,212420,212421,212424,213364,211929,208893,209487,212481,212483,210316,211202,212472,214122,211628,207157,211548,211993,209165,213662,214411,214029,211547,208816,210941,210944,212484,214019,211179,212065,212404,212474,212452,211266,212086,209982,210901,210942,214957,208654,210772,210773,211435,211723,212888,212889,212890,212891,214563,213105,208873,210506,212716,205784,205684,209510,212219,213187,214083,212411,212473,212475,213527,210300,212402,207909,210025,210797,211419,210897,212043,213454,212497,213419,205792,212477,213311,213209,212077,208553,212136,212896,212485,205791,205793,212407,211225,213577,213578,207553,211462,211869,206970,213660,214704}'::numeric[]))",
"Rows Removed by Index Recheck": 0,
"Shared Hit Blocks": 1837,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
},
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Left",
"Startup Cost": 3348.82,
"Total Cost": 12637.81,
"Plan Rows": 25401,
"Plan Width": 14,
"Actual Startup Time": 105.096,
"Actual Total Time": 127.458,
"Actual Rows": 480,
"Actual Loops": 1,
"Output": ["(min(CASE WHEN ((dense_rank() OVER (?)) = 1) THEN (logistics_voyage.freight_covered)::integer ELSE NULL::integer END))", "plvo.on_hold_0", "buvo.bunge_voyage_id"],
"Inner Unique": true,
"Hash Cond": "(logistics_voyage.logistics_voyage_id = plvo.logistics_voyage_id)",
"Shared Hit Blocks": 78879,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 21.15,
"Total Cost": 9303.80,
"Plan Rows": 2363,
"Plan Width": 15,
"Actual Startup Time": 54.549,
"Actual Total Time": 76.688,
"Actual Rows": 480,
"Actual Loops": 1,
"Output": ["(min(CASE WHEN ((dense_rank() OVER (?)) = 1) THEN (logistics_voyage.freight_covered)::integer ELSE NULL::integer END))", "logistics_voyage.logistics_voyage_id", "buvo.bunge_voyage_id"],
"Inner Unique": false,
"Shared Hit Blocks": 54884,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Aggregate",
"Strategy": "Sorted",
"Partial Mode": "Simple",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 20.73,
"Total Cost": 5165.83,
"Plan Rows": 2363,
"Plan Width": 9,
"Actual Startup Time": 54.514,
"Actual Total Time": 75.312,
"Actual Rows": 480,
"Actual Loops": 1,
"Output": ["logistics_voyage.logistics_voyage_id", "min(CASE WHEN ((dense_rank() OVER (?)) = 1) THEN (logistics_voyage.freight_covered)::integer ELSE NULL::integer END)"],
"Group Key": ["logistics_voyage.logistics_voyage_id"],
"Shared Hit Blocks": 52964,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Merge Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 20.73,
"Total Cost": 5118.57,
"Plan Rows": 2363,
"Plan Width": 14,
"Actual Startup Time": 27.224,
"Actual Total Time": 74.759,
"Actual Rows": 3098,
"Actual Loops": 1,
"Output": ["logistics_voyage.logistics_voyage_id", "(dense_rank() OVER (?))", "logistics_voyage.freight_covered"],
"Inner Unique": true,
"Merge Cond": "(logistics_voyage.logistics_voyage_id = bunge2logistics.logistics_voyage_id)",
"Shared Hit Blocks": 52964,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "WindowAgg",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 0.29,
"Total Cost": 4381.01,
"Plan Rows": 56502,
"Plan Width": 20,
"Actual Startup Time": 0.022,
"Actual Total Time": 66.582,
"Actual Rows": 56007,
"Actual Loops": 1,
"Output": ["logistics_voyage.logistics_voyage_id", "logistics_voyage.freight_covered", "logistics_voyage.rev", "dense_rank() OVER (?)"],
"Shared Hit Blocks": 51127,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "sys_c00163153",
"Relation Name": "logistics_voyage",
"Schema": "voyage_audit_own",
"Alias": "logistics_voyage",
"Startup Cost": 0.29,
"Total Cost": 3392.23,
"Plan Rows": 56502,
"Plan Width": 12,
"Actual Startup Time": 0.006,
"Actual Total Time": 30.193,
"Actual Rows": 56008,
"Actual Loops": 1,
"Output": ["logistics_voyage.logistics_voyage_id", "logistics_voyage.rev", "logistics_voyage.freight_covered"],
"Shared Hit Blocks": 51127,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
},
{
"Node Type": "Sort",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 20.44,
"Total Cost": 20.94,
"Plan Rows": 200,
"Plan Width": 32,
"Actual Startup Time": 1.629,
"Actual Total Time": 1.709,
"Actual Rows": 480,
"Actual Loops": 1,
"Output": ["bunge2logistics.logistics_voyage_id"],
"Sort Key": ["bunge2logistics.logistics_voyage_id"],
"Sort Method": "quicksort",
"Sort Space Used": 47,
"Sort Space Type": "Memory",
"Shared Hit Blocks": 1837,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Aggregate",
"Strategy": "Hashed",
"Partial Mode": "Simple",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 10.80,
"Total Cost": 12.80,
"Plan Rows": 200,
"Plan Width": 32,
"Actual Startup Time": 1.476,
"Actual Total Time": 1.520,
"Actual Rows": 480,
"Actual Loops": 1,
"Output": ["bunge2logistics.logistics_voyage_id"],
"Group Key": ["bunge2logistics.logistics_voyage_id"],
"Planned Partitions": 0,
"HashAgg Batches": 1,
"Peak Memory Usage": 89,
"Disk Usage": 0,
"Shared Hit Blocks": 1837,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "CTE Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"CTE Name": "bunge2logistics",
"Alias": "bunge2logistics",
"Startup Cost": 0.00,
"Total Cost": 9.60,
"Plan Rows": 480,
"Plan Width": 32,
"Actual Startup Time": 0.120,
"Actual Total Time": 1.337,
"Actual Rows": 480,
"Actual Loops": 1,
"Output": ["bunge2logistics.logistics_voyage_id"],
"Shared Hit Blocks": 1837,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
}
]
}
]
}
]
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "idx_bunge_voyage_logi_voyage",
"Relation Name": "bunge_voyage",
"Schema": "voyage_own",
"Alias": "buvo",
"Startup Cost": 0.42,
"Total Cost": 1.73,
"Plan Rows": 1,
"Plan Width": 11,
"Actual Startup Time": 0.002,
"Actual Total Time": 0.002,
"Actual Rows": 1,
"Actual Loops": 480,
"Output": ["buvo.bunge_voyage_id", "buvo.vessel_id", "buvo.logistics_voyage_id", "buvo.polo_voyage_id", "buvo.is_active", "buvo.created_on", "buvo.created_by", "buvo.updated_on", "buvo.updated_by", "buvo.voyage_type_id", "buvo.freight_voyage_id", "buvo.version", "buvo.agent_voyage_id", "buvo.is_freight_related"],
"Index Cond": "(buvo.logistics_voyage_id = logistics_voyage.logistics_voyage_id)",
"Rows Removed by Index Recheck": 0,
"Shared Hit Blocks": 1920,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 3300.80,
"Total Cost": 3300.80,
"Plan Rows": 2150,
"Plan Width": 10,
"Actual Startup Time": 50.516,
"Actual Total Time": 50.522,
"Actual Rows": 480,
"Actual Loops": 1,
"Output": ["plvo.on_hold_0", "plvo.logistics_voyage_id"],
"Hash Buckets": 4096,
"Original Hash Buckets": 4096,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 53,
"Shared Hit Blocks": 23995,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Subquery Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Alias": "plvo",
"Startup Cost": 21.00,
"Total Cost": 3300.80,
"Plan Rows": 2150,
"Plan Width": 10,
"Actual Startup Time": 30.988,
"Actual Total Time": 50.383,
"Actual Rows": 480,
"Actual Loops": 1,
"Output": ["plvo.on_hold_0", "plvo.logistics_voyage_id"],
"Shared Hit Blocks": 23995,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Aggregate",
"Strategy": "Sorted",
"Partial Mode": "Simple",
"Parent Relationship": "Subquery",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 21.00,
"Total Cost": 3279.30,
"Plan Rows": 2150,
"Plan Width": 10,
"Actual Startup Time": 30.987,
"Actual Total Time": 50.323,
"Actual Rows": 480,
"Actual Loops": 1,
"Output": ["planned_voyage.logistics_voyage_id", "min(CASE WHEN ((dense_rank() OVER (?)) = 1) THEN (planned_voyage.on_hold)::integer ELSE NULL::integer END)"],
"Group Key": ["planned_voyage.logistics_voyage_id"],
"Shared Hit Blocks": 23995,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Merge Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 21.00,
"Total Cost": 3236.30,
"Plan Rows": 2150,
"Plan Width": 15,
"Actual Startup Time": 6.063,
"Actual Total Time": 49.899,
"Actual Rows": 2135,
"Actual Loops": 1,
"Output": ["planned_voyage.logistics_voyage_id", "(dense_rank() OVER (?))", "planned_voyage.on_hold"],
"Inner Unique": true,
"Merge Cond": "(planned_voyage.logistics_voyage_id = bunge2logistics_1.logistics_voyage_id)",
"Shared Hit Blocks": 23995,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "WindowAgg",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 0.56,
"Total Cost": 2837.93,
"Plan Rows": 29437,
"Plan Width": 21,
"Actual Startup Time": 0.071,
"Actual Total Time": 46.025,
"Actual Rows": 29292,
"Actual Loops": 1,
"Output": ["planned_voyage.logistics_voyage_id", "planned_voyage.on_hold", "planned_voyage.rev", "dense_rank() OVER (?)"],
"Shared Hit Blocks": 23995,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Incremental Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 0.56,
"Total Cost": 2322.78,
"Plan Rows": 29437,
"Plan Width": 13,
"Actual Startup Time": 0.067,
"Actual Total Time": 28.818,
"Actual Rows": 29293,
"Actual Loops": 1,
"Output": ["planned_voyage.logistics_voyage_id", "planned_voyage.rev", "planned_voyage.on_hold"],
"Sort Key": ["planned_voyage.logistics_voyage_id", "planned_voyage.rev"],
"Presorted Key": ["planned_voyage.logistics_voyage_id"],
"Full-sort Groups": {
"Group Count": 808,
"Sort Methods Used": ["quicksort"],
"Sort Space Memory": {
"Average Sort Space Used": 26,
"Peak Sort Space Used": 26
}
},
"Pre-sorted Groups": {
"Group Count": 10,
"Sort Methods Used": ["quicksort"],
"Sort Space Memory": {
"Average Sort Space Used": 25,
"Peak Sort Space Used": 25
}
},
"Shared Hit Blocks": 23995,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "idx_planned_voyage_lv_id",
"Relation Name": "planned_voyage",
"Schema": "voyage_audit_own",
"Alias": "planned_voyage",
"Startup Cost": 0.29,
"Total Cost": 1179.82,
"Plan Rows": 29437,
"Plan Width": 13,
"Actual Startup Time": 0.008,
"Actual Total Time": 13.607,
"Actual Rows": 29303,
"Actual Loops": 1,
"Output": ["planned_voyage.logistics_voyage_id", "planned_voyage.rev", "planned_voyage.on_hold"],
"Shared Hit Blocks": 23995,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
}
]
},
{
"Node Type": "Sort",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 20.44,
"Total Cost": 20.94,
"Plan Rows": 200,
"Plan Width": 32,
"Actual Startup Time": 0.325,
"Actual Total Time": 0.401,
"Actual Rows": 480,
"Actual Loops": 1,
"Output": ["bunge2logistics_1.logistics_voyage_id"],
"Sort Key": ["bunge2logistics_1.logistics_voyage_id"],
"Sort Method": "quicksort",
"Sort Space Used": 47,
"Sort Space Type": "Memory",
"Shared Hit Blocks": 0,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Aggregate",
"Strategy": "Hashed",
"Partial Mode": "Simple",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 10.80,
"Total Cost": 12.80,
"Plan Rows": 200,
"Plan Width": 32,
"Actual Startup Time": 0.156,
"Actual Total Time": 0.210,
"Actual Rows": 480,
"Actual Loops": 1,
"Output": ["bunge2logistics_1.logistics_voyage_id"],
"Group Key": ["bunge2logistics_1.logistics_voyage_id"],
"Planned Partitions": 0,
"HashAgg Batches": 1,
"Peak Memory Usage": 89,
"Disk Usage": 0,
"Shared Hit Blocks": 0,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "CTE Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"CTE Name": "bunge2logistics",
"Alias": "bunge2logistics_1",
"Startup Cost": 0.00,
"Total Cost": 9.60,
"Plan Rows": 480,
"Plan Width": 32,
"Actual Startup Time": 0.001,
"Actual Total Time": 0.029,
"Actual Rows": 480,
"Actual Loops": 1,
"Output": ["bunge2logistics_1.logistics_voyage_id"],
"Shared Hit Blocks": 0,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
}
]
}
]
}
]
}
]
}
]
}
]
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 3310.76,
"Total Cost": 3310.76,
"Plan Rows": 3,
"Plan Width": 254,
"Actual Startup Time": 6938.279,
"Actual Total Time": 6938.309,
"Actual Rows": 458,
"Actual Loops": 1,
"Output": ["xold.vessel_id", "xold.constraints", "xold.slot_from", "xold.slot_text", "xold.new_remarks_cnt", "xold.freight_covered", "xold.on_hold", "xold.freight_comments", "xold.planning_comments", "xold.location_changed", "xold.cargo_changed", "xold.new_schedules_added", "xold.new_cargos_added", "xold.planned_slot_from", "xold.planned_slot_text", "xold.bunge_voyage_id"],
"Hash Buckets": 1024,
"Original Hash Buckets": 1024,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 89,
"Shared Hit Blocks": 8720208,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Subquery Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Alias": "xold",
"Startup Cost": 3294.96,
"Total Cost": 3310.76,
"Plan Rows": 3,
"Plan Width": 254,
"Actual Startup Time": 6805.867,
"Actual Total Time": 6937.943,
"Actual Rows": 458,
"Actual Loops": 1,
"Output": ["xold.vessel_id", "xold.constraints", "xold.slot_from", "xold.slot_text", "xold.new_remarks_cnt", "xold.freight_covered", "xold.on_hold", "xold.freight_comments", "xold.planning_comments", "xold.location_changed", "xold.cargo_changed", "xold.new_schedules_added", "xold.new_cargos_added", "xold.planned_slot_from", "xold.planned_slot_text", "xold.bunge_voyage_id"],
"Shared Hit Blocks": 8720208,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Aggregate",
"Strategy": "Sorted",
"Partial Mode": "Simple",
"Parent Relationship": "Subquery",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 3294.96,
"Total Cost": 3310.73,
"Plan Rows": 3,
"Plan Width": 254,
"Actual Startup Time": 6805.865,
"Actual Total Time": 6937.845,
"Actual Rows": 458,
"Actual Loops": 1,
"Output": ["bv.bunge_voyage_id", "alv.vessel_id", "alv.constraints", "max(ts.time_slot_from)", "substr(max(concat(ts.time_slot_from, ts.time_slot_text)), 20)", "count(DISTINCT ar.revision_id)", "alv.freight_covered", "max((apv1.on_hold)::integer)", "max((apv1.freight_comments)::text)", "max((apv1.planning_comments)::text)", "CASE WHEN (max((SubPlan 2)) > 0) THEN true ELSE false END", "CASE WHEN (max((SubPlan 3)) > 0) THEN true ELSE false END", "count(DISTINCT alsnr.revision_id)", "count(DISTINCT acnr.revision_id)", "max(pts.time_slot_from)", "substr(max(concat(pts.time_slot_from, pts.time_slot_text)), 20)"],
"Group Key": ["bv.bunge_voyage_id", "alv.vessel_id", "alv.constraints", "alv.freight_covered"],
"Shared Hit Blocks": 8720208,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 3294.96,
"Total Cost": 3294.97,
"Plan Rows": 3,
"Plan Width": 258,
"Actual Startup Time": 6805.696,
"Actual Total Time": 6807.873,
"Actual Rows": 20887,
"Actual Loops": 1,
"Output": ["bv.bunge_voyage_id", "alv.vessel_id", "alv.constraints", "alv.freight_covered", "ts.time_slot_from", "ts.time_slot_text", "ar.revision_id", "apv1.on_hold", "apv1.freight_comments", "apv1.planning_comments", "als.logistics_schedule_id", "als.rev", "als.is_active", "als.location_id", "ac.logistics_cargo_id", "ac.rev", "ac.commodity_id", "ac.quantity", "ac.tolerance", "ac.is_active", "alsnr.revision_id", "acnr.revision_id", "pts.time_slot_from", "pts.time_slot_text"],
"Sort Key": ["bv.bunge_voyage_id", "alv.vessel_id", "alv.constraints", "alv.freight_covered"],
"Sort Method": "quicksort",
"Sort Space Used": 6671,
"Sort Space Type": "Memory",
"Shared Hit Blocks": 8591328,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Left",
"Startup Cost": 668.32,
"Total Cost": 3294.94,
"Plan Rows": 3,
"Plan Width": 258,
"Actual Startup Time": 6.317,
"Actual Total Time": 6778.496,
"Actual Rows": 20887,
"Actual Loops": 1,
"Output": ["bv.bunge_voyage_id", "alv.vessel_id", "alv.constraints", "alv.freight_covered", "ts.time_slot_from", "ts.time_slot_text", "ar.revision_id", "apv1.on_hold", "apv1.freight_comments", "apv1.planning_comments", "als.logistics_schedule_id", "als.rev", "als.is_active", "als.location_id", "ac.logistics_cargo_id", "ac.rev", "ac.commodity_id", "ac.quantity", "ac.tolerance", "ac.is_active", "alsnr.revision_id", "acnr.revision_id", "pts.time_slot_from", "pts.time_slot_text"],
"Inner Unique": false,
"Shared Hit Blocks": 8591320,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Left",
"Startup Cost": 666.14,
"Total Cost": 3139.10,
"Plan Rows": 3,
"Plan Width": 175,
"Actual Startup Time": 6.235,
"Actual Total Time": 2056.639,
"Actual Rows": 20887,
"Actual Loops": 1,
"Output": ["alv.vessel_id", "alv.constraints", "alv.freight_covered", "als.logistics_schedule_id", "als.rev", "als.is_active", "als.location_id", "bv.bunge_voyage_id", "bv.logistics_voyage_id", "ac.logistics_cargo_id", "ac.rev", "ac.commodity_id", "ac.quantity", "ac.tolerance", "ac.is_active", "ar.revision_id", "alsnr.revision_id", "acnr.revision_id", "ts.time_slot_from", "ts.time_slot_text"],
"Inner Unique": true,
"Shared Hit Blocks": 2438651,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Left",
"Startup Cost": 665.71,
"Total Cost": 3135.68,
"Plan Rows": 3,
"Plan Width": 175,
"Actual Startup Time": 6.213,
"Actual Total Time": 2003.311,
"Actual Rows": 20887,
"Actual Loops": 1,
"Output": ["alv.vessel_id", "alv.constraints", "alv.freight_covered", "als.logistics_schedule_id", "als.rev", "als.is_active", "als.location_id", "bv.bunge_voyage_id", "bv.logistics_voyage_id", "ac.logistics_cargo_id", "ac.rev", "ac.commodity_id", "ac.quantity", "ac.tolerance", "ac.is_active", "ar.revision_id", "alsn.rev", "acnr.revision_id", "ts.time_slot_from", "ts.time_slot_text"],
"Inner Unique": false,
"Shared Hit Blocks": 2375941,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Left",
"Startup Cost": 665.41,
"Total Cost": 3132.29,
"Plan Rows": 1,
"Plan Width": 174,
"Actual Startup Time": 6.205,
"Actual Total Time": 1954.215,
"Actual Rows": 6663,
"Actual Loops": 1,
"Output": ["alv.vessel_id", "alv.constraints", "alv.freight_covered", "alv.logistics_voyage_id", "als.logistics_schedule_id", "als.rev", "als.is_active", "als.location_id", "bv.bunge_voyage_id", "bv.logistics_voyage_id", "ac.logistics_cargo_id", "ac.rev", "ac.commodity_id", "ac.quantity", "ac.tolerance", "ac.is_active", "ar.revision_id", "acnr.revision_id", "ts.time_slot_from", "ts.time_slot_text"],
"Inner Unique": true,
"Shared Hit Blocks": 2320968,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Left",
"Startup Cost": 665.14,
"Total Cost": 3131.95,
"Plan Rows": 1,
"Plan Width": 155,
"Actual Startup Time": 6.192,
"Actual Total Time": 1938.940,
"Actual Rows": 6663,
"Actual Loops": 1,
"Output": ["alv.vessel_id", "alv.constraints", "alv.freight_covered", "alv.logistics_voyage_id", "als.logistics_schedule_id", "als.rev", "als.is_active", "als.location_id", "als.time_slot_id", "bv.bunge_voyage_id", "bv.logistics_voyage_id", "ac.logistics_cargo_id", "ac.rev", "ac.commodity_id", "ac.quantity", "ac.tolerance", "ac.is_active", "ar.revision_id", "acnr.revision_id"],
"Inner Unique": true,
"Shared Hit Blocks": 2300979,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Left",
"Startup Cost": 664.71,
"Total Cost": 3130.83,
"Plan Rows": 1,
"Plan Width": 155,
"Actual Startup Time": 6.182,
"Actual Total Time": 1921.298,
"Actual Rows": 6663,
"Actual Loops": 1,
"Output": ["alv.vessel_id", "alv.constraints", "alv.freight_covered", "alv.logistics_voyage_id", "als.logistics_schedule_id", "als.rev", "als.is_active", "als.location_id", "als.time_slot_id", "bv.bunge_voyage_id", "bv.logistics_voyage_id", "ac.logistics_cargo_id", "ac.rev", "ac.commodity_id", "ac.quantity", "ac.tolerance", "ac.is_active", "ar.revision_id", "acn.rev"],
"Inner Unique": false,
"Shared Hit Blocks": 2280990,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Left",
"Startup Cost": 664.42,
"Total Cost": 3130.48,
"Plan Rows": 1,
"Plan Width": 155,
"Actual Startup Time": 6.173,
"Actual Total Time": 1899.766,
"Actual Rows": 6663,
"Actual Loops": 1,
"Output": ["alv.vessel_id", "alv.constraints", "alv.freight_covered", "alv.logistics_voyage_id", "als.logistics_schedule_id", "als.rev", "als.is_active", "als.location_id", "als.time_slot_id", "bv.bunge_voyage_id", "bv.logistics_voyage_id", "cs.logistics_cargo_id", "ac.logistics_cargo_id", "ac.rev", "ac.commodity_id", "ac.quantity", "ac.tolerance", "ac.is_active", "ar.revision_id"],
"Inner Unique": true,
"Shared Hit Blocks": 2257175,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 663.98,
"Total Cost": 3129.25,
"Plan Rows": 1,
"Plan Width": 155,
"Actual Startup Time": 6.163,
"Actual Total Time": 1882.294,
"Actual Rows": 6663,
"Actual Loops": 1,
"Output": ["alv.vessel_id", "alv.constraints", "alv.freight_covered", "alv.logistics_voyage_id", "als.logistics_schedule_id", "als.rev", "als.is_active", "als.location_id", "als.time_slot_id", "bv.bunge_voyage_id", "bv.logistics_voyage_id", "cs.logistics_cargo_id", "ac.logistics_cargo_id", "ac.rev", "ac.commodity_id", "ac.quantity", "ac.tolerance", "ac.is_active", "avr.rev"],
"Inner Unique": true,
"Shared Hit Blocks": 2239036,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 663.55,
"Total Cost": 3127.61,
"Plan Rows": 1,
"Plan Width": 155,
"Actual Startup Time": 6.151,
"Actual Total Time": 1864.549,
"Actual Rows": 6663,
"Actual Loops": 1,
"Output": ["alv.vessel_id", "alv.constraints", "alv.freight_covered", "alv.logistics_voyage_id", "als.logistics_schedule_id", "als.rev", "als.is_active", "als.location_id", "als.time_slot_id", "bv.bunge_voyage_id", "bv.logistics_voyage_id", "cs.logistics_cargo_id", "ac.logistics_cargo_id", "ac.rev", "ac.commodity_id", "ac.quantity", "ac.tolerance", "ac.is_active", "avr.rev"],
"Inner Unique": false,
"Join Filter": "(alv.logistics_voyage_id = als.logistics_voyage_id)",
"Rows Removed by Join Filter": 0,
"Shared Hit Blocks": 2219045,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 663.26,
"Total Cost": 3085.91,
"Plan Rows": 1,
"Plan Width": 135,
"Actual Startup Time": 6.109,
"Actual Total Time": 1013.749,
"Actual Rows": 2456,
"Actual Loops": 1,
"Output": ["alv.vessel_id", "alv.constraints", "alv.freight_covered", "alv.logistics_voyage_id", "bv.bunge_voyage_id", "bv.logistics_voyage_id", "s.logistics_voyage_id", "cs.logistics_cargo_id", "ac.logistics_cargo_id", "ac.rev", "ac.commodity_id", "ac.quantity", "ac.tolerance", "ac.is_active", "avr.rev"],
"Inner Unique": false,
"Join Filter": "(bv.logistics_voyage_id = alv.logistics_voyage_id)",
"Rows Removed by Join Filter": 0,
"Shared Hit Blocks": 1132744,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 662.97,
"Total Cost": 3025.45,
"Plan Rows": 1,
"Plan Width": 56,
"Actual Startup Time": 6.046,
"Actual Total Time": 77.431,
"Actual Rows": 2456,
"Actual Loops": 1,
"Output": ["bv.bunge_voyage_id", "bv.logistics_voyage_id", "s.logistics_voyage_id", "cs.logistics_cargo_id", "ac.logistics_cargo_id", "ac.rev", "ac.commodity_id", "ac.quantity", "ac.tolerance", "ac.is_active", "avr.rev"],
"Inner Unique": false,
"Shared Hit Blocks": 53117,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 662.68,
"Total Cost": 2511.89,
"Plan Rows": 93,
"Plan Width": 28,
"Actual Startup Time": 5.996,
"Actual Total Time": 29.575,
"Actual Rows": 2936,
"Actual Loops": 1,
"Output": ["bv.bunge_voyage_id", "bv.logistics_voyage_id", "s.logistics_voyage_id", "cs.logistics_cargo_id", "avr.rev"],
"Inner Unique": false,
"Shared Hit Blocks": 12296,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 662.39,
"Total Cost": 2481.84,
"Plan Rows": 89,
"Plan Width": 28,
"Actual Startup Time": 5.978,
"Actual Total Time": 18.224,
"Actual Rows": 2806,
"Actual Loops": 1,
"Output": ["bv.bunge_voyage_id", "bv.logistics_voyage_id", "s.logistics_voyage_id", "s.logistics_schedule_id", "avr.rev"],
"Inner Unique": false,
"Shared Hit Blocks": 3983,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Right",
"Startup Cost": 662.09,
"Total Cost": 1647.94,
"Plan Rows": 480,
"Plan Width": 17,
"Actual Startup Time": 5.952,
"Actual Total Time": 11.511,
"Actual Rows": 1202,
"Actual Loops": 1,
"Output": ["bv.bunge_voyage_id", "bv.logistics_voyage_id", "avr.rev"],
"Inner Unique": true,
"Hash Cond": "(avr.bunge_voyage_id = bv.bunge_voyage_id)",
"Shared Hit Blocks": 2336,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Relation Name": "voyage_remark",
"Schema": "voyage_audit_own",
"Alias": "avr",
"Startup Cost": 0.00,
"Total Cost": 916.05,
"Plan Rows": 26589,
"Plan Width": 12,
"Actual Startup Time": 0.010,
"Actual Total Time": 4.440,
"Actual Rows": 27945,
"Actual Loops": 1,
"Output": ["avr.logistics_voyage_remark_id", "avr.rev", "avr.revtype", "avr.bunge_voyage_id", "avr.freight_voyage_id", "avr.is_active", "avr.logistics_voyage_id", "avr.remark_id", "avr.remark_reference"],
"Filter": "(avr.revtype = 0)",
"Rows Removed by Filter": 6607,
"Shared Hit Blocks": 502,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 656.09,
"Total Cost": 656.09,
"Plan Rows": 480,
"Plan Width": 11,
"Actual Startup Time": 1.390,
"Actual Total Time": 1.391,
"Actual Rows": 480,
"Actual Loops": 1,
"Output": ["bv.bunge_voyage_id", "bv.logistics_voyage_id"],
"Hash Buckets": 1024,
"Original Hash Buckets": 1024,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 30,
"Shared Hit Blocks": 1834,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "idx_bunge_voyage_id",
"Relation Name": "bunge_voyage",
"Schema": "voyage_own",
"Alias": "bv",
"Startup Cost": 0.42,
"Total Cost": 656.09,
"Plan Rows": 480,
"Plan Width": 11,
"Actual Startup Time": 0.111,
"Actual Total Time": 1.288,
"Actual Rows": 480,
"Actual Loops": 1,
"Output": ["bv.bunge_voyage_id", "bv.logistics_voyage_id"],
"Index Cond": "(bv.bunge_voyage_id = ANY ('{226167,226168,226171,226172,226173,226174,226175,226176,226178,226186,226188,226197,226213,226219,226231,226170,209930,209399,208856,208908,208285,201878,206722,209815,206989,208808,205769,207558,208186,208527,208851,206395,203040,203258,205333,207477,207780,202501,202765,206000,208892,226257,209124,209125,209161,208821,205687,208894,205786,198204,208495,208652,208939,209805,207621,207367,204035,207464,206319,204482,204588,207938,209298,209162,208407,202596,209817,204066,208665,208946,209057,205780,207559,207846,207319,205559,206344,206856,207033,209465,210103,210356,210296,202764,206886,209058,206858,208496,208497,208498,210480,210247,206855,204946,205633,205638,206774,207772,209720,226269,206436,209721,210067,208848,208796,203868,207816,209344,205781,210203,203039,203898,208388,208493,208576,208890,210248,210923,210768,202595,210675,208857,204067,207079,205583,209463,209464,209479,209480,209481,210680,210450,208368,208367,209011,210627,210628,207994,210172,204015,207622,211177,208542,209565,208168,203785,208681,209482,205675,206394,207736,210948,208940,209488,210278,203259,205521,226283,210250,210017,210245,209490,209492,209796,208845,205639,207640,226284,208456,210770,209984,208682,209483,209486,205788,207273,207295,210306,208653,209489,209491,209797,210283,210285,210286,210373,210964,205561,206775,207773,208650,210043,210251,211544,210297,210000,204042,208169,208661,209986,210104,210249,210281,209518,203899,211418,210734,208406,210257,211722,204068,207908,209293,209484,205783,205787,198213,207737,210949,210282,210284,212102,209494,208288,209876,211230,203059,204948,205916,206301,208122,211824,208287,210037,211274,212020,208458,208460,212047,210648,211570,210613,210044,206518,210872,205782,207774,210769,211572,212066,210477,210677,210678,210866,210867,211316,206826,211416,207155,207728,209571,210947,226310,226335,208457,208459,210252,210253,210255,210256,212092,204069,208170,209254,210474,211564,208786,210869,210870,212579,210479,210860,208807,202924,211046,205640,210646,209166,210771,211096,212313,212366,205777,205779,211583,212502,208871,210863,211228,205563,211380,212840,207641,210732,213137,211857,209532,210505,205579,210871,210873,211203,205789,208847,211695,212403,212907,210862,210868,211514,208094,207552,211444,212657,204949,211259,211394,211426,210905,209334,211227,208813,210874,211802,207993,210566,212015,210317,210864,212872,206382,207091,209163,210254,211430,212048,212049,212050,212422,211546,210298,212982,211571,158734,210865,211825,211888,212413,203901,209474,211868,211852,214478,213455,214264,211216,213060,212480,212482,212800,207142,210357,210717,211808,213141,211995,211197,211215,208289,211466,212934,207035,209572,209745,214476,212151,212420,212421,212424,213364,211929,208893,209487,212481,212483,210316,211202,212472,214122,211628,207157,211548,211993,209165,213662,214411,214029,211547,208816,210941,210944,212484,214019,211179,212065,212404,212474,212452,211266,212086,209982,210901,210942,214957,208654,210772,210773,211435,211723,212888,212889,212890,212891,214563,213105,208873,210506,212716,205784,205684,209510,212219,213187,214083,212411,212473,212475,213527,210300,212402,207909,210025,210797,211419,210897,212043,213454,212497,213419,205792,212477,213311,213209,212077,208553,212136,212896,212485,205791,205793,212407,211225,213577,213578,207553,211462,211869,206970,213660,214704}'::numeric[]))",
"Rows Removed by Index Recheck": 0,
"Shared Hit Blocks": 1834,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
}
]
},
{
"Node Type": "Memoize",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 0.30,
"Total Cost": 1.73,
"Plan Rows": 3,
"Plan Width": 11,
"Actual Startup Time": 0.003,
"Actual Total Time": 0.004,
"Actual Rows": 2,
"Actual Loops": 1202,
"Output": ["s.logistics_voyage_id", "s.logistics_schedule_id"],
"Cache Key": "bv.logistics_voyage_id",
"Cache Mode": "logical",
"Cache Hits": 722,
"Cache Misses": 480,
"Cache Evictions": 0,
"Cache Overflows": 0,
"Peak Memory Usage": 87,
"Shared Hit Blocks": 1647,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "idx_logistics_schedule_lv_id",
"Relation Name": "logistics_schedule",
"Schema": "voyage_own",
"Alias": "s",
"Startup Cost": 0.29,
"Total Cost": 1.72,
"Plan Rows": 3,
"Plan Width": 11,
"Actual Startup Time": 0.005,
"Actual Total Time": 0.006,
"Actual Rows": 2,
"Actual Loops": 480,
"Output": ["s.logistics_voyage_id", "s.logistics_schedule_id"],
"Index Cond": "(s.logistics_voyage_id = bv.logistics_voyage_id)",
"Rows Removed by Index Recheck": 0,
"Shared Hit Blocks": 1647,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
}
]
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "idx_logistics_cargo_schedule_ls_id",
"Relation Name": "logistics_cargo_schedule",
"Schema": "voyage_own",
"Alias": "cs",
"Startup Cost": 0.29,
"Total Cost": 0.33,
"Plan Rows": 1,
"Plan Width": 12,
"Actual Startup Time": 0.003,
"Actual Total Time": 0.003,
"Actual Rows": 1,
"Actual Loops": 2806,
"Output": ["cs.logistics_cargo_schedule_id", "cs.logistics_cargo_id", "cs.logistics_schedule_id", "cs.quantity", "cs.is_active", "cs.created_on", "cs.created_by", "cs.updated_by", "cs.updated_on"],
"Index Cond": "(cs.logistics_schedule_id = s.logistics_schedule_id)",
"Rows Removed by Index Recheck": 0,
"Shared Hit Blocks": 8313,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "sys_c00163119",
"Relation Name": "logistics_cargo",
"Schema": "voyage_audit_own",
"Alias": "ac",
"Startup Cost": 0.29,
"Total Cost": 5.51,
"Plan Rows": 1,
"Plan Width": 28,
"Actual Startup Time": 0.015,
"Actual Total Time": 0.015,
"Actual Rows": 1,
"Actual Loops": 2936,
"Output": ["ac.logistics_cargo_id", "ac.rev", "ac.revtype", "ac.is_active", "ac.loading_coordinator_id", "ac.logistics_voyage_id", "ac.commodity_id", "ac.quantity", "ac.schedule_reference", "ac.tolerance"],
"Index Cond": "(ac.logistics_cargo_id = cs.logistics_cargo_id)",
"Rows Removed by Index Recheck": 0,
"Filter": "(ac.rev = (SubPlan 7))",
"Rows Removed by Filter": 1,
"Shared Hit Blocks": 40821,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Aggregate",
"Strategy": "Plain",
"Partial Mode": "Simple",
"Parent Relationship": "SubPlan",
"Subplan Name": "SubPlan 7",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 5.16,
"Total Cost": 5.17,
"Plan Rows": 1,
"Plan Width": 32,
"Actual Startup Time": 0.008,
"Actual Total Time": 0.008,
"Actual Rows": 1,
"Actual Loops": 4062,
"Output": ["max(ar4.revision_id)"],
"Shared Hit Blocks": 30912,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 0.72,
"Total Cost": 5.16,
"Plan Rows": 1,
"Plan Width": 6,
"Actual Startup Time": 0.005,
"Actual Total Time": 0.007,
"Actual Rows": 2,
"Actual Loops": 4062,
"Output": ["ar4.revision_id"],
"Inner Unique": true,
"Shared Hit Blocks": 30912,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Index Only Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "sys_c00163119",
"Relation Name": "logistics_cargo",
"Schema": "voyage_audit_own",
"Alias": "ac1",
"Startup Cost": 0.29,
"Total Cost": 2.51,
"Plan Rows": 1,
"Plan Width": 6,
"Actual Startup Time": 0.002,
"Actual Total Time": 0.002,
"Actual Rows": 2,
"Actual Loops": 4062,
"Output": ["ac1.logistics_cargo_id", "ac1.rev"],
"Index Cond": "(ac1.logistics_cargo_id = ac.logistics_cargo_id)",
"Rows Removed by Index Recheck": 0,
"Heap Fetches": 498,
"Shared Hit Blocks": 8638,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
},
{
"Node Type": "Index Only Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "idx_rev_id_updated",
"Relation Name": "revision",
"Schema": "voyage_audit_own",
"Alias": "ar4",
"Startup Cost": 0.43,
"Total Cost": 2.65,
"Plan Rows": 1,
"Plan Width": 6,
"Actual Startup Time": 0.002,
"Actual Total Time": 0.002,
"Actual Rows": 1,
"Actual Loops": 7424,
"Output": ["ar4.revision_id", "ar4.updated_on"],
"Index Cond": "((ar4.revision_id = ac1.rev) AND (ar4.updated_on < '2023-12-12 00:00:00'::timestamp without time zone))",
"Rows Removed by Index Recheck": 0,
"Heap Fetches": 0,
"Shared Hit Blocks": 22274,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
}
]
}
]
}
]
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "sys_c00163153",
"Relation Name": "logistics_voyage",
"Schema": "voyage_audit_own",
"Alias": "alv",
"Startup Cost": 0.29,
"Total Cost": 60.45,
"Plan Rows": 1,
"Plan Width": 79,
"Actual Startup Time": 0.378,
"Actual Total Time": 0.380,
"Actual Rows": 1,
"Actual Loops": 2456,
"Output": ["alv.logistics_voyage_id", "alv.rev", "alv.revtype", "alv.constraints", "alv.cp_date", "alv.cp_dem_rate", "alv.cp_des_rate", "alv.cp_hire_rate", "alv.cp_type", "alv.freight_covered", "alv.freight_office_id", "alv.fully_purchased", "alv.is_active", "alv.max_sail_date", "alv.nomination_key", "alv.nomination_number", "alv.nomination_type", "alv.office_id", "alv.require_grab", "alv.suggested_vessel_size_id", "alv.system_id", "alv.user_fixed_by", "alv.user_fixed_on", "alv.user_nomination_by", "alv.user_nomination_on", "alv.user_updated_by", "alv.user_updated_on", "alv.vessel_id", "alv.vessel_name", "alv.voyage_description", "alv.voyage_state", "alv.ebl", "alv.freight_booking", "alv.letter_of_credit", "alv.letter_of_credit_days", "alv.import_license", "alv.import_license_days", "alv.pre_advice", "alv.pre_advice_days", "alv.org_contract_from", "alv.org_contract_to", "alv.sales_contract_from", "alv.sales_contract_to", "alv.letter_of_credit_email", "alv.import_license_email", "alv.pre_advice_email", "alv.freight_coverage_type"],
"Index Cond": "(alv.logistics_voyage_id = s.logistics_voyage_id)",
"Rows Removed by Index Recheck": 0,
"Filter": "(alv.rev = (SubPlan 5))",
"Rows Removed by Filter": 8,
"Shared Hit Blocks": 1079627,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Aggregate",
"Strategy": "Plain",
"Partial Mode": "Simple",
"Parent Relationship": "SubPlan",
"Subplan Name": "SubPlan 5",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 11.99,
"Total Cost": 12.00,
"Plan Rows": 1,
"Plan Width": 32,
"Actual Startup Time": 0.041,
"Actual Total Time": 0.041,
"Actual Rows": 1,
"Actual Loops": 21743,
"Output": ["max(ar1.revision_id)"],
"Shared Hit Blocks": 1055045,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 0.72,
"Total Cost": 11.97,
"Plan Rows": 5,
"Plan Width": 6,
"Actual Startup Time": 0.005,
"Actual Total Time": 0.039,
"Actual Rows": 15,
"Actual Loops": 21743,
"Output": ["ar1.revision_id"],
"Inner Unique": true,
"Shared Hit Blocks": 1055045,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Index Only Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "sys_c00163153",
"Relation Name": "logistics_voyage",
"Schema": "voyage_audit_own",
"Alias": "alv1",
"Startup Cost": 0.29,
"Total Cost": 3.11,
"Plan Rows": 5,
"Plan Width": 6,
"Actual Startup Time": 0.002,
"Actual Total Time": 0.004,
"Actual Rows": 15,
"Actual Loops": 21743,
"Output": ["alv1.logistics_voyage_id", "alv1.rev"],
"Index Cond": "(alv1.logistics_voyage_id = alv.logistics_voyage_id)",
"Rows Removed by Index Recheck": 0,
"Heap Fetches": 50232,
"Shared Hit Blocks": 88578,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
},
{
"Node Type": "Index Only Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "idx_rev_id_updated",
"Relation Name": "revision",
"Schema": "voyage_audit_own",
"Alias": "ar1",
"Startup Cost": 0.43,
"Total Cost": 1.77,
"Plan Rows": 1,
"Plan Width": 6,
"Actual Startup Time": 0.002,
"Actual Total Time": 0.002,
"Actual Rows": 1,
"Actual Loops": 321679,
"Output": ["ar1.revision_id", "ar1.updated_on"],
"Index Cond": "((ar1.revision_id = alv1.rev) AND (ar1.updated_on < '2023-12-12 00:00:00'::timestamp without time zone))",
"Rows Removed by Index Recheck": 0,
"Heap Fetches": 1428,
"Shared Hit Blocks": 966467,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
}
]
}
]
}
]
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "idx_audit_logistics_schedule_lv_id",
"Relation Name": "logistics_schedule",
"Schema": "voyage_audit_own",
"Alias": "als",
"Startup Cost": 0.29,
"Total Cost": 41.69,
"Plan Rows": 1,
"Plan Width": 30,
"Actual Startup Time": 0.059,
"Actual Total Time": 0.345,
"Actual Rows": 3,
"Actual Loops": 2456,
"Output": ["als.logistics_schedule_id", "als.rev", "als.revtype", "als.is_active", "als.lja_rate", "als.location_id", "als.logistics_eta", "als.logistics_voyage_id", "als.port_agent_id", "als.schedule_reference", "als.schedule_type", "als.time_slot_id"],
"Index Cond": "(als.logistics_voyage_id = s.logistics_voyage_id)",
"Rows Removed by Index Recheck": 0,
"Filter": "(als.rev = (SubPlan 6))",
"Rows Removed by Filter": 10,
"Shared Hit Blocks": 1086301,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Aggregate",
"Strategy": "Plain",
"Partial Mode": "Simple",
"Parent Relationship": "SubPlan",
"Subplan Name": "SubPlan 6",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 6.86,
"Total Cost": 6.87,
"Plan Rows": 1,
"Plan Width": 32,
"Actual Startup Time": 0.027,
"Actual Total Time": 0.027,
"Actual Rows": 1,
"Actual Loops": 30096,
"Output": ["max(ar2.revision_id)"],
"Shared Hit Blocks": 1066123,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 0.85,
"Total Cost": 6.86,
"Plan Rows": 2,
"Plan Width": 6,
"Actual Startup Time": 0.005,
"Actual Total Time": 0.026,
"Actual Rows": 4,
"Actual Loops": 30096,
"Output": ["ar2.revision_id"],
"Inner Unique": true,
"Shared Hit Blocks": 1066123,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Index Only Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "sys_c00163132",
"Relation Name": "logistics_schedule",
"Schema": "voyage_audit_own",
"Alias": "als1",
"Startup Cost": 0.42,
"Total Cost": 2.65,
"Plan Rows": 2,
"Plan Width": 6,
"Actual Startup Time": 0.002,
"Actual Total Time": 0.005,
"Actual Rows": 9,
"Actual Loops": 30096,
"Output": ["als1.logistics_schedule_id", "als1.rev"],
"Index Cond": "(als1.logistics_schedule_id = als.logistics_schedule_id)",
"Rows Removed by Index Recheck": 0,
"Heap Fetches": 162259,
"Shared Hit Blocks": 253199,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
},
{
"Node Type": "Index Only Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "idx_rev_id_updated",
"Relation Name": "revision",
"Schema": "voyage_audit_own",
"Alias": "ar2",
"Startup Cost": 0.43,
"Total Cost": 2.10,
"Plan Rows": 1,
"Plan Width": 6,
"Actual Startup Time": 0.002,
"Actual Total Time": 0.002,
"Actual Rows": 0,
"Actual Loops": 270610,
"Output": ["ar2.revision_id", "ar2.updated_on"],
"Index Cond": "((ar2.revision_id = als1.rev) AND (ar2.updated_on < '2023-12-12 00:00:00'::timestamp without time zone))",
"Rows Removed by Index Recheck": 0,
"Heap Fetches": 1092,
"Shared Hit Blocks": 812924,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
}
]
}
]
}
]
},
{
"Node Type": "Index Only Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "idx_rev_id_updated",
"Relation Name": "revision",
"Schema": "voyage_audit_own",
"Alias": "acr",
"Startup Cost": 0.43,
"Total Cost": 1.64,
"Plan Rows": 1,
"Plan Width": 6,
"Actual Startup Time": 0.002,
"Actual Total Time": 0.002,
"Actual Rows": 1,
"Actual Loops": 6663,
"Output": ["acr.revision_id", "acr.updated_on"],
"Index Cond": "(acr.revision_id = ac.rev)",
"Rows Removed by Index Recheck": 0,
"Heap Fetches": 0,
"Shared Hit Blocks": 19991,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
},
{
"Node Type": "Index Only Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "idx_rev_id_updated",
"Relation Name": "revision",
"Schema": "voyage_audit_own",
"Alias": "ar",
"Startup Cost": 0.43,
"Total Cost": 1.23,
"Plan Rows": 1,
"Plan Width": 6,
"Actual Startup Time": 0.002,
"Actual Total Time": 0.002,
"Actual Rows": 0,
"Actual Loops": 6663,
"Output": ["ar.revision_id", "ar.updated_on"],
"Index Cond": "((ar.revision_id = avr.rev) AND (ar.updated_on > '2023-12-12 00:00:00'::timestamp without time zone))",
"Rows Removed by Index Recheck": 0,
"Heap Fetches": 942,
"Shared Hit Blocks": 18139,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "sys_c00163119",
"Relation Name": "logistics_cargo",
"Schema": "voyage_audit_own",
"Alias": "acn",
"Startup Cost": 0.29,
"Total Cost": 0.34,
"Plan Rows": 1,
"Plan Width": 12,
"Actual Startup Time": 0.002,
"Actual Total Time": 0.003,
"Actual Rows": 1,
"Actual Loops": 6663,
"Output": ["acn.logistics_cargo_id", "acn.rev", "acn.revtype", "acn.is_active", "acn.loading_coordinator_id", "acn.logistics_voyage_id", "acn.commodity_id", "acn.quantity", "acn.schedule_reference", "acn.tolerance"],
"Index Cond": "(acn.logistics_cargo_id = cs.logistics_cargo_id)",
"Rows Removed by Index Recheck": 0,
"Filter": "(acn.revtype = 0)",
"Rows Removed by Filter": 1,
"Shared Hit Blocks": 23815,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
},
{
"Node Type": "Index Only Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "idx_rev_id_updated",
"Relation Name": "revision",
"Schema": "voyage_audit_own",
"Alias": "acnr",
"Startup Cost": 0.43,
"Total Cost": 1.13,
"Plan Rows": 1,
"Plan Width": 6,
"Actual Startup Time": 0.002,
"Actual Total Time": 0.002,
"Actual Rows": 0,
"Actual Loops": 6663,
"Output": ["acnr.revision_id", "acnr.updated_on"],
"Index Cond": "((acnr.revision_id = acn.rev) AND (acnr.updated_on > '2023-12-12 00:00:00'::timestamp without time zone))",
"Rows Removed by Index Recheck": 0,
"Heap Fetches": 0,
"Shared Hit Blocks": 19989,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "pk_time_slot",
"Relation Name": "time_slot",
"Schema": "voyage_own",
"Alias": "ts",
"Startup Cost": 0.28,
"Total Cost": 0.33,
"Plan Rows": 1,
"Plan Width": 33,
"Actual Startup Time": 0.002,
"Actual Total Time": 0.002,
"Actual Rows": 1,
"Actual Loops": 6663,
"Output": ["ts.time_slot_id", "ts.time_slot_text", "ts.time_slot_from", "ts.time_slot_to"],
"Index Cond": "(ts.time_slot_id = als.time_slot_id)",
"Rows Removed by Index Recheck": 0,
"Shared Hit Blocks": 19989,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "idx_audit_logistics_schedule_lv_id",
"Relation Name": "logistics_schedule",
"Schema": "voyage_audit_own",
"Alias": "alsn",
"Startup Cost": 0.29,
"Total Cost": 3.36,
"Plan Rows": 3,
"Plan Width": 11,
"Actual Startup Time": 0.003,
"Actual Total Time": 0.006,
"Actual Rows": 3,
"Actual Loops": 6663,
"Output": ["alsn.logistics_schedule_id", "alsn.rev", "alsn.revtype", "alsn.is_active", "alsn.lja_rate", "alsn.location_id", "alsn.logistics_eta", "alsn.logistics_voyage_id", "alsn.port_agent_id", "alsn.schedule_reference", "alsn.schedule_type", "alsn.time_slot_id"],
"Index Cond": "(alsn.logistics_voyage_id = alv.logistics_voyage_id)",
"Rows Removed by Index Recheck": 0,
"Filter": "(alsn.revtype = 0)",
"Rows Removed by Filter": 10,
"Shared Hit Blocks": 54973,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
},
{
"Node Type": "Index Only Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "idx_rev_id_updated",
"Relation Name": "revision",
"Schema": "voyage_audit_own",
"Alias": "alsnr",
"Startup Cost": 0.43,
"Total Cost": 1.14,
"Plan Rows": 1,
"Plan Width": 6,
"Actual Startup Time": 0.002,
"Actual Total Time": 0.002,
"Actual Rows": 0,
"Actual Loops": 20887,
"Output": ["alsnr.revision_id", "alsnr.updated_on"],
"Index Cond": "((alsnr.revision_id = alsn.rev) AND (alsnr.updated_on > '2023-12-12 00:00:00'::timestamp without time zone))",
"Rows Removed by Index Recheck": 0,
"Heap Fetches": 48,
"Shared Hit Blocks": 62710,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
},
{
"Node Type": "Nested Loop",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Left",
"Startup Cost": 2.18,
"Total Cost": 51.93,
"Plan Rows": 1,
"Plan Width": 94,
"Actual Startup Time": 0.135,
"Actual Total Time": 0.226,
"Actual Rows": 1,
"Actual Loops": 20887,
"Output": ["apv1.on_hold", "apv1.freight_comments", "apv1.planning_comments", "apv1.logistics_voyage_id", "pts.time_slot_from", "pts.time_slot_text"],
"Inner Unique": true,
"Shared Hit Blocks": 6152669,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 1.91,
"Total Cost": 51.64,
"Plan Rows": 1,
"Plan Width": 75,
"Actual Startup Time": 0.133,
"Actual Total Time": 0.223,
"Actual Rows": 1,
"Actual Loops": 20887,
"Output": ["apv1.on_hold", "apv1.freight_comments", "apv1.planning_comments", "apv1.logistics_voyage_id", "apv1.time_slot_id"],
"Inner Unique": false,
"Shared Hit Blocks": 6090008,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "idx_planned_voyage_lv_id",
"Relation Name": "planned_voyage",
"Schema": "voyage_audit_own",
"Alias": "apv1",
"Startup Cost": 0.29,
"Total Cost": 1.62,
"Plan Rows": 4,
"Plan Width": 81,
"Actual Startup Time": 0.002,
"Actual Total Time": 0.004,
"Actual Rows": 6,
"Actual Loops": 20887,
"Output": ["apv1.planned_voyage_id", "apv1.rev", "apv1.revtype", "apv1.logistics_voyage_id", "apv1.vessel_id", "apv1.time_slot_id", "apv1.planning_comments", "apv1.proposed_vessel", "apv1.on_hold", "apv1.is_hidden", "apv1.freight_comments", "apv1.holding_comments"],
"Index Cond": "(apv1.logistics_voyage_id = bv.logistics_voyage_id)",
"Rows Removed by Index Recheck": 0,
"Shared Hit Blocks": 143485,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "idx_bunge_voyage_logi_voyage",
"Relation Name": "bunge_voyage",
"Schema": "voyage_own",
"Alias": "bv15",
"Startup Cost": 1.62,
"Total Cost": 12.50,
"Plan Rows": 1,
"Plan Width": 5,
"Actual Startup Time": 0.037,
"Actual Total Time": 0.037,
"Actual Rows": 0,
"Actual Loops": 122545,
"Output": ["bv15.bunge_voyage_id", "bv15.vessel_id", "bv15.logistics_voyage_id", "bv15.polo_voyage_id", "bv15.is_active", "bv15.created_on", "bv15.created_by", "bv15.updated_on", "bv15.updated_by", "bv15.voyage_type_id", "bv15.freight_voyage_id", "bv15.version", "bv15.agent_voyage_id", "bv15.is_freight_related"],
"Index Cond": "(bv15.logistics_voyage_id = apv1.logistics_voyage_id)",
"Rows Removed by Index Recheck": 0,
"Filter": "((bv15.bunge_voyage_id = ANY ('{226167,226168,226171,226172,226173,226174,226175,226176,226178,226186,226188,226197,226213,226219,226231,226170,209930,209399,208856,208908,208285,201878,206722,209815,206989,208808,205769,207558,208186,208527,208851,206395,203040,203258,205333,207477,207780,202501,202765,206000,208892,226257,209124,209125,209161,208821,205687,208894,205786,198204,208495,208652,208939,209805,207621,207367,204035,207464,206319,204482,204588,207938,209298,209162,208407,202596,209817,204066,208665,208946,209057,205780,207559,207846,207319,205559,206344,206856,207033,209465,210103,210356,210296,202764,206886,209058,206858,208496,208497,208498,210480,210247,206855,204946,205633,205638,206774,207772,209720,226269,206436,209721,210067,208848,208796,203868,207816,209344,205781,210203,203039,203898,208388,208493,208576,208890,210248,210923,210768,202595,210675,208857,204067,207079,205583,209463,209464,209479,209480,209481,210680,210450,208368,208367,209011,210627,210628,207994,210172,204015,207622,211177,208542,209565,208168,203785,208681,209482,205675,206394,207736,210948,208940,209488,210278,203259,205521,226283,210250,210017,210245,209490,209492,209796,208845,205639,207640,226284,208456,210770,209984,208682,209483,209486,205788,207273,207295,210306,208653,209489,209491,209797,210283,210285,210286,210373,210964,205561,206775,207773,208650,210043,210251,211544,210297,210000,204042,208169,208661,209986,210104,210249,210281,209518,203899,211418,210734,208406,210257,211722,204068,207908,209293,209484,205783,205787,198213,207737,210949,210282,210284,212102,209494,208288,209876,211230,203059,204948,205916,206301,208122,211824,208287,210037,211274,212020,208458,208460,212047,210648,211570,210613,210044,206518,210872,205782,207774,210769,211572,212066,210477,210677,210678,210866,210867,211316,206826,211416,207155,207728,209571,210947,226310,226335,208457,208459,210252,210253,210255,210256,212092,204069,208170,209254,210474,211564,208786,210869,210870,212579,210479,210860,208807,202924,211046,205640,210646,209166,210771,211096,212313,212366,205777,205779,211583,212502,208871,210863,211228,205563,211380,212840,207641,210732,213137,211857,209532,210505,205579,210871,210873,211203,205789,208847,211695,212403,212907,210862,210868,211514,208094,207552,211444,212657,204949,211259,211394,211426,210905,209334,211227,208813,210874,211802,207993,210566,212015,210317,210864,212872,206382,207091,209163,210254,211430,212048,212049,212050,212422,211546,210298,212982,211571,158734,210865,211825,211888,212413,203901,209474,211868,211852,214478,213455,214264,211216,213060,212480,212482,212800,207142,210357,210717,211808,213141,211995,211197,211215,208289,211466,212934,207035,209572,209745,214476,212151,212420,212421,212424,213364,211929,208893,209487,212481,212483,210316,211202,212472,214122,211628,207157,211548,211993,209165,213662,214411,214029,211547,208816,210941,210944,212484,214019,211179,212065,212404,212474,212452,211266,212086,209982,210901,210942,214957,208654,210772,210773,211435,211723,212888,212889,212890,212891,214563,213105,208873,210506,212716,205784,205684,209510,212219,213187,214083,212411,212473,212475,213527,210300,212402,207909,210025,210797,211419,210897,212043,213454,212497,213419,205792,212477,213311,213209,212077,208553,212136,212896,212485,205791,205793,212407,211225,213577,213578,207553,211462,211869,206970,213660,214704}'::numeric[])) AND (apv1.rev = (SubPlan 4)))",
"Rows Removed by Filter": 1,
"Shared Hit Blocks": 5946523,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Aggregate",
"Strategy": "Plain",
"Partial Mode": "Simple",
"Parent Relationship": "SubPlan",
"Subplan Name": "SubPlan 4",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 10.69,
"Total Cost": 10.70,
"Plan Rows": 1,
"Plan Width": 32,
"Actual Startup Time": 0.034,
"Actual Total Time": 0.034,
"Actual Rows": 1,
"Actual Loops": 122545,
"Output": ["max(ar3.revision_id)"],
"Shared Hit Blocks": 5456343,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 0.72,
"Total Cost": 10.68,
"Plan Rows": 4,
"Plan Width": 6,
"Actual Startup Time": 0.005,
"Actual Total Time": 0.033,
"Actual Rows": 11,
"Actual Loops": 122545,
"Output": ["ar3.revision_id"],
"Inner Unique": true,
"Shared Hit Blocks": 5456343,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "idx_planned_voyage_lv_id",
"Relation Name": "planned_voyage",
"Schema": "voyage_audit_own",
"Alias": "apv1_1",
"Startup Cost": 0.29,
"Total Cost": 3.37,
"Plan Rows": 4,
"Plan Width": 6,
"Actual Startup Time": 0.002,
"Actual Total Time": 0.006,
"Actual Rows": 11,
"Actual Loops": 122545,
"Output": ["apv1_1.planned_voyage_id", "apv1_1.rev", "apv1_1.revtype", "apv1_1.logistics_voyage_id", "apv1_1.vessel_id", "apv1_1.time_slot_id", "apv1_1.planning_comments", "apv1_1.proposed_vessel", "apv1_1.on_hold", "apv1_1.is_hidden", "apv1_1.freight_comments", "apv1_1.holding_comments"],
"Index Cond": "(apv1_1.logistics_voyage_id = bv15.logistics_voyage_id)",
"Rows Removed by Index Recheck": 0,
"Shared Hit Blocks": 1328146,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
},
{
"Node Type": "Index Only Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "idx_rev_id_updated",
"Relation Name": "revision",
"Schema": "voyage_audit_own",
"Alias": "ar3",
"Startup Cost": 0.43,
"Total Cost": 1.83,
"Plan Rows": 1,
"Plan Width": 6,
"Actual Startup Time": 0.002,
"Actual Total Time": 0.002,
"Actual Rows": 1,
"Actual Loops": 1376065,
"Output": ["ar3.revision_id", "ar3.updated_on"],
"Index Cond": "((ar3.revision_id = apv1_1.rev) AND (ar3.updated_on < '2023-12-12 00:00:00'::timestamp without time zone))",
"Rows Removed by Index Recheck": 0,
"Heap Fetches": 0,
"Shared Hit Blocks": 4128197,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
}
]
}
]
}
]
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "pk_time_slot",
"Relation Name": "time_slot",
"Schema": "voyage_own",
"Alias": "pts",
"Startup Cost": 0.28,
"Total Cost": 0.29,
"Plan Rows": 1,
"Plan Width": 33,
"Actual Startup Time": 0.002,
"Actual Total Time": 0.002,
"Actual Rows": 1,
"Actual Loops": 20887,
"Output": ["pts.time_slot_id", "pts.time_slot_text", "pts.time_slot_from", "pts.time_slot_to"],
"Index Cond": "(pts.time_slot_id = apv1.time_slot_id)",
"Rows Removed by Index Recheck": 0,
"Shared Hit Blocks": 62661,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
}
]
}
]
},
{
"Node Type": "Aggregate",
"Strategy": "Plain",
"Partial Mode": "Simple",
"Parent Relationship": "SubPlan",
"Subplan Name": "SubPlan 2",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 2.65,
"Total Cost": 2.65,
"Plan Rows": 1,
"Plan Width": 8,
"Actual Startup Time": 0.003,
"Actual Total Time": 0.003,
"Actual Rows": 1,
"Actual Loops": 20887,
"Output": ["count(*)"],
"Shared Hit Blocks": 87046,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "sys_c00163132",
"Relation Name": "logistics_schedule",
"Schema": "voyage_audit_own",
"Alias": "s10",
"Startup Cost": 0.42,
"Total Cost": 2.64,
"Plan Rows": 1,
"Plan Width": 0,
"Actual Startup Time": 0.002,
"Actual Total Time": 0.002,
"Actual Rows": 0,
"Actual Loops": 20887,
"Output": ["s10.logistics_schedule_id", "s10.rev", "s10.revtype", "s10.is_active", "s10.lja_rate", "s10.location_id", "s10.logistics_eta", "s10.logistics_voyage_id", "s10.port_agent_id", "s10.schedule_reference", "s10.schedule_type", "s10.time_slot_id"],
"Index Cond": "((s10.logistics_schedule_id = als.logistics_schedule_id) AND (s10.rev > als.rev))",
"Rows Removed by Index Recheck": 0,
"Filter": "((s10.is_active <> als.is_active) OR (s10.location_id <> als.location_id))",
"Rows Removed by Filter": 1,
"Shared Hit Blocks": 87046,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
},
{
"Node Type": "Aggregate",
"Strategy": "Plain",
"Partial Mode": "Simple",
"Parent Relationship": "SubPlan",
"Subplan Name": "SubPlan 3",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 2.52,
"Total Cost": 2.53,
"Plan Rows": 1,
"Plan Width": 8,
"Actual Startup Time": 0.002,
"Actual Total Time": 0.002,
"Actual Rows": 1,
"Actual Loops": 20887,
"Output": ["count(*)"],
"Shared Hit Blocks": 41834,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "sys_c00163119",
"Relation Name": "logistics_cargo",
"Schema": "voyage_audit_own",
"Alias": "c10",
"Startup Cost": 0.29,
"Total Cost": 2.52,
"Plan Rows": 1,
"Plan Width": 0,
"Actual Startup Time": 0.001,
"Actual Total Time": 0.001,
"Actual Rows": 0,
"Actual Loops": 20887,
"Output": ["c10.logistics_cargo_id", "c10.rev", "c10.revtype", "c10.is_active", "c10.loading_coordinator_id", "c10.logistics_voyage_id", "c10.commodity_id", "c10.quantity", "c10.schedule_reference", "c10.tolerance"],
"Index Cond": "((c10.logistics_cargo_id = ac.logistics_cargo_id) AND (c10.rev > ac.rev))",
"Rows Removed by Index Recheck": 0,
"Filter": "((c10.commodity_id <> ac.commodity_id) OR (c10.quantity <> ac.quantity) OR (COALESCE(c10.tolerance, '0'::numeric) <> COALESCE(ac.tolerance, '0'::numeric)) OR (c10.is_active <> ac.is_active))",
"Rows Removed by Filter": 0,
"Shared Hit Blocks": 41834,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
}
]
}
]
}
]
}
]
},
"Settings": {
"effective_cache_size": "13115000kB",
"random_page_cost": "1.1",
"search_path": "voyage_own, public, \"$user\"",
"temp_buffers": "128MB",
"work_mem": "64MB"
},
"Query Identifier": 3182917706061094005,
"Planning": {
"Shared Hit Blocks": 1544,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000
},
"Planning Time": 11.361,
"Triggers": [
],
"Execution Time": 7066.940
}
]
