Query in subquery is slow about cstore_fdw HOT 7 CLOSED

Interesting. I have a couple of questions:

1. In some explain outputs limit row count is 100 and in other cases it is 1000. It seems that some queries have been run with "LIMIT 100" and some with "LIMIT 1000". Can you double check this?
2. Did you run "ANALYZE tablename;" before running the queries?

Thanks,
-- Hadi

from cstore_fdw.

1. ) LIMIT does not actually matter about the result ( just double checked that) . Even with LIMIT 1 in subquery performance drops significantly.
2. ) I did run "ANALYZE tablename;" and tried it again, but no help. Without surrounding SELECT performance is good and better than plain PG.

-Sami

from cstore_fdw.

Thanks for the reply. I'll look into this. Can I get a sample of your data or at least its CREATE TABLE statement?

from cstore_fdw.

Something like this.

CREATE FOREIGN TABLE IF NOT EXISTS test1.ins (id serial, ts bigint, lev smallint, ts_oot int, ts_int int, ins int, cll varchar(1), cpu smallint, thr int, mod smallint, sym int)
SERVER cstore_server
OPTIONS(compression pglz)

Small data sample:
8519327|420|11|0|8591|1|c|0|28|1|12
8519328|422|12|0|41|17|e|0|28|1|12
8519329|463|12|0|186|1|c|0|28|1|13
8519330|465|13|0|22|9|e|0|28|1|13
8519331|487|13|0|16|1|c|0|28|1|14
8519332|489|14|0|14|6|e|0|28|1|14
8519333|503|13|0|3|1|e|0|28|1|13
8519334|506|13|0|52|1|c|0|28|1|7
8519335|508|14|0|50|21|e|0|28|1|7
8519336|558|13|0|3|1|e|0|28|1|13
8519337|561|13|0|76|1|c|0|28|1|15
8519338|563|14|0|74|31|e|0|28|1|15
8519339|637|13|0|12|5|e|0|28|1|13
8519340|649|12|0|14|6|e|0|28|1|12
8519341|663|12|0|67|1|c|0|28|1|16
8519342|666|13|0|64|27|e|0|28|1|16
8519343|730|12|0|7|3|e|0|28|1|12
8519344|737|12|0|172|1|c|0|28|1|17
8519345|740|13|0|12|5|e|0|28|1|17
8519346|752|13|0|52|1|c|0|28|1|18
8519347|754|14|0|50|21|e|0|28|1|18
8519348|804|13|0|5|2|e|0|28|1|17
8519349|809|13|0|17|1|c|0|28|1|2
8519350|812|14|0|14|6|e|0|28|1|2
8519351|826|13|0|9|4|e|0|28|1|17
8519352|835|13|0|58|1|c|0|28|1|3
8519353|838|14|0|55|23|e|0|28|1|3
8519354|893|13|0|16|7|e|0|28|1|17
8519355|909|12|0|8|3|e|0|28|1|12
8519356|917|12|0|162|1|c|0|28|1|19
8519357|919|13|0|29|12|e|0|28|1|19
8519358|948|13|0|126|1|c|0|28|1|20
8519359|950|14|0|29|12|e|0|28|1|20
8519360|979|14|0|16|1|c|0|28|1|14
8519361|981|15|0|14|6|e|0|28|1|14
8519362|995|14|0|43|18|e|0|28|1|20
8519363|1038|14|0|17|1|c|0|28|1|2
8519364|1041|15|0|14|6|e|0|28|1|2
8519365|1055|14|0|19|8|e|0|28|1|20

from cstore_fdw.

Thanks for the data and sorry for not looking into this so far. I've been very busy during last two weeks. I will look into this early next week.

from cstore_fdw.

This might be related to our doing not a very good job at providing estimates to PostgreSQL. We will try to improve estimation functions in v1.3, and will investigate this in more detail then.

from cstore_fdw.

row count estimations are now merged to master and released with v1.3

from cstore_fdw.