1. - I have a nested join query on a table of 8,000,000 records which
performs similar or better than PostreSQL (~10ms) on my small test setup (2x
nodes, 8GB, 2CPU):
SELECT
mainTable.pk, mainTable.id, mainTable.k, mainTable.v
FROM
public.test_data AS mainTable
INNER JOIN (
SELECT
lastName.id
FROM
(SELECT id FROM public.test_data WHERE k =
'trans.cust.last_name' AND v
= 'Smythe-Veall') AS lastName
INNER JOIN
(SELECT id FROM
public.test_data WHERE k = 'trans.date' AND v =
'2017-12-21') AS transDate ON transDate.id = lastName.id
INNER JOIN
(SELECT id FROM
public.test_data WHERE k = 'trans.amount' AND cast(v
AS integer) > 90000) AS transAmount ON transAmount.id = lastName.id
) AS subTable ON mainTable.id = subTable.id
ORDER BY 1, 2
2. - By simply adding a WHERE clause at the end, the performance becomes
catastrophic on Ignite (~10s for subsequent queries - first query takes many
minutes). On PostgreSQL performance does not change...
SELECT
mainTable.pk, mainTable.id, mainTable.k, mainTable.v
FROM
public.test_data AS mainTable
INNER JOIN (
SELECT
lastName.id
FROM
(SELECT id FROM public.test_data WHERE k =
'trans.cust.last_name' AND v
= 'Smythe-Veall') AS lastName
INNER JOIN
(SELECT id FROM
public.test_data WHERE k = 'trans.date' AND v =
'2017-12-21') AS transDate ON transDate.id = lastName.id
INNER JOIN
(SELECT id FROM
public.test_data WHERE k = 'trans.amount' AND cast(v
AS integer) > 90000) AS transAmount ON transAmount.id = lastName.id
) AS subTable ON mainTable.id = subTable.id
*WHERE
mainTable.k = 'trans.cust.first_name'*
ORDER BY 1, 2
What can I do to optimise this query for Ignite???
(Table structure and query plans attached for reference)
Thanks,
Jose
table.sql
<http://apache-ignite-users.70518.x6.nabble.com/file/t1652/table.sql>
good-join-query.txt
<http://apache-ignite-users.70518.x6.nabble.com/file/t1652/good-join-query.txt>
bad-join-query.txt
<http://apache-ignite-users.70518.x6.nabble.com/file/t1652/bad-join-query.txt>
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
