Accessing pg_timezone_names system view

On Fri, Aug 17, 2007 at 11:51:52PM +1000, Naz Gassiep wrote:

I was wondering if there is any reason that accessing the system view
pg_timezone_names is extremely slow relative to other queries. The
following query:

SELECT * FROM pg_timezone_names;

Executes in between 29ms and 32ms on my server. It takes about the same
when I put a

WHERE name = 'some/timezone'

clause in it. To put this into perspective, on the pages that execute
this, it accounts for something like 3/4 of my DB execution time.

This view is backed by a set returning function that will enumerate all the
files in the timezone directory. The WHERE clause doesn't apply until after
the function has already traversed all files.

As you can see, the execution of that single fetch dwarfs all other
processing loads. I've run this a few times, and the timings are always
roughly the same. Is there a way for me to speed this up? Would I be
better off loading these into a static table and executing from there?

Yes, much better if it's something you're querying regularly.

//Magnus