Stats for inheritance trees
От | Tom Lane |
---|---|
Тема | Stats for inheritance trees |
Дата | |
Msg-id | 2674.1262040064@sss.pgh.pa.us обсуждение исходный текст |
Ответы |
Re: Stats for inheritance trees
Re: Stats for inheritance trees Re: Stats for inheritance trees |
Список | pgsql-hackers |
Following up on the discussion here http://archives.postgresql.org/message-id/4B3875C6020000250002D97D@gw.wicourts.gov I'd like to propose making the following changes that would allow saner planning for queries involving inheritance: 1. Currently the primary key of pg_statistic is (starelid, staattnum) indicating the table and column the stats entry is for. I propose adding a bool stainherit to the pkey. "false" means the stats entry is for just that table column, ie, the traditional interpretation. "true" means the stats entry covers that column and all its inheritance children. Such entries could be used directly by the planner in cases where it currently punts and delivers a default estimate. 2. When ANALYZE is invoked on a table that has inheritance children, it will perform its normal duties for just that table (creating or updating entries with stainherit = false) and then perform a second scan that covers that table and all its children. This will be used to create or update entries with stainherit = true. It might be possible to avoid scanning the parent table itself twice, but I won't contort the code too much to avoid that, since in most practical applications the parent is empty or small anyway. 3. Ideally autovacuum would know enough to perform ANALYZEs on inheritance parents after enough churn has occurred in their child table(s). I am not entirely clear about a good way to do that. We could have it just directly force an ANALYZE on parent(s) of any table it has chosen to ANALYZE, but that might be overkill --- in particular leading to excess ANALYZEs when several children receive a lot of updates. Even without a really smart solution to #3, this would be a big step forward for inheritance queries. BTW, while at it I'm inclined to add a non-unique index on pg_inherits.inhparent, so that find_inheritance_children won't have to seqscan pg_inherits anymore. It's surprising people haven't complained about that before. The code says * XXX might be a good idea to create an index on pg_inherits' inhparent * field, so that we can use an indexscan insteadof sequential scan here. * However, in typical databases pg_inherits won't have enough entries to * justifyan indexscan... but we've long since learned that people stress databases in odd ways. Comments? regards, tom lane
В списке pgsql-hackers по дате отправления: