Gregory Stark wrote:
Thanks. If the tables were in a tablespace that was stored on something that
looked like a conventional filesystem would the server code be prepared to wait
the minutes that it took the operating system and FUSE implementation to load
the tables onto disc?
Ah, I see what you mean now. I think you might have a problem with the planner
opening the files to do an lseek to measure how large they are. I'm not sure
if that gets triggered before or after constraint exclusion. That's the only
problem I can think of.
The size could be stored in the catalogue though. However at that point I
guess that anything that was used before constraint exclusion would have to be
in the catalogue and anything after would have to initiate retrieval from
tertiary media if it's not already cached.
The earlier work e.g. http://www.vldb.org/conf/1996/P156.PDF apparently warned
the planner about long-latency devices but that's probably unnecessary if the
application program was aware that a table had been partitioned by age and
accessing old data could be slow.
Well it's not like there are any alternative plans that will avoid the need to
access the data at all. I assume the FUSE setup will always have to load the
entire file so there's no even any difference between indexed and sequential
access. (Unless the table is over 1G in which case you might want to avoid
sequential scans if index scans would avoid accessing some segments.)
I'd imagine in most cases that sequential scan time would be dwarfed by
medium-load and seek time. It would be important here that the server didn't
time out assuming that it had hit a hardware problem when in actual fact the
table was still being pulled from tape.
I'd presume that when Sarawagi (who I believe is now with IBM) was doing the
work that there wasn't a straightforward way to partition tables (as is
currently described in section 5.9 of the manual) so she had to add internal
hooks. Now granted that I don't pretend to really understand how things work
(I'm a luser, not a guru) but it seems to me that it would not be difficult to
extend the tablespace definition from
CREATE TABLESPACE tablespacename LOCATION '/directory'
to something like
CREATE TABLESPACE tablespacename LOCATION '|check_loaded.pl /directory'
where the check_loaded.pl script could check that the table was cached and
return its name when available. However I guess that the script would probably
need to see the initial lseek or whatever as well... there's probably a whole
lot of non-obvious details that I've totally overlooked.
Just my 2d-worth :-)
--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk
[Opinions above are the author's, not those of his employers or colleagues]
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
