The following bug has been logged online:
Bug reference: 3658
Logged by: Alessandra Bilardi
Email address: bilardi@cribi.unipd.it
PostgreSQL version: 8.1.9
Operating system: Linux version 2.6.18-4-vserver-amd64 (Debian
2.6.18.dfsg.1-12etch2)
Description: I've got disk-full errors when insert relational tables.
Details:
Hi all,
I've got disk-full errors when I follow this points:
$ curl -O http://sgdlite.princeton.edu/download/sgdlite/sgdlite.sql.gz
$ createdb sgdlite
$ (gunzip -c sgdlite.sql.gz | psql -d sgdlite -f - ) >& log.load
$ curl -O http://genomics.cribi.unipd.it/~bilardi/data/sgdlite_mart.sql
$ psql -d sgdlite < sgdlite_mart.sql
sgdlite.sql.gz file is one example about chado shema.
sgdlite_feature.sql file is one example about relational tables about
sgdlite database generated by biomart 0.5 script.
Errors relate to one single statement
about sgdlite_feature.sql:
create table sgdlite_mart.TEMP__266 as select a.*,b.dbxref_id as
dbxref_id,b.is_analysis as is_analysis,b.is_obsolete as
is_obsolete,b.md5checksum as md5checksum,b.name as name,b.organism_id as
organism_id,b.residues as residues,b.seqlen as seqlen,b.timeaccessioned
as timeaccessioned,b.timelastmodified as timelastmodified,b.type_id as
type_id,b.uniquename as uniquename from sgdlite_mart.TEMP__265 as a left
join public.feature as b on a.srcfeature_id=b.feature_id;
It seems that Postgres is for some reason unable to perform this join
efficiently. My colleague noticed that just by removing the 'b.name' column
from
the select clause, the statement runs in seconds, but if you include it,
then it takes hours and eventually fills up the disk. This is backed up
by doing an explain query on the original query and the one without
b.name - they give entirely different execution plans.
Can someone help me?
Thanks very much for the help in advance.
Alessandra Bilardi.
-------------------------------
http://genomics.cribi.unipd.it/
-------------------------------