0

I am experiencing a strange behavior with nested query inside the IN query. It is taking unexpectedly longer time to execute. I have simplified my query to minimal for debugging.

Here is the SQL:

select * from test_table where id in (
  select 1
)

And the EXPLAIN ANALYZE is

"Hash Semi Join  (cost=0.03..0.07 rows=1 width=2198) (actual time=0.615..10297.491 rows=1 loops=1)"
"  Hash Cond: (test_table.id = (1))"
"  ->  Data Node Scan on test_table "_REMOTE_TABLE_QUERY_"  (cost=0.00..0.00 rows=1000 width=2198) (actual time=0.593..7494.668 rows=3008167 loops=1)"
"        Node/s: datanode"
"  ->  Hash  (cost=0.02..0.02 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=1)"
"        Buckets: 1024  Batches: 1  Memory Usage: 1kB"
"        ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)"
"Total runtime: 10628.786 ms"

As I am using select 1 as nested query and it only returns 1, So I am modifying my SQL as below:

select * from test_table where id in (
    1  -- NO SELECT
)

And the EXPLAIN ANALYZE is 

"Data Node Scan on "__REMOTE_FQS_QUERY__"  (cost=0.00..0.00 rows=0 width=0) (actual time=1.109..1.112 rows=1 loops=1)"
"  Node/s: datanode"
"Total runtime: 1.152 ms"

Now my question is, why the time difference is huge here? And what can I do to improve my situation here? My psql version is 9.3.1

Sabuj Hassan
  • 38,281
  • 14
  • 75
  • 85
  • 2
    This doesn't look like a "vanilla" Postgres installation (because of the "*Data Node Scan*" part). What are you really using? Please post the output of `select version()` –  Oct 23 '14 at 14:19
  • @a_horse_with_no_name it returns `"PostgreSQL 9.3.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit"`. I have a `xc` wrapper over the `postgresql` for clustering. – Sabuj Hassan Oct 23 '14 at 14:25
  • 1
    So it's Postgres-XC, **not** Postgres –  Oct 23 '14 at 14:28
  • 1
    In the second case Postgres-XC sends filtering predicate (`id = 1`) to a Postgres server. In first case it does full scan on the table (transfer all data in the table?) and then does the filtering. – Ihor Romanchenko Oct 23 '14 at 14:34

0 Answers0