pg.ddx.io  pgsql-interfaces@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Postmaster getting veeeery big
2+ messages / 2 participants
[nested] [flat]

* Postmaster getting veeeery big
@ 1999-05-18 09:05  Eildert Groeneveld <eg@tzv.fal.de>
  0 siblings, 1 reply; 2+ messages in thread

From: Eildert Groeneveld @ 1999-05-18 09:05 UTC (permalink / raw)
  To: pgsql-interfaces

Hello everyone,
we observe a problem when doing some sort of aggregation like

select distinct count(substr(narodene,5,4)) from raw_field;

(raw_field has 1300000 rows)


On executing this query the postmaster grows in memory size until it runs
out of virtual mem and dies.

Any ideas of what is going on and how to avoid this?

Eildert Groeneveld
ps. RedHat5.2 postgresql-6.4.2-4  
=========================================
Institute for
Animal Science and Animal Behaviour
Mariensee 31535 Neustadt Germany
Tel   : (49)(0)5034 871155
Fax   : (49)(0)5034 92579
www   : http://www.tzv.fal.de/~eg/ 
e-mail: eg@tzv.fal.de
=========================================



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: [INTERFACES] Postmaster getting veeeery big
@ 1999-05-19 14:40  Herouth Maoz <herouth@oumail.openu.ac.il>
  parent: Eildert Groeneveld <eg@tzv.fal.de>
  0 siblings, 0 replies; 2+ messages in thread

From: Herouth Maoz @ 1999-05-19 14:40 UTC (permalink / raw)
  To: eg@tzv.fal.de; pgsql-interfaces

At 12:05 +0300 on 18/05/1999, Eildert Groeneveld wrote:


>
> Hello everyone,
> we observe a problem when doing some sort of aggregation like
>
> select distinct count(substr(narodene,5,4)) from raw_field;
>
> (raw_field has 1300000 rows)
>
>
> On executing this query the postmaster grows in memory size until it runs
> out of virtual mem and dies.
>
> Any ideas of what is going on and how to avoid this?

No matter what the memory problem is (memory used for the substr function
not released, probably), I think your query is wrong.

I think what you wanted was the count of the distinct substrings. But
that's "count distinct" (which is not implemented in PostgreSQL yet) and
not "distinct count". Distinct count only has meaning in a group-by query.

So you should probably rephrase your query as:

SELECT count(*)
FROM raw_field r1
WHERE int4( r1.oid ) = (
   SELECT max( in4(oid) )
   FROM raw_field r2
   WHERE substr( r1.narodene,5,4) = substr( r2.narodene,5,4)
);

The WHERE clause will only become true when the current row has the maximal
oid for the same substring of narodene. In effect, it selects one row for
each substring. Thus you will get the count of the distinct substrings.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~1999-05-19 14:40 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-05-18 09:05 Postmaster getting veeeery big Eildert Groeneveld <eg@tzv.fal.de>
1999-05-19 14:40 ` Herouth Maoz <herouth@oumail.openu.ac.il>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox