citusdata / citusdata/cstore_fdw
Unexpectedly bad performance
- Dominant language
- C
- Stars
- 1.8k
- Forks
- 169
- PR merge metrics
- No merged PRs in 30d
Description
cstore_fdw 1.6.0 on postgresql-10
So, I've got a lot of time-series data in a table that looks like this:
```
spider=> create foreign table history_y2017 (
itemid bigint not null,
clock integer not null,
value numeric(20,0) not null,
ns integer default 0 not null,
constraint check_clock CHECK ( clock >= 1483228800 AND clock < 1514764800)
)
SERVER cstore_server OPTIONS(compression 'pglz');
insert into history_y2017 select * from history where clock >= 1483228800 AND clock < 1514764800 order by itemid,clock,ns;
analyze history_y2017;
```
```
spider=> explain analyze verbose select * from history_y2017 where itemid=1464 and clock 1497582092 and clock <= 1497668492;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Foreign Scan on public.history_y2017 (cost=0.00..12087188.56 rows=851 width=20) (actual time=995.497..1276.525 rows=1440 loops=1)
Output: itemid, clock, value, ns
Filter: ((history_y2017.clock > 1497582092) AND (history_y2017.clock <= 1497668492) AND (history_y2017.itemid = 1464))
Rows Removed by Filter: 28560
CStore File: /var/lib/pgsql/10/data/cstore_fdw/16385/17191
CStore File Size: 3549773603
Planning time: 0.408 ms
Execution time: 1278.834 ms
```
Benchmarking it on a select benchmark gives me ~3 tps...
Changing the block_row_count from default to '1000' only serves to make the matter four times worse.
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.