The following bug has been logged online: Bug reference: 3658 Logged by: Alessandra Bilardi Email address: [EMAIL PROTECTED] 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/ ------------------------------- ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
