I am running select count() against large table in postgresql, over 10 billion rows. The count() query is taking over 25 minutes to return. Slow is one thing, but that extraordinary and leads to to think there is something wrong within DB, table, ? What might be the cause od such slow performance? DB is AWS Serverless but appears to be adequately resourced (ACU's, etc) Query Plan is below. Query does Parallel Seq Scan that is taking all of time, but why 25 mins?
QUERY PLAN
-----------------------------------------------------------
[ +
{ +
"Plan": { +
"Node Type": "Aggregate", +
"Strategy": "Plain", +
"Partial Mode": "Finalize", +
"Parallel Aware": false, +
"Startup Cost": 222432514.88, +
"Total Cost": 222432514.89, +
"Plan Rows": 1, +
"Plan Width": 8, +
"Actual Startup Time": 1524678.987, +
"Actual Total Time": 1524798.515, +
"Actual Rows": 1, +
"Actual Loops": 1, +
"Shared Hit Blocks": 2605348, +
"Shared Read Blocks": 163231630, +
"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": 1044087497.561, +
"I/O Write Time": 0.000, +
"Plans": [ +
{ +
"Node Type": "Gather", +
"Parent Relationship": "Outer", +
"Parallel Aware": false, +
"Startup Cost": 222432514.67, +
"Total Cost": 222432514.88, +
"Plan Rows": 2, +
"Plan Width": 8, +
"Actual Startup Time": 1524678.978, +
"Actual Total Time": 1524798.508, +
"Actual Rows": 3, +
"Actual Loops": 1, +
"Workers Planned": 2, +
"Workers Launched": 2, +
"Single Copy": false, +
"Shared Hit Blocks": 2605348, +
"Shared Read Blocks": 163231630, +
"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": 1044087497.561, +
"I/O Write Time": 0.000, +
"Plans": [ +
{ +
"Node Type": "Aggregate", +
"Strategy": "Plain", +
"Partial Mode": "Partial", +
"Parent Relationship": "Outer", +
"Parallel Aware": false, +
"Startup Cost": 222431514.67, +
"Total Cost": 222431514.68, +
"Plan Rows": 1, +
"Plan Width": 8, +
"Actual Startup Time": 1524661.376, +
"Actual Total Time": 1524661.376, +
"Actual Rows": 1, +
"Actual Loops": 3, +
"Shared Hit Blocks": 2605348, +
"Shared Read Blocks": 163231630, +
"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": 1044087497.561, +
"I/O Write Time": 0.000, +
"Workers": [ +
], +
"Plans": [ +
{ +
"Node Type": "Seq Scan", +
"Parent Relationship": "Outer", +
"Parallel Aware": true, +
"Relation Name": "alf_node_properties",+
"Alias": "alf_node_properties", +
"Startup Cost": 0.00, +
"Total Cost": 211112638.93, +
"Plan Rows": 4527550293, +
"Plan Width": 0, +
"Actual Startup Time": 1.430, +
"Actual Total Time": 1238163.306, +
"Actual Rows": 3624835814, +
"Actual Loops": 3, +
"Shared Hit Blocks": 2605348, +
"Shared Read Blocks": 163231630, +
"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": 1044087497.561, +
"I/O Write Time": 0.000, +
"Workers": [ +
] +
} +
] +
} +
] +
} +
] +
}, +
"Planning": { +
"Shared Hit Blocks": 225, +
"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": 17.793, +
"Triggers": [ +
], +
"Execution Time": 1524798.554 +
} +
]
(1 row)
I ran Explain Plan on query. Looking at count() in reference to other slow queries involving this table. Also, vacuum/analyze has been run on table as recently as hour before running the count() query. Have monitored instance via AWS RDS monitoring and not seen anything would indicate DB is resource constrained I realize table is large but no query should take 25 minutes to complete
