citusdata / citusdata/cstore_fdw
Running out of memory with very large queries.
- Dominant language
- C
- Stars
- 1.8k
- Forks
- 169
- PR merge metrics
- No merged PRs in 30d
Description
Hi there folks at citusdata. We quite like the `cstore_fdw` extension and have been experimenting with it on production with very large datasets. However, we started to notice memory errors that may or may not be caused by the way the extension loads data into memory. I wanted to fill this issue to see if this has anything to do with `cstore_fdw`.
We are currently running a relatively large query on a large dataset. It is failing to complete, raising the following error:
```shell
OperationalError: (psycopg2.OperationalError) exceeded maxAllocatedDescs (491) while trying to open file "/var/lib/postgresql/data/cstore_fdw/16384/65667.footer"
```
The query is the following:
```sql
CREATE TABLE foo.baz_20161011_20161101 AS (WITH tizz AS (
WITH all_locs AS ((SELECT * FROM bar.baz_20161011)
UNION ALL
(SELECT * FROM bar.baz_20161012)
UNION ALL
(SELECT * FROM bar.baz_20161013)
UNION ALL
(SELECT * FROM bar.baz_20161014)
UNION ALL
(SELECT * FROM bar.baz_20161015)
UNION ALL
(SELECT * FROM bar.baz_20161016)
UNION ALL
(SELECT * FROM bar.baz_20161017)
UNION ALL
(SELECT * FROM bar.baz_20161018)
UNION ALL
(SELECT * FROM bar.baz_20161019)
UNION ALL
(SELECT * FROM bar.baz_20161020)
UNION ALL
(WITH fizz AS (WITH subset_dates AS (
SELECT *
FROM beez
WHERE start_time >= '2016-10-21'::timestamptz
AND start_time < '2016-10-22'::timestamptz
)
SELECT imei AS user, start_time AS time, cell
FROM subset_dates
WHERE CELL IS NOT NULL AND CELL !='')
SELECT final_time.user, cell
FROM
(SELECT fizz.user, time, cell,
row_number() OVER (PARTITION BY fizz.user ORDER BY time DESC)
AS rank
FROM fizz) AS final_time
WHERE rank = 1
)
UNION ALL
(SELECT * FROM bar.baz_20161022)
UNION ALL
(SELECT * FROM bar.baz_20161023)
UNION ALL
(SELECT * FROM bar.baz_20161024)
UNION ALL
(SELECT * FROM bar.baz_20161025)
UNION ALL
(SELECT * FROM bar.baz_20161026)
UNION ALL
(SELECT * FROM bar.baz_20161027)
UNION ALL
(SELECT * FROM bar.baz_20161028)
UNION ALL
(WITH fizz AS (WITH subset_dates AS (
SELECT *
FROM beez
WHERE start_time >= '2016-10-29'::timestamptz
AND start_time < '2016-10-30'::timestamptz
)
SELECT imei AS user, start_time AS time, cell
FROM subset_dates
WHERE CELL IS NOT NULL AND CELL !='')
SELECT buzz.user, cell
FROM
(SELECT fizz.user, time, cell,
row_number() OVER (PARTITION BY fizz.user ORDER BY time DESC)
AS rank
FROM fizz) AS final_time
WHERE rank = 1
)
UNION ALL
(SELECT * FROM bar.baz_20161030)
UNION ALL
(SELECT * FROM bar.baz_20161031)
UNION ALL
(SELECT * FROM bar.baz_20161101))
```
Each one of the `bar.baz_*` tables is a partition divided by date. Each with about 70 ~ 100M records. There are about 10 columns in each one of those tables, with a range of `NUMERIC`, `TEXT`, and `TIMESTAMPTZ` values.
The machine only has 32Gb of RAM.
Does this error has anything to do with `cstore_fdw`?
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.