Re: Is there value in having optimizer stats for joins/foreignkeys?

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/