Projections for Catalog tables in Vertica

Moderator: NorbertKrupa

Post Reply
Posts: 7
Joined: Fri Sep 20, 2013 9:05 am

Projections for Catalog tables in Vertica

Post by pk132007 » Thu Sep 26, 2013 9:20 am

We have a requirement where we calling V_CATALOG.VIEW_COLUMNS catalog table iteratively inside a script to get the metadata for aroung 300 columns.

We are quering V_CATALOG.VIEW_COLUMNS table one by one for each column.
The query is taking a lot of time to fetch the data.

Is there any way we can reduce the query time?
Can we ask our DB admin to create projections on catalog table? Is that possible ? Will it help us to reduce the query time ?

Please help.

Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Projections for Catalog tables in Vertica

Post by id10t » Thu Sep 26, 2013 10:30 am


Can you expand your "fetching algorithm/strategy"? What does it mean "one by one for each column":

Code: Select all

1| get names of views
2| for each view:
3|     get names of columns
4|     for each column:
5|          do something()
* Which language used for script? Bash + vsql?
* If bash: Can you use in python?
* If yes: Can you use in pyodbc?

Other strategy: fetch all meta-data, store it separately to Vertica in some repository and after it query a repository and not Vertica.
This strategy can be easily implemented in python: python has built in database - SQLite(for performance SQLite can be in-memory database).
- fetch all meta-data from Vertica
- save it in SQLite (define to be in-memory for performance, it will not take a lot space)
- query SQLite for info about Vertica's meta

It looks complicated, but trust me it is not so much complicated strategy for implementation. I can help.

User avatar
Site Admin
Site Admin
Posts: 1824
Joined: Sat Jan 21, 2012 4:58 am

Re: Projections for Catalog tables in Vertica

Post by JimKnicely » Fri Sep 27, 2013 1:57 pm


I've found the Vertica system tables to be excruciatingly slow at times. At the HP conference in Boston a few months ago, an attendee brought this up at one of the user sessions. The tech from Vertica responded and said that their engineers are aware of this issue and are actively working on a solution for the next release :)

In the mean time, if your views aren't actively changing and are relatively stable, maybe you can create a table yourself called VIEW_COLUMNS_COPY (or something similar) and copy the contents of the V_CATALOG.VIEW_COLUMNS table into your table with an interval relative to how the often your views change?

Then you can query your table for the view meta data which should perform a lot better!

Just a thought...
Jim Knicely


Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Database Development”