Hi,
Thanks to everyone who has worked on and reviewed the join MCV
statistics feature. It's a nice piece of work and addresses a real,
long-standing gap in the planner's estimation for correlated joins.
While testing the v8-* patches, I found a case where ANALYZE on a table
with a join MCV statistics takes ~10 minutes.
Example:
```
CREATE TABLE other_tbl (id serial PRIMARY KEY, fk int);
CREATE INDEX other_tbl_fk_idx ON other_tbl(fk);
INSERT INTO other_tbl (fk) SELECT 1 FROM generate_series(1, 500000);
VACUUM other_tbl;
CREATE TABLE anchor_tbl (id serial PRIMARY KEY, fk int);
INSERT INTO anchor_tbl (fk) SELECT 1 FROM generate_series(1, 20000);
VACUUM anchor_tbl;
\timing on
ANALYZE other_tbl, anchor_tbl;
Time: 36,438 ms
\timing off
CREATE STATISTICS anchor_other_join_stats (mcv)
ON anchor_tbl.fk
FROM anchor_tbl JOIN other_tbl ON anchor_tbl.fk = other_tbl.fk;
\timing on
ANALYZE other_tbl, anchor_tbl;
Time: 566027,223 ms (09:26,027)
\timing off
```
I profiled the running ANALYZE backend. The breakdown is:
sample_index 64.02% (all ANALYZE time)
|-- index_getnext_slot 64.02%
|-- index_fetch_heap 58.25%
| `-- heapam_index_fetch_tuple 56.73%
| |-- LockBuffer 29.47%
| | |-- BufferLockAcquire 8.75%
| | |-- BufferLockUnlock 12.09%
| | `-- BufferLockAttempt 6.39%
| |-- heap_hot_search_buffer 15.88%
| `-- HeapTupleSatisfiesMVCC ~5.2%
`-- index_getnext_tid -> btgettuple
-> _bt_next -> _bt_readnextpage 5.77% <- actual btree
traversal
The overwhelming majority is spent fetching heap tables, buffer locking
for every single index entry examined. I also attached flamegraph of it.
At this point it isn't obvious to me how to cut down that cleanly, so if
anyone can see a good way to address it, I'd very much welcome the input.
--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC,
https://tantorlabs.com/
