Thread

Faster pg_timezone_names view

Tom Lane <tgl@sss.pgh.pa.us> — 2017-05-01T20:48:26Z

I've been casting about for low-hanging fruit for making the regression
tests run faster.  One thing I noticed is that this query in sysviews.sql
is one of the slowest queries in the whole suite:

select count(distinct utc_offset) >= 24 as ok from pg_timezone_names;

Reading pg_timezone_names takes upwards of 300 msec on my workstation,
and north of 3 seconds on some of my slower buildfarm critters.  I'd
always figured that, since it's reading the whole of the timezone data
directory tree, it's just naturally got to be slow.  However, I chanced
to try strace'ing a scan of pg_timezone_names, and what I saw was just
horrid.  We do something like this for *each* timezone data file:

stat("/usr/share/zoneinfo/posix/America/Iqaluit", {st_mode=S_IFREG|0644, st_size=2000, ...}) = 0
open("/usr/share/zoneinfo", O_RDONLY|O_NONBLOCK|O_DIRECTORY|O_CLOEXEC) = 20
getdents(20, /* 68 entries */, 32768)   = 1968
close(20)                               = 0
open("/usr/share/zoneinfo/posix", O_RDONLY|O_NONBLOCK|O_DIRECTORY|O_CLOEXEC) = 20
getdents(20, /* 62 entries */, 32768)   = 1776
close(20)                               = 0
open("/usr/share/zoneinfo/posix/America", O_RDONLY|O_NONBLOCK|O_DIRECTORY|O_CLOEXEC) = 20
getdents(20, /* 146 entries */, 32768)  = 4696
close(20)                               = 0
open("/usr/share/zoneinfo/posix/America/Iqaluit", O_RDONLY) = 20
read(20, "TZif2\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\10\0\0\0\10\0\0\0\0"..., 54968) = 2000
close(20)                               = 0
open("/usr/share/zoneinfo", O_RDONLY|O_NONBLOCK|O_DIRECTORY|O_CLOEXEC) = 20
getdents(20, /* 68 entries */, 32768)   = 1968
close(20)                               = 0
open("/usr/share/zoneinfo/posixrules", O_RDONLY) = 20
read(20, "TZif2\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\4\0\0\0\4\0\0\0\0"..., 54968) = 3519
close(20)                               = 0

That is, having found a data file by dint of searching the directory tree,
we *repeat the search* from the root of the timezone tree.  And then we
read the posixrules file, in addition to the target zoneinfo file itself.
And just to add insult to injury, we search the directory path down to
posixrules, though fortunately that's just one level down.

There are a couple of things going on here.  One is that pg_open_tzfile()
performs the repeat directory searches we're seeing above, because it is
tasked with accepting a case-insensitive spelling of a timezone name and
returning the correctly-cased zone name as seen in the file system.
That's necessary, and not too expensive, when performing something like
a "SET timezone" command, because we don't insist that the user spell
the zone name canonically in SET.  But it's pretty silly in the context
of a directory scan, because we already know the canonical spelling of
the file path: we just read it from the filesystem.

The second problem is that we don't cache the result of reading
posixrules, even though we need it for practically every zone load.
This seems like a performance bug in the upstream IANA library,
though I'm not sure that they care about use-cases like this one.
(They might also object that they don't want to cache data that
could conceivably change on-disk; but we already cache timezone
data at other levels, so there's no reason not to do it here.)

The attached patch adjusts both of these things.  On my workstation
it reduces the runtime of the pg_timezone_names view by better than
a factor of 3.

If I were to commit this, I'd want to back-patch, because experience
has shown that we don't want any cross-branch variation in the timezone
code files; absorbing upstream fixes is painful enough without that.
But it seems like a pretty safe and helpful thing to back-patch.

Thoughts, objections?

			regards, tom lane