From: | tv(at)fuzzy(dot)cz |
---|---|
To: | "Jorge Arévalo" <jorge(dot)arevalo(at)deimos-space(dot)com> |
Cc: | "pgsql-general" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Join query including two generate_series calls causes big memory growth and crash |
Date: | 2011-04-20 15:35:54 |
Message-ID: | 3c9bf0ddeb0235905413afc75955871e.squirrel@sq.gransy.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> Hello,
>
> I'm executing this query:
>
> SELECT x, y, another_field FROM generate_series(1, 10) x,
> generate_series(1, 10) y, my_table
Well, do you realize this is a cartesian product that gives
10 x 10 x 360000 = 36.000.000
rows in the end. Not sure how wide is the third table (how many columns
etc.) but this may occupy a lot of memory.
> The field 'another_field' belongs to 'my_table'. And that table has
> 360000 entries. In a 64 bits machine, with 4GB RAM, Ubuntu 10.10 and
> postgres 8.4.7, the query works fine. But in a 32 bits machine, with
> 1GB RAM, Ubuntu 9.10 and postgres 8.4.7, the query process is killed
> after taking about 80% of available memory. In the 64 bits machine the
> query takes about 60-70% of the available memory too, but it ends.
> And this happens even if I simply get x and y:
>
> SELECT x, y FROM generate_series(1, 10) x, generate_series(1, 10) y,
> my_table
The result is still 36 million rows, so there's not a big difference I guess.
> Is it normal? I mean, postgres has to deal with millions of rows, ok,
> but shouldn't it start swapping memory instead of crashing? Is a
> question of postgres configuration?
I guess that's the OOM killer, killing one of the processes. See this
http://en.wikipedia.org/wiki/Out_of_memory
so it's a matter of the system, not PostgreSQL - the kernel decides
there's not enough memory, chooses one of the processes and kills it.
PostgreSQL is a victim in this case.
Tomas
From | Date | Subject | |
---|---|---|---|
Next Message | Emi Lu | 2011-04-20 15:51:25 | How to realize ROW_NUMBER() in 8.3? |
Previous Message | Tom Lane | 2011-04-20 15:34:48 | Re: Join query including two generate_series calls causes big memory growth and crash |