citusdata / citusdata/cstore_fdw
performance issue with views and functions.
- Dominant language
- C
- Stars
- 1.8k
- Forks
- 169
- PR merge metrics
- No merged PRs in 30d
Description
Hi, I was testing cstore_fdw against plain pg table and noticed this:
```sql
pg=# create foreign table cstore_test (time timestamp,meter_id int,data real) server cstore_server options(compression'pglz');
CREATE FOREIGN TABLE
Time: 31.434 ms
pg=# INSERT INTO cstore_test SELECT "time", FLOOR(RANDOM() * 10), 10- (RANDOM() * 20) FROM GENERATE_SERIES(NOW() - INTERVAL '1 year', NOW(), INTERVAL '1 seconds') TIME;
INSERT 0 31536001
Time: 41724.510 ms (00:41.725)
pg=# CREATE OR REPLACE FUNCTION increment(i real) RETURNS real AS $$
pg$# BEGIN
pg$# RETURN i + 1;
pg$# END;
pg$# $$ LANGUAGE plpgsql;
CREATE FUNCTION
Time: 41.641 ms
sirate=# create view cstore_view as select time,meter_id,increment(data) from cstore_test;
CREATE VIEW
Time: 63.024 ms
pg=# select time,meter_id,increment(data) from cstore_test where time > '2018-03-16 23:55:00' and meter_id = 1;
time | meter_id | increment
----------------------------+----------+--------------
2018-03-18 04:40:57.048955 | 1 | 9.6884
2018-03-18 04:41:21.048955 | 1 | -4.72585
2018-03-18 04:41:31.048955 | 1 | 2.50237
2018-03-18 04:41:34.048955 | 1 | -3.65364
2018-03-18 04:41:35.048955 | 1 | -0.16955
2018-03-18 04:42:07.048955 | 1 | -5.81057
2018-03-18 04:42:13.048955 | 1 | 5.37638
2018-03-18 04:42:23.048955 | 1 | -2.19258
Time: 127.635 ms
pg=# select * from cstore_view where time > '2018-03-16 23:55:00' and meter_id = 1;
time | meter_id | data
----------------------------+----------+--------------
2018-03-18 04:40:57.048955 | 1 | 9.6884
2018-03-18 04:41:21.048955 | 1 | -4.72585
2018-03-18 04:41:31.048955 | 1 | 2.50237
2018-03-18 04:41:34.048955 | 1 | -3.65364
2018-03-18 04:41:35.048955 | 1 | -0.16955
2018-03-18 04:42:07.048955 | 1 | -5.81057
2018-03-18 04:42:13.048955 | 1 | 5.37638
2018-03-18 04:42:23.048955 | 1 | -2.19258
Time: 2784.645 ms (00:02.785)
```
And after I inserted another 31M rows the times got twice as bad: `Time: 5239.377 ms (00:05.239)`.
If using vanilla tables it's about the same time using query or view. `Time: 4424.318 ms (00:04.424)`
Contributor guide
No contributing guide indexed for this repository
Research direction
The issue names no source files, tests, or entry points. Start by reproducing the direct-query and view timings from the supplied SQL against cstore_fdw and a plain PostgreSQL table, including after inserting another 31M rows; done means identifying and resolving the view/function performance gap.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- c, postgresql
- Domain
- databases, performance
- Issue type
- Bug
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Stale
- Clarity
- Needs clarification
- Newbie friendliness
- 20/100