Re: Problems with memory

Pau Marc Munoz Torres wrote:
> 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)

You have 29 indexes on a table with 6 columns?
But only 32000 rows?

> 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))
etc.

OK, so you have 29 different functional indexes which use your columns
and then a fixed parameter. Looks odd to me, but I suppose you might
have good reason.

Oh - and it's not necessarily saving you any disk space - the index
values need to be stored.

> 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

Do you have a table/view called pssms in your search-path? Because
that's what the error is about. Might it be a case-sensitive issue - do
you have a table called PSSMS instead?

> 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;
[snip]

> mhc2db=> explain select count(*) from precalc where idr(p1, p4, p6, p7, p9,
> 'HLA-DRB1*0101')>-2;
[snip]

> 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.

For what reason? I still don't see any out-of-memory errors.

--
Richard Huxton
Archonet Ltd