On Mon, Nov 20, 2017 at 9:36 AM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
9.6.what exactly?
9.6.5
The only thing I can think of offhand that could create temp files far in
excess of the actual data volume is if a hash join repeatedly decides that
it needs to increase the number of hash batches. We have seen that happen
in cases where an inner relation contains a vast number of identical hash
key values: they all go into the same bucket, and the executor keeps
splitting the batch trying to reduce its size, but being all identical
values it will never be the case that some rows go into a different batch
than others. There is logic in there to give up splitting when this
happens, but maybe it's not firing in your case, or maybe somebody broke
it recently :-(.
I think this is exactly the scenario that happened. More below.
I find it suspicious that your plan involves two separate hash joins both
of which have a much larger table on the inside than the outside ---
offhand that does not look very sane. So there's also a question of
why did the planner do that.
What can you tell us about the distributions of the join keys for the two
hash joins --- are those keys unique, or a bit less than unique, or very
much not unique?
We were able to get the query to run, without using much temp space at all, by eliminating this portion of the query:
LEFT JOIN donation d2
ON mca.parent_id = d2.candidate_id AND mca.parent_id IS NOT NULL AND mca.account_id = d2.account_id
AND d2.account_id NOT IN (1, 2, 3, 195, 196, 81)
The resultant full query plan is attached (json format this time).
What was happening is that there is a fairly non-unique "parent_id" value (66k times) that is creating a very large hashkey that it cannot break into a smaller chunks -- so, essentially what your guess was, Tom. Perhaps worth investigating whether that code is still functioning as intended.
Incidentally, I'd also be interested in any suggestions for refactoring this query for better performance. It does complete now if we exclude the problematic account but still takes quite a bit of time and we expect our dataset to only get bigger.
thanks
--Cory
[
{
"Plan": {
"Node Type": "Aggregate",
"Strategy": "Sorted",
"Partial Mode": "Simple",
"Parallel Aware": false,
"Startup Cost": 18682578.29,
"Total Cost": 18984282.25,
"Plan Rows": 529305,
"Plan Width": 526,
"Actual Startup Time": 827809.462,
"Actual Total Time": 1576769.572,
"Actual Rows": 15170854,
"Actual Loops": 1,
"Output": ["c.account_id", "c.candidate_id", "c.number", "c.pre_direction", "c.street_name", "c.street_suffix", "c.post_direction", "c.secondary_designator", "c.secondary_number", "c.city", "c.state", "c.zipcode", "array_agg(DISTINCT jsonb_build_object('first_name', cp.first_name, 'middle_name', cp.middle_name, 'last_name', cp.last_name, 'suffix', cp.suffix, 'salutation', cp.salutation, 'spouse', cp.spouse))", "array_agg(DISTINCT mce.email) FILTER (WHERE (mce.email IS NOT NULL))", "array_agg(DISTINCT jsonb_build_object('country_code', mcp.country_code, 'national_number', mcp.number)) FILTER (WHERE (mcp.country_code = 1))", "array_agg(DISTINCT jsonb_build_object('donation_amount', d.donation_amount, 'donation_date', COALESCE(d.donation_date, make_date(d.donation_year, 1, 1)), 'charity', d.account_id)) FILTER (WHERE (d.account_id IS NOT NULL))"],
"Group Key": ["c.account_id", "c.candidate_id", "c.number", "c.pre_direction", "c.street_name", "c.street_suffix", "c.post_direction", "c.secondary_designator", "c.secondary_number", "c.city", "c.state", "c.zipcode"],
"Shared Hit Blocks": 15,
"Shared Read Blocks": 1389726,
"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": 2153328,
"Temp Written Blocks": 2292366,
"Plans": [
{
"Node Type": "Unique",
"Parent Relationship": "InitPlan",
"Subplan Name": "CTE candidates",
"Parallel Aware": false,
"Startup Cost": 2312476.04,
"Total Cost": 2666322.40,
"Plan Rows": 5293052,
"Plan Width": 80,
"Actual Startup Time": 80515.358,
"Actual Total Time": 119791.347,
"Actual Rows": 15170854,
"Actual Loops": 1,
"Output": ["match_candidate_address.account_id", "match_candidate_address.candidate_id", "match_candidate_address.parent_id", "match_candidate_address.number", "match_candidate_address.pre_direction", "match_candidate_address.street_name", "match_candidate_address.street_suffix", "match_candidate_address.post_direction", "match_candidate_address.secondary_designator", "match_candidate_address.secondary_number", "match_candidate_address.city", "match_candidate_address.state", "match_candidate_address.zipcode"],
"Shared Hit Blocks": 0,
"Shared Read Blocks": 560457,
"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": 139254,
"Temp Written Blocks": 139254,
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 2312476.04,
"Total Cost": 2337750.78,
"Plan Rows": 10109896,
"Plan Width": 80,
"Actual Startup Time": 80515.357,
"Actual Total Time": 114230.555,
"Actual Rows": 15171416,
"Actual Loops": 1,
"Output": ["match_candidate_address.account_id", "match_candidate_address.candidate_id", "match_candidate_address.parent_id", "match_candidate_address.number", "match_candidate_address.pre_direction", "match_candidate_address.street_name", "match_candidate_address.street_suffix", "match_candidate_address.post_direction", "match_candidate_address.secondary_designator", "match_candidate_address.secondary_number", "match_candidate_address.city", "match_candidate_address.state", "match_candidate_address.zipcode"],
"Sort Key": ["match_candidate_address.account_id", "match_candidate_address.candidate_id", "match_candidate_address.parent_id", "match_candidate_address.number", "match_candidate_address.pre_direction", "match_candidate_address.street_name", "match_candidate_address.street_suffix", "match_candidate_address.post_direction", "match_candidate_address.secondary_designator", "match_candidate_address.secondary_number", "match_candidate_address.city", "match_candidate_address.state", "match_candidate_address.zipcode"],
"Sort Method": "external merge",
"Sort Space Used": 1112696,
"Sort Space Type": "Disk",
"Shared Hit Blocks": 0,
"Shared Read Blocks": 560457,
"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": 139254,
"Temp Written Blocks": 139254,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "match_candidate_address",
"Schema": "public",
"Alias": "match_candidate_address",
"Startup Cost": 0.00,
"Total Cost": 687005.30,
"Plan Rows": 10109896,
"Plan Width": 80,
"Actual Startup Time": 1.436,
"Actual Total Time": 14230.207,
"Actual Rows": 15171416,
"Actual Loops": 1,
"Output": ["match_candidate_address.account_id", "match_candidate_address.candidate_id", "match_candidate_address.parent_id", "match_candidate_address.number", "match_candidate_address.pre_direction", "match_candidate_address.street_name", "match_candidate_address.street_suffix", "match_candidate_address.post_direction", "match_candidate_address.secondary_designator", "match_candidate_address.secondary_number", "match_candidate_address.city", "match_candidate_address.state", "match_candidate_address.zipcode"],
"Filter": "(match_candidate_address.account_id <> 61)",
"Rows Removed by Filter": 0,
"Shared Hit Blocks": 0,
"Shared Read Blocks": 560457,
"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
}
]
}
]
},
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 16016255.90,
"Total Cost": 16029488.53,
"Plan Rows": 5293052,
"Plan Width": 478,
"Actual Startup Time": 827797.541,
"Actual Total Time": 898578.530,
"Actual Rows": 50389867,
"Actual Loops": 1,
"Output": ["c.account_id", "c.candidate_id", "c.number", "c.pre_direction", "c.street_name", "c.street_suffix", "c.post_direction", "c.secondary_designator", "c.secondary_number", "c.city", "c.state", "c.zipcode", "cp.first_name", "cp.middle_name", "cp.last_name", "cp.suffix", "cp.salutation", "cp.spouse", "mce.email", "mcp.country_code", "mcp.number", "d.donation_amount", "d.donation_date", "d.donation_year", "d.account_id"],
"Sort Key": ["c.account_id", "c.candidate_id", "c.number", "c.pre_direction", "c.street_name", "c.street_suffix", "c.post_direction", "c.secondary_designator", "c.secondary_number", "c.city", "c.state", "c.zipcode"],
"Sort Method": "external merge",
"Sort Space Used": 6305976,
"Sort Space Type": "Disk",
"Shared Hit Blocks": 0,
"Shared Read Blocks": 1389709,
"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": 2153328,
"Temp Written Blocks": 2292366,
"Plans": [
{
"Node Type": "Merge Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Right",
"Startup Cost": 13497628.70,
"Total Cost": 14285372.12,
"Plan Rows": 5293052,
"Plan Width": 478,
"Actual Startup Time": 440648.257,
"Actual Total Time": 696973.135,
"Actual Rows": 50389867,
"Actual Loops": 1,
"Output": ["c.account_id", "c.candidate_id", "c.number", "c.pre_direction", "c.street_name", "c.street_suffix", "c.post_direction", "c.secondary_designator", "c.secondary_number", "c.city", "c.state", "c.zipcode", "cp.first_name", "cp.middle_name", "cp.last_name", "cp.suffix", "cp.salutation", "cp.spouse", "mce.email", "mcp.country_code", "mcp.number", "d.donation_amount", "d.donation_date", "d.donation_year", "d.account_id"],
"Merge Cond": "((d.account_id = c.account_id) AND ((d.candidate_id)::text = (c.candidate_id)::text))",
"Shared Hit Blocks": 0,
"Shared Read Blocks": 1389709,
"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": 676964,
"Temp Written Blocks": 816002,
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 5634896.34,
"Total Cost": 5716432.80,
"Plan Rows": 32614586,
"Plan Width": 30,
"Actual Startup Time": 124939.878,
"Actual Total Time": 206392.632,
"Actual Rows": 32727758,
"Actual Loops": 1,
"Output": ["d.donation_amount", "d.donation_date", "d.donation_year", "d.account_id", "d.candidate_id"],
"Sort Key": ["d.account_id", "d.candidate_id"],
"Sort Method": "external merge",
"Sort Space Used": 1427752,
"Sort Space Type": "Disk",
"Shared Hit Blocks": 0,
"Shared Read Blocks": 375627,
"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": 182232,
"Temp Written Blocks": 182232,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "donation",
"Schema": "public",
"Alias": "d",
"Startup Cost": 0.00,
"Total Cost": 784424.80,
"Plan Rows": 32614586,
"Plan Width": 30,
"Actual Startup Time": 0.604,
"Actual Total Time": 10742.364,
"Actual Rows": 32727758,
"Actual Loops": 1,
"Output": ["d.donation_amount", "d.donation_date", "d.donation_year", "d.account_id", "d.candidate_id"],
"Filter": "(d.account_id <> ALL ('{61,195}'::bigint[]))",
"Rows Removed by Filter": 0,
"Shared Hit Blocks": 0,
"Shared Read Blocks": 375627,
"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
}
]
},
{
"Node Type": "Materialize",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 7862732.36,
"Total Cost": 8339466.28,
"Plan Rows": 5293052,
"Plan Width": 458,
"Actual Startup Time": 315574.548,
"Actual Total Time": 460540.933,
"Actual Rows": 50389867,
"Actual Loops": 1,
"Output": ["c.account_id", "c.candidate_id", "c.number", "c.pre_direction", "c.street_name", "c.street_suffix", "c.post_direction", "c.secondary_designator", "c.secondary_number", "c.city", "c.state", "c.zipcode", "mce.email", "mcp.country_code", "mcp.number", "cp.first_name", "cp.middle_name", "cp.last_name", "cp.suffix", "cp.salutation", "cp.spouse"],
"Shared Hit Blocks": 0,
"Shared Read Blocks": 1014082,
"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": 494732,
"Temp Written Blocks": 633770,
"Plans": [
{
"Node Type": "Merge Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Left",
"Startup Cost": 7862732.36,
"Total Cost": 8326233.65,
"Plan Rows": 5293052,
"Plan Width": 458,
"Actual Startup Time": 315574.542,
"Actual Total Time": 449981.947,
"Actual Rows": 17669727,
"Actual Loops": 1,
"Output": ["c.account_id", "c.candidate_id", "c.number", "c.pre_direction", "c.street_name", "c.street_suffix", "c.post_direction", "c.secondary_designator", "c.secondary_number", "c.city", "c.state", "c.zipcode", "mce.email", "mcp.country_code", "mcp.number", "cp.first_name", "cp.middle_name", "cp.last_name", "cp.suffix", "cp.salutation", "cp.spouse"],
"Merge Cond": "((c.account_id = cp.account_id) AND ((c.candidate_id)::text = (cp.candidate_id)::text))",
"Shared Hit Blocks": 0,
"Shared Read Blocks": 1014082,
"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": 494732,
"Temp Written Blocks": 633770,
"Plans": [
{
"Node Type": "Merge Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Left",
"Startup Cost": 4847870.07,
"Total Cost": 5108798.16,
"Plan Rows": 5293052,
"Plan Width": 432,
"Actual Startup Time": 219882.178,
"Actual Total Time": 298562.482,
"Actual Rows": 16565342,
"Actual Loops": 1,
"Output": ["c.account_id", "c.candidate_id", "c.number", "c.pre_direction", "c.street_name", "c.street_suffix", "c.post_direction", "c.secondary_designator", "c.secondary_number", "c.city", "c.state", "c.zipcode", "mce.email", "mcp.country_code", "mcp.number"],
"Merge Cond": "((c.account_id = mcp.account_id) AND ((c.candidate_id)::text = (mcp.candidate_id)::text))",
"Shared Hit Blocks": 0,
"Shared Read Blocks": 818791,
"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": 392171,
"Temp Written Blocks": 531209,
"Plans": [
{
"Node Type": "Merge Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Left",
"Startup Cost": 3187230.42,
"Total Cost": 3320211.67,
"Plan Rows": 5293052,
"Plan Width": 420,
"Actual Startup Time": 179286.777,
"Actual Total Time": 226377.661,
"Actual Rows": 15230295,
"Actual Loops": 1,
"Output": ["c.account_id", "c.candidate_id", "c.number", "c.pre_direction", "c.street_name", "c.street_suffix", "c.post_direction", "c.secondary_designator", "c.secondary_number", "c.city", "c.state", "c.zipcode", "mce.email"],
"Merge Cond": "((c.account_id = mce.account_id) AND ((c.candidate_id)::text = (mce.candidate_id)::text))",
"Shared Hit Blocks": 0,
"Shared Read Blocks": 672809,
"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": 336915,
"Temp Written Blocks": 475953,
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 1655829.81,
"Total Cost": 1669062.44,
"Plan Rows": 5293052,
"Plan Width": 398,
"Actual Startup Time": 136986.864,
"Actual Total Time": 153262.499,
"Actual Rows": 15170854,
"Actual Loops": 1,
"Output": ["c.account_id", "c.candidate_id", "c.number", "c.pre_direction", "c.street_name", "c.street_suffix", "c.post_direction", "c.secondary_designator", "c.secondary_number", "c.city", "c.state", "c.zipcode"],
"Sort Key": ["c.account_id", "c.candidate_id"],
"Sort Method": "external merge",
"Sort Space Used": 1093984,
"Sort Space Type": "Disk",
"Shared Hit Blocks": 0,
"Shared Read Blocks": 560457,
"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": 276169,
"Temp Written Blocks": 415207,
"Plans": [
{
"Node Type": "CTE Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"CTE Name": "candidates",
"Alias": "c",
"Startup Cost": 0.00,
"Total Cost": 105861.04,
"Plan Rows": 5293052,
"Plan Width": 398,
"Actual Startup Time": 80515.364,
"Actual Total Time": 126557.786,
"Actual Rows": 15170854,
"Actual Loops": 1,
"Output": ["c.account_id", "c.candidate_id", "c.number", "c.pre_direction", "c.street_name", "c.street_suffix", "c.post_direction", "c.secondary_designator", "c.secondary_number", "c.city", "c.state", "c.zipcode"],
"Shared Hit Blocks": 0,
"Shared Read Blocks": 560457,
"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": 139254,
"Temp Written Blocks": 278292
}
]
},
{
"Node Type": "Materialize",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 1531400.61,
"Total Cost": 1577754.98,
"Plan Rows": 9270875,
"Plan Width": 39,
"Actual Startup Time": 42299.898,
"Actual Total Time": 60347.623,
"Actual Rows": 9344398,
"Actual Loops": 1,
"Output": ["mce.email", "mce.account_id", "mce.candidate_id"],
"Shared Hit Blocks": 0,
"Shared Read Blocks": 112352,
"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": 60746,
"Temp Written Blocks": 60746,
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 1531400.61,
"Total Cost": 1554577.80,
"Plan Rows": 9270875,
"Plan Width": 39,
"Actual Startup Time": 42299.889,
"Actual Total Time": 58741.647,
"Actual Rows": 9279468,
"Actual Loops": 1,
"Output": ["mce.email", "mce.account_id", "mce.candidate_id"],
"Sort Key": ["mce.account_id", "mce.candidate_id"],
"Sort Method": "external merge",
"Sort Space Used": 485400,
"Sort Space Type": "Disk",
"Shared Hit Blocks": 0,
"Shared Read Blocks": 112352,
"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": 60746,
"Temp Written Blocks": 60746,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "match_candidate_email",
"Schema": "public",
"Alias": "mce",
"Startup Cost": 0.00,
"Total Cost": 205060.75,
"Plan Rows": 9270875,
"Plan Width": 39,
"Actual Startup Time": 0.056,
"Actual Total Time": 2003.713,
"Actual Rows": 9279469,
"Actual Loops": 1,
"Output": ["mce.email", "mce.account_id", "mce.candidate_id"],
"Shared Hit Blocks": 0,
"Shared Read Blocks": 112352,
"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
}
]
}
]
}
]
},
{
"Node Type": "Materialize",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 1660639.65,
"Total Cost": 1711043.77,
"Plan Rows": 10080824,
"Plan Width": 30,
"Actual Startup Time": 40595.393,
"Actual Total Time": 59240.804,
"Actual Rows": 10277512,
"Actual Loops": 1,
"Output": ["mcp.country_code", "mcp.number", "mcp.account_id", "mcp.candidate_id"],
"Shared Hit Blocks": 0,
"Shared Read Blocks": 145982,
"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": 55256,
"Temp Written Blocks": 55256,
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 1660639.65,
"Total Cost": 1685841.71,
"Plan Rows": 10080824,
"Plan Width": 30,
"Actual Startup Time": 40595.373,
"Actual Total Time": 57410.363,
"Actual Rows": 10083395,
"Actual Loops": 1,
"Output": ["mcp.country_code", "mcp.number", "mcp.account_id", "mcp.candidate_id"],
"Sort Key": ["mcp.account_id", "mcp.candidate_id"],
"Sort Method": "external merge",
"Sort Space Used": 441496,
"Sort Space Type": "Disk",
"Shared Hit Blocks": 0,
"Shared Read Blocks": 145982,
"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": 55256,
"Temp Written Blocks": 55256,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "match_candidate_phone",
"Schema": "public",
"Alias": "mcp",
"Startup Cost": 0.00,
"Total Cost": 246790.24,
"Plan Rows": 10080824,
"Plan Width": 30,
"Actual Startup Time": 0.026,
"Actual Total Time": 2711.138,
"Actual Rows": 10083397,
"Actual Loops": 1,
"Output": ["mcp.country_code", "mcp.number", "mcp.account_id", "mcp.candidate_id"],
"Shared Hit Blocks": 0,
"Shared Read Blocks": 145982,
"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
}
]
}
]
}
]
},
{
"Node Type": "Materialize",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 3014862.29,
"Total Cost": 3102389.46,
"Plan Rows": 17505434,
"Plan Width": 44,
"Actual Startup Time": 95692.332,
"Actual Total Time": 132792.466,
"Actual Rows": 19124416,
"Actual Loops": 1,
"Output": ["cp.first_name", "cp.middle_name", "cp.last_name", "cp.suffix", "cp.salutation", "cp.spouse", "cp.account_id", "cp.candidate_id"],
"Shared Hit Blocks": 0,
"Shared Read Blocks": 195291,
"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": 102561,
"Temp Written Blocks": 102561,
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 3014862.29,
"Total Cost": 3058625.88,
"Plan Rows": 17505434,
"Plan Width": 44,
"Actual Startup Time": 95692.327,
"Actual Total Time": 129543.733,
"Actual Rows": 17456375,
"Actual Loops": 1,
"Output": ["cp.first_name", "cp.middle_name", "cp.last_name", "cp.suffix", "cp.salutation", "cp.spouse", "cp.account_id", "cp.candidate_id"],
"Sort Key": ["cp.account_id", "cp.candidate_id"],
"Sort Method": "external merge",
"Sort Space Used": 819472,
"Sort Space Type": "Disk",
"Shared Hit Blocks": 0,
"Shared Read Blocks": 195291,
"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": 102561,
"Temp Written Blocks": 102561,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "candidate_person",
"Schema": "public",
"Alias": "cp",
"Startup Cost": 0.00,
"Total Cost": 370345.34,
"Plan Rows": 17505434,
"Plan Width": 44,
"Actual Startup Time": 1.603,
"Actual Total Time": 4834.571,
"Actual Rows": 17456377,
"Actual Loops": 1,
"Output": ["cp.first_name", "cp.middle_name", "cp.last_name", "cp.suffix", "cp.salutation", "cp.spouse", "cp.account_id", "cp.candidate_id"],
"Shared Hit Blocks": 0,
"Shared Read Blocks": 195291,
"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
}
]
}
]
}
]
}
]
}
]
}
]
}
]
},
"Planning Time": 0.901,
"Triggers": [
],
"Execution Time": 1579882.714
}
]
