Hello,
We are currently testing cstore_fdw 1.3 but we came across serious performance issues on different cases.
To demonstrate the problem, we created a testing data set as follows:
postgres=> create foreign table "test_table" (somedata integer) server cstore_server;
CREATE FOREIGN TABLE
Time: 22.470 ms
postgres=> insert into "test_table" select generate_series(1,10000000);
INSERT 0 10000000
Then a simple query to test the table:
SELECT sum(somedata)
FROM "test_table"
WHERE somedata between 100 and 8000;
sum
----------
31999050
(1 row)
Time: 26.560 ms
Postgres' query plan when using 'explain analyze' shows that only a few rows were filtered. Our understanding is that cstore_fdw was able to skip many blocks entirely :
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=155165.00..155165.01 rows=1 width=4) (actual time=25.134..25.134 rows=1 loops=1)
-> Foreign Scan on test_table (cost=0.00..155040.00 rows=50000 width=4) (actual time=0.578..23.509 rows=7901 loops=1)
Filter: ((somedata >= 100) AND (somedata <= 8000))
Rows Removed by Filter: 2099
CStore File: /var/lib/postgresql/9.4/main/cstore_fdw/12150/16528
CStore File Size: 41283601
However, when doing the following equivalent query, performance gets much worse:
WITH test AS (
SELECT sum(somedata)
FROM "test_table"
WHERE somedata between 100 and 8000
)
SELECT * FROM test;
sum
----------
31999050
(1 row)
Time: 771.661 ms
The planer seems to indicate a full scan is being used (9992099 rows filtered):
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
CTE Scan on test (cost=155165.01..155165.03 rows=1 width=8) (actual time=871.111..871.112 rows=1 loops=1)
CTE test
-> Aggregate (cost=155165.00..155165.01 rows=1 width=4) (actual time=871.107..871.107 rows=1 loops=1)
-> Foreign Scan on test_table (cost=0.00..155040.00 rows=50000 width=4) (actual time=1.135..869.455 rows=7901 loops=1)
Filter: ((somedata >= 100) AND (somedata <= 8000))
Rows Removed by Filter: 9992099
CStore File: /var/lib/postgresql/9.4/main/cstore_fdw/12150/16528
CStore File Size: 41283601
A similar issue exists when doing a 'group by' or an 'order by'
SELECT *
FROM (
SELECT somedata
FROM "test_table"
WHERE somedata between 100 and 8000
ORDER BY somedata
) x(somedata);
Time: 823.139 ms
Doing the equivalent query this way is faster:
SELECT somedata
FROM (
SELECT somedata
FROM "test_table"
WHERE somedata between 100 and 8000
) x(somedata)
ORDER BY somedata;
Time: 34.104 ms
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Sort (cost=158942.41..159067.41 rows=50000 width=4) (actual time=7.317..7.604 rows=7901 loops=1)
Sort Key: test_table.somedata
Sort Method: quicksort Memory: 563kB
-> Foreign Scan on test_table (cost=0.00..155040.00 rows=50000 width=4) (actual time=0.177..6.510 rows=7901 loops=1)
Filter: ((somedata >= 100) AND (somedata <= 8000))
Rows Removed by Filter: 2099
CStore File: /var/lib/postgresql/9.4/main/cstore_fdw/12150/16528
CStore File Size: 41283601
However, when using the above (faster) query as a subselect, things get slower again with a query plan that also indicates that a full scan occured:
SELECT *
FROM (
SELECT somedata
FROM (
SELECT somedata
FROM "test_table"
WHERE somedata between 100 and 8000
) x(somedata)
ORDER BY somedata
) y(somedata);
Time: 858.250 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Sort (cost=158942.41..159067.41 rows=50000 width=4) (actual time=879.086..879.306 rows=7901 loops=1)
Sort Key: test_table.somedata
Sort Method: quicksort Memory: 563kB
-> Foreign Scan on test_table (cost=0.00..155040.00 rows=50000 width=4) (actual time=1.142..876.050 rows=7901 loops=1)
Filter: ((somedata >= 100) AND (somedata <= 8000))
Rows Removed by Filter: 9992099
CStore File: /var/lib/postgresql/9.4/main/cstore_fdw/12150/16528
CStore File Size: 41283601
Planning time: 3.950 ms
Execution time: 879.659 ms