> > > -> Parallel Seq Scan on pricescope_items (cost=0.00..1027794.01 > rows=3277101 width=522) (actual time=0.753..41654.507 rows=2621681 loops=3) > > Why does it take over 41 seconds to read a table with less than > 3 million rows? Are the rows so large? Is the tabe bloated? > What is the size of the table as measured with pg_relation_size() > and pg_table_size()?
There's one JSON column in each table with a couple fields, and a column with long texts in Items. -- pg_table_size, pg_relation_size, pg_indexes_size, rows nametable_sizerelation_sizeindex_sizerow_estimate tenders 1,775,222,784 1,630,461,952 3,815,567 items 8,158,773,248 6,052,470,784 7,865,043 check_postgres gave a 1.4 bloat score to tenders, 1.9 to items. I had a duplicate index on transaction_id (one hand made, other from the unique constraint) and other text column indexes with 0.3-0.5 bloat scores. After Vacuum Full Analyze; sizes are greatly reduced, specially Items: -- pg_table_size, pg_relation_size, pg_indexes_size, rows nametable_sizerelation_sizeindex_sizerow_estimate tenders 1,203,445,760 1,203,421,184 500,482,048 3,815,567 items 4,436,189,184 4,430,790,656 2,326,118,400 7,865,043 There were a couple mass deletions which probably caused the bloating. Autovacuum is on defaults, but I guess it doesn't take care of that. Still, performance seems about the same. The planner is now using an Index Scan for Colombia without the subselect hack, but subselect takes ~200ms less in avg, so might as well keep doing it. Row estimate is still +1M so still can't use that, but at least now it takes less than 10s to get the exact count with all countries.
