Found the reason for the problem - and a solution...
Problem is that the query-optimiser performs the final WHERE clause *first*,
which results in a full table scan.
The solution is to structure the query so that the original query (without
the WHERE clause) is forced to be performed first, then finally the WHERE
clause can be run on a small set of data. This can be achieved by setting
the whole original query (without the WHERE clause) as a 'Nested
Sub-query'...
SELECT subQuery.*
FROM (
*SELECT
mainTable.pk, mainTable.id, mainTable.k, mainTable.v
FROM
public.transactions AS mainTable
INNER JOIN
(SELECT id FROM public.transactions WHERE k = 'trans.cust.last_name'
AND v = 'Smythe-Veall') AS lastName ON lastName.id = mainTable.id
INNER JOIN
(SELECT id FROM public.transactions WHERE k = 'trans.date' AND v =
'2017-12-21') AS transDate ON transDate.id = mainTable.id
INNER JOIN
(SELECT id FROM public.transactions WHERE k = 'trans.amount' AND
cast(v AS integer) > 90000) AS transAmount ON transAmount.id = mainTable.id*
) AS subQuery
WHERE subQuery.k = 'trans.cust.first_name'
ORDER BY 1
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
