Inefficiency in recent pgtz patch

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Magnus Hagander" <mha(at)sollentuna(dot)net>
Cc: pgsql-hackers(at)postgreSQL(dot)org
Subject: Inefficiency in recent pgtz patch
Date: 2005-05-28 20:40:38
Message-ID: 28648.1117312838@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I have just noticed that this patch:

* Implements a hash-table cache of loaded tables, so we don't have
to read and parse the TZ file everytime we change a timezone. While
not necesasry now (we don't change timezones very often), I beleive
this will be necessary (or at least good) when "multiple timezones
in the same query" is eventually implemented. And code-wise, this
was the time to do it.

balloons the working store of every backend process by something over 5
megabytes:

Timezones: 5678768 total in 19 blocks; 3904 free (0 chunks); 5674864 used

The reason is that during postmaster start we load every single timezone
definition in the zic database whilst searching for the one that matches
the system timezone most closely. Because pg_tzset is designed to
unconditionally put every loaded timezone into the hashtable, that means
the postmaster ends up with copies of everything, and then all that junk
is inherited via fork by every backend.

I'm not sure about the actual cost of inheriting data that we (probably)
don't ever change, but this seems like a bad idea in principle,
considering that 99% of backends will likely have a use for only one
timezone value.

Please fix it. Possibly pg_tzset could take an extra argument
indicating whether or not to cache the data. Or maybe score_timezone
shouldn't use pg_tzset.

regards, tom lane

Browse pgsql-hackers by date

  From Date Subject
Next Message Gregory Maxwell 2005-05-28 22:25:24 Bloom Filter indexes?
Previous Message Tom Lane 2005-05-28 19:04:46 Re: [HACKERS] patches for items from TODO list