Hi Jose, I am a bit lost in two provided explains. Both "godd" and "bad" contain "k = 'trans.cust.first_name'" condition. Could you please confirm that they are correct? Specifically, I cannot understand why this condition is present in "good" explain.
On Tue, Nov 27, 2018 at 12:06 PM joseheitor <j...@heitorprojects.com> wrote: > 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/ >
