------=_Part_309_18758687.1210240454811
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
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
register=
s
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=3D $1 and
POS=3D=
1 and
MOLEC=3D $2 "
PL/pgSQL function "idr" line 11 at SQL statement
if i ask for explanation for both queries works:
mhc2db=3D> explain select count(*) from precalc where idr(p1, p4, p6, p7,
p=
9,
'HLA-DRB1*0101')<-2;
QUERY PLAN
---------------------------------------------------------------------------=
-------------------------------------
Aggregate (cost=3D66188.88..66188.89 rows=3D1 width=3D0)
-> Bitmap Heap Scan on precalc (cost=3D17615.20..63522.21
rows=3D10666=
67
width=3D0)
Recheck Cond: (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101'::character
varying) < -2::double precision)
-> Bitmap Index Scan on hladrb10101 (cost=3D0.00..17348.54
rows=3D1066667 width=3D0)
Index Cond: (idr(p1, p4, p6, p7, p9,
'HLA-DRB1*0101'::character varying) < -2::double precision)
(5 rows)
mhc2db=3D> explain select count(*) from precalc where idr(p1, p4, p6, p7,
p=
9,
'HLA-DRB1*0101')>-2;
QUERY PLAN
---------------------------------------------------------------------------=
-------------------------------------
Aggregate (cost=3D66188.88..66188.89 rows=3D1 width=3D0)
-> Bitmap Heap Scan on precalc (cost=3D17615.20..63522.21
rows=3D10666=
67
width=3D0)
Recheck Cond: (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101'::character
varying) > -2::double precision)
-> Bitmap Index Scan on hladrb10101 (cost=3D0.00..17348.54
rows=3D1066667 width=3D0)
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
--=20
Pau Marc Mu=F1oz Torres
Laboratori de Biologia Computacional
Institut de Biotecnologia i Biomedicina Vicent Villar
Universitat Autonoma de Barcelona
E-08193 Bellaterra (Barcelona)
tel=E8fon: 93 5812807
Email : paumarc.munoz@[EMAIL PROTECTED]
text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Hi<br><br>maybe i should give you some more explanations of my
problem.<br>=
<br>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
regis=
ters where made up using a pgsql language to save disk space, and they
&quo=
t;work" (see the table schema under those lines)<br>
<br><br><br> Column |
Type &n=
bsp; |
Modifiers<br>--------+--------------+-----------<br> id &n=
bsp; | integer
|<br> p1 =
; | character(1) |<br> p4 |
=
character(1) |<br>
p6 | character(1)
|<br> p7 &n=
bsp; | character(1) |<br> p9 |
character=
(1) |<br>Indexes:<br> "h2iab" btree (idr(p1,
p4=
, p6, p7, p9, 'H-2*IAb'::character varying))<br>
=
"h2iad" btree (idr(p1, p4, p6, p7, p9,
'H-2*IAd'::charact=
er varying))<br>
"h2iak" btree (idr(p1, p4, p6, p7, p9,
'H-=
2*IAk'::character varying))<br> "h2ied"
btr=
ee (idr(p1, p4, p6, p7, p9, 'H-2*IEd'::character
varying))<br> =
; "hladqa10501" btree (idr(p1, p4, p6, p7, p9,
'H=
LA-DQA1*0501'::character varying))<br>
"hladqb10201" btree (idr(p1, p4, p6, p7, p9,
&=
#39;HLA-DQB1*0201'::character varying))<br>
"hla=
dr" btree (idr(p1, p4, p6, p7, p9, 'HLA-DR'::character
varying=
))<br> "hladr1" btree (idr(p1, p4, p6, p7, p9,
=
'HLA-DR1'::character varying))<br>
"hladr13" btree (idr(p1, p4, p6, p7, p9,
'=
HLA-DR13'::character varying))<br>
"hladr3"=
btree (idr(p1, p4, p6, p7, p9, 'HLA-DR3'::character
varying))<br>&=
nbsp; "hladr7" btree (idr(p1, p4, p6, p7, p9,
'HL=
A-DR7'::character varying))<br>
"hladrb10101" btree (idr(p1, p4, p6, p7, p9,
&=
#39;HLA-DRB1*0101'::character varying))<br>
"hla=
drb10102" btree (idr(p1, p4, p6, p7, p9,
'HLA-DRB1*0102'::char=
acter varying))<br> "hladrb10301" btree
(idr(p1=
, p4, p6, p7, p9, 'HLA-DRB1*0301'::character varying))<br>
"hladrb10302" btree (idr(p1, p4, p6, p7, p9,
&=
#39;HLA-DRB1*0302'::character varying))<br>
"hla=
drb10401" btree (idr(p1, p4, p6, p7, p9,
'HLA-DRB1*0401'::char=
acter varying))<br> "hladrb10402" btree
(idr(p1=
, p4, p6, p7, p9, 'HLA-DRB1*0402'::character varying))<br>
"hladrb10701" btree (idr(p1, p4, p6, p7, p9,
&=
#39;HLA-DRB1*0701'::character varying))<br>
"hla=
drb10802" btree (idr(p1, p4, p6, p7, p9,
'HLA-DRB1*0802'::char=
acter varying))<br> "hladrb10901" btree
(idr(p1=
, p4, p6, p7, p9, 'HLA-DRB1*0901'::character varying))<br>
"hladrb11101" btree (idr(p1, p4, p6, p7, p9,
&=
#39;HLA-DRB1*1101'::character varying))<br>
"hla=
drb11102" btree (idr(p1, p4, p6, p7, p9,
'HLA-DRB1*1102'::char=
acter varying))<br> "hladrb11103" btree
(idr(p1=
, p4, p6, p7, p9, 'HLA-DRB1*1103'::character varying))<br>
"hladrb11104" btree (idr(p1, p4, p6, p7, p9,
&=
#39;HLA-DRB1*1104'::character varying))<br>
"hla=
drb11301" btree (idr(p1, p4, p6, p7, p9,
'HLA-DRB1*1301'::char=
acter varying))<br> "hladrb11302" btree
(idr(p1=
, p4, p6, p7, p9, 'HLA-DRB1*1302'::character varying))<br>
"hladrb11501" btree (idr(p1, p4, p6, p7, p9,
&=
#39;HLA-DRB1*1501'::character varying))<br>
"hla=
drb40101" btree (idr(p1, p4, p6, p7, p9,
'HLA-DRB4*0101'::char=
acter varying))<br> "hladrb50101" btree
(idr(p1=
, p4, p6, p7, p9, 'HLA-DRB5*0101'::character varying))<br>
<br><br><br><br>when i do a query as:<br><br>select * from precalc where
id=
r(p1, p4, p6, p7, p9, 'HLA-DRB1*0101')<-2; it works and return
1=
28030 registers<br><br>if i do <br><br>select * from precalc where idr(p1,
=
p4, p6, p7, p9, 'HLA-DRB1*0101')>-2; 3071970 registers,
it=
don't work<br>
ERROR: relation "pssms" does not exist<br>CONTEXT:
SQ=
L statement "select score from PSSMS where AA=3D $1 and POS=3D1
=
and MOLEC=3D $2 "<br>PL/pgSQL function "idr" line 11 at SQL
=
statement<br><br>
if i ask for explanation for both queries works:<br><br>mhc2db=3D>
expla=
in select count(*) from precalc where idr(p1, p4, p6, p7, p9,
'HLA-DRB1=
*0101')<-2;<br>  =
; &n=
bsp;  =
; &n=
bsp; QUERY PLAN<br>
---------------------------------------------------------------------------=
-------------------------------------<br> Aggregate
(cost=3D6618=
8.88..66188.89 rows=3D1 width=3D0)<br> -> Bitmap Heap
=
Scan on precalc (cost=3D17615.20..63522.21 rows=3D1066667
width=3D0)<=
br>
Recheck Cond: (idr(p1,
p4,=
p6, p7, p9, 'HLA-DRB1*0101'::character varying) < -2::double
pr=
ecision)<br> ->
Bi=
tmap Index Scan on hladrb10101 (cost=3D0.00..17348.54 rows=3D1066667
=
width=3D0)<br> &=
nbsp; Index Cond: (idr(p1, p4, p6, p7, p9,
'HLA-DRB1*=
0101'::character varying) < -2::double precision)<br>
(5 rows)<br><br>mhc2db=3D> explain select count(*) from precalc where
id=
r(p1, p4, p6, p7, p9,
'HLA-DRB1*0101')>-2;<br> =
&nb=
sp; =
&nb=
sp; QUERY
PLAN<br>---=
---------------------------------------------------------------------------=
----------------------------------<br>
Aggregate (cost=3D66188.88..66188.89 rows=3D1
width=3D0)<br>&nb=
sp; -> Bitmap Heap Scan on precalc
(cost=3D17615.20..6=
3522.21 rows=3D1066667
width=3D0)<br> &n=
bsp; Recheck Cond: (idr(p1, p4, p6, p7, p9,
'HLA-DRB1*0101'::=
character varying) > -2::double precision)<br>
-> Bitmap Index
S=
can on hladrb10101 (cost=3D0.00..17348.54 rows=3D1066667
width=3D0)<b=
r> &=
nbsp; Index Cond: (idr(p1, p4, p6, p7, p9,
'HLA-DRB1*0101'::c=
haracter varying) > -2::double precision)<br>
(5 rows)<br><br>and the index used are the correct ones<br><br>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
register=
s, so if I try to do a cross select between tables it could be worse.<br>
<br><br>Any idea?<br> <br>Thanks<br><br>pau<br clear=3D"all"><br>--
<b=
r>Pau Marc Mu=F1oz Torres<br><br>Laboratori de Biologia Computacional
<br>I=
nstitut de Biotecnologia i Biomedicina Vicent Villar <br>Universitat
Autono=
ma de Barcelona<br>
E-08193 Bellaterra (Barcelona)<br> <br>tel=E8fon: 93 5812807<br>Email :
pau=
marc.munoz@[EMAIL PROTECTED]
|
