query running slow, not able to update statistics on table and projections
John_Y
✭
We recently migrated our Vertica cluster from version 9 to 23. we did the back up from v9 to another v23 vm cluster, Since then, we've been experiencing significant query performance issues, even with simple joins. Upon examining the explain plan, we've identified numerous instances of "no statistics" issues like this:
| | +-- Outer -> STORAGE ACCESS for XXX [Cost: 3K, Rows: 2M] (PATH ID: 3)
| | | Projection: testj.ubersmith_inv_packs_InvId_PackId_b0
| | | Materialize: XXX.XXX, XXX.XXXid
| | | Filter: (XXX.XXX IS NOT NULL)
| | | Execute on: All Nodes
| | +-- Inner -> STORAGE ACCESS for XXX [Cost: 14K, Rows: 10K (NO STATISTICS)] (PATH ID: 4)
| | | Projection: testj.XXXXX
| | |
| | | Filter: (XXX.XXX IS NOT NULL)
| | | Filter: (XXX.XXX IS NOT NULL)
| | | Execute on: All Nodes
| +-- Inner -> STORAGE ACCESS for INV [Cost: 385M, Rows: 1M (NO STATISTICS)] (PATH ID: 5)
| | Projection: testj.XXXX
We've tried various methods to address this, such as refreshing tables and projections using commands like REFRESH, SELECT ANALYZE_STATISTICS on individual and all tables, but these "no statistics" hinds persist. This is severely impacting our query performance. Could someone please provide guidance on how to resolve this issue? Thank you.
Answers
If you still have the v9 cluster available, get an annotated query plan by running "EXPLAIN ANNOTATED ;" then copy the annotated query to run on the v23 cluster.
If that doesn't help, or the v9 cluster is no longer available, try setting "select set_optimizer_directives('AllowFKInner=true');" then run the query.
Please open a support case if neither approach works. Or, if one or both workarounds work, let us know here.
thank you, but it seems that none of them would work, we still have those 'non statistics' on every table explanation plan, and query speed does not increase