Performance issue when applying LIMIT.

[Alexandru Cristian <ac...@metabiota.com>]

Hi everyone,

I'm trying to understand some strange behavior that's linked to LIMIT in the following scenario:

*         I'm running a simple select (no order by)

o   SELECT * FROM t WHERE field IN (value1, value 2 ..... value n) - returns 444 records in 3s

The execution plan is as bellow:

CLIENT 2-CHUNK PARALLEL 1-WAY FULL SCAN OVER t

     SERVER FILTER BY (field IN ('01200a_OB_2335df4d_a75ed685085a','01a ...



o   SELECT * FROM t WHERE field IN (value1, value 2 ..... value n) LIMIT 1000 - returns 444 records in 7s

The execution plan changes to:

CLIENT 2-CHUNK PARALLEL 1-WAY FULL SCAN OVER t

     SERVER FILTER BY (field IN ('01200a_OB_2335df4d_a75ed685085a','01a

     SERVER 1000 ROW LIMIT

 CLIENT 1000 ROW LIMIT



Now from the documentation I've found I understand that the LIMIT is applied in the execution plan as the last step, after we do the ORDER BY.

Now taking into accound that the select I'm running doesn't do any order by, can someone explain the huge difference in performance, almost double when limiting the result set.



Thanks,