The select is too slowly

[houzi1099857978]

I put 100,000 rows data to elasticsearch,when I select 1 row by id through foreign table,it took 222 second, I wander is it fetch all data from elasticsearch to postgres and then filter the data?

[matthewfranglen]

Ideally https://github.com/matthewfranglen/postgres-elasticsearch-fdw/blob/master/pg_es_fdw/__init__.py#L107 would use the rowid column if it is in the where clause, but it does not. Currently I just evaluate the where clause column, so you can make it fast if you should use that. Something like:

select * from test_es where query = '_id:1';

[houzi1099857978]

https://github.com/matthewfranglen/postgres-elasticsearch-fdw/blob/master/pg_es_fdw/__init__.py#L107 should use the rowid column if it is in the where clause. Currently I just evaluate the where clause column, so you can make it fast if you should use that. Something like:

select * from test_es where query = '_id:1';

Thanks,it is faster by using query fetch data

[ghost]

Hello,

I was trying to perform the query from Postgres to elasticsearch using "query" as shown above. But it was throwing me an error saying...

postgres=# select * from articles_es where query = '_id:1';
ERROR:  COUNT for /index1 failed: Expecting value: line 1 column 1 (char 0)

Here are the details of how I created the foreign table (providing info on a high level):

CREATE FOREIGN TABLE articles_es
    (
        id TEXT,
        query TEXT,
        ....
    )
SERVER multicorn_es
OPTIONS
    (
        host 'elasticsearch',
        port '9200',
        index 'article-index',
        type 'article',
        rowid_column 'id',
        query_column 'query',
        query_dsl 'false',
        refresh 'false',
        complete_returning 'false',
        timeout '20',
        username 'elastic',
        password 'elastic'
    )
;

Any suggestions, please.

[matthewfranglen]

The response that is being received by the client is not json. This is really a separate issue so I've opened it as another issue: https://github.com/matthewfranglen/postgres-elasticsearch-fdw/issues/26

[matthewfranglen]

I've opened #27 to track the problem that this ticket originally reports. Since this ticket has lost focus I am closing it.