Hi
maybe i should give you some more explanations of my problem.
The reason for which i think that postgresql run out of memory is that: I have a relation with 6 fields, 29 indexes and 32000 registers, the registers where made up using a pgsql language to save disk space, and they "work" (see the table schema under those lines)
Column | Type | Modifiers
--------+--------------+-----------
id | integer |
p1 | character(1) |
p4 | character(1) |
p6 | character(1) |
p7 | character(1) |
p9 | character(1) |
Indexes:
"h2iab" btree (idr(p1, p4, p6, p7, p9, 'H-2*IAb'::character varying))
"h2iad" btree (idr(p1, p4, p6, p7, p9, 'H-2*IAd'::character varying))
"h2iak" btree (idr(p1, p4, p6, p7, p9, 'H-2*IAk'::character varying))
"h2ied" btree (idr(p1, p4, p6, p7, p9, 'H-2*IEd'::character varying))
"hladqa10501" btree (idr(p1, p4, p6, p7, p9, 'HLA-DQA1*0501'::character varying))
"hladqb10201" btree (idr(p1, p4, p6, p7, p9, 'HLA-DQB1*0201'::character varying))
"hladr" btree (idr(p1, p4, p6, p7, p9, 'HLA-DR'::character varying))
"hladr1" btree (idr(p1, p4, p6, p7, p9, 'HLA-DR1'::character varying))
"hladr13" btree (idr(p1, p4, p6, p7, p9, 'HLA-DR13'::character varying))
"hladr3" btree (idr(p1, p4, p6, p7, p9, 'HLA-DR3'::character varying))
"hladr7" btree (idr(p1, p4, p6, p7, p9, 'HLA-DR7'::character varying))
"hladrb10101" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101'::character varying))
"hladrb10102" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0102'::character varying))
"hladrb10301" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0301'::character varying))
"hladrb10302" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0302'::character varying))
"hladrb10401" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0401'::character varying))
"hladrb10402" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0402'::character varying))
"hladrb10701" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0701'::character varying))
"hladrb10802" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0802'::character varying))
"hladrb10901" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0901'::character varying))
"hladrb11101" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1101'::character varying))
"hladrb11102" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1102'::character varying))
"hladrb11103" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1103'::character varying))
"hladrb11104" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1104'::character varying))
"hladrb11301" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1301'::character varying))
"hladrb11302" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1302'::character varying))
"hladrb11501" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1501'::character varying))
"hladrb40101" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB4*0101'::character varying))
"hladrb50101" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB5*0101'::character varying))
when i do a query as:
select * from precalc where idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101')<-2; it works and return 128030 registers
if i do
select * from precalc where idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101')>-2; 3071970 registers, it don't work
ERROR: relation "pssms" does not exist
CONTEXT: SQL statement "select score from PSSMS where AA= $1 and POS=1 and MOLEC= $2 "
PL/pgSQL function "idr" line 11 at SQL statement
if i ask for explanation for both queries works:
mhc2db=> explain select count(*) from precalc where idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101')<-2;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Aggregate (cost=66188.88..66188.89 rows=1 width=0)
-> Bitmap Heap Scan on precalc (cost=17615.20..63522.21 rows=1066667 width=0)
Recheck Cond: (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101'::character varying) < -2::double precision)
-> Bitmap Index Scan on hladrb10101 (cost=0.00..17348.54 rows=1066667 width=0)
Index Cond: (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101'::character varying) < -2::double precision)
(5 rows)
mhc2db=> explain select count(*) from precalc where idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101')>-2;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Aggregate (cost=66188.88..66188.89 rows=1 width=0)
-> Bitmap Heap Scan on precalc (cost=17615.20..63522.21 rows=1066667 width=0)
Recheck Cond: (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101'::character varying) > -2::double precision)
-> Bitmap Index Scan on hladrb10101 (cost=0.00..17348.54 rows=1066667 width=0)
Index Cond: (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101'::character varying) > -2::double precision)
(5 rows)
and the index used are the correct ones
If for that reason that i think that my machine runs out of memory, by the way, this is not the biggest table that i have others have more than 503000000 registers, so if I try to do a cross select between tables it could be worse.
Any idea?
Thanks
pau
--
Pau Marc Muñoz Torres
Laboratori de Biologia Computacional
Institut de Biotecnologia i Biomedicina Vicent Villar
Universitat Autonoma de Barcelona
E-08193 Bellaterra (Barcelona)
telèfon: 93 5812807
Email : paumarc.munoz@xxxxxxxxxxxxxx
