The Vertica Forum is getting a makeover! The site will be undergoing maintenance from Tuesday 8/13 to Friday 8/16. We appreciate your patience and cooperation during this time.
Wrong query planner estimates for empty tables
Hi, looking into QUERY_EVENTS table I sometimes see these records: event_type | NO HISTOGRAM event_description | The optimizer encountered a predicate on a column for which it does not have a histogram | event_details | No histogram for db_zdq1m22nx6apb34mk6jdzal1wjv2et8n.qt_40b2c11df5d00551402b043460a51.def_5bbebebccbfc7ca2a0bfc06f40e56d5e suggested_action | analyze_statistics('db_zdq1m22nx6apb34mk6jdzal1wjv2et8n.qt_40b2c11df5d00551402b043460a51.def_5bbebebccbfc7ca2a0bfc06f40e56d5e'); The point is that qt_40b2c11df5d00551402b043460a51 table in question has been in fact analyzed but it is empty. But query planner cannot see any statistics about it and so assumes that the table has 10k rows. This could lead to inefficient execution plan or to quite a large overestimation of memory usage for the query, possibly postponing it due to currently insufficient resources (when the system is under heavy load). Any ideas how big problem this could (when using e.g. 3 or more such empty tables in query), and especially what to do with it? Note that in our application built on Vertica we use several CREATE TABLE AS SELECT statements depending on each other so we cannot tell in advance whether those intermediate tables will be empty or not.