You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by nunob <nu...@finantech.pt> on 2020/01/20 18:04:11 UTC
Query performance varying with LIMIT keyword
Hello,
I have table A with PK (col1, col2, col3) and table B with PK (col1, col2
col3)
Both tables have around 300k records.
This query executes in *2ms*:
/SELECT * FROM table_a a
LEFT JOIN table_b b ON b.col1 = a.col1
AND b.col2 = a.col2
AND b.col3 = a.col3
*LIMIT 1 *OFFSET 0;/
This query executes in *1.4s*:
/SELECT * FROM table_a a
LEFT JOIN table_b b ON b.col1 = a.col1
AND b.col2 = a.col2
AND b.col3 = a.col3
*LIMIT 10 *OFFSET 0;/
Shouldn't the execution time be the same for both queries or am I missing
something?
Regards,
Nuno
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Query performance varying with LIMIT keyword
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
200 ms for 2 entries / 1.3s for 11 entries is just 20% increase per entry.
Likewise, 7.8s for 51 entries / 1.3s for 11 entries is further 30% increase
per entry.
It does not sound exponential, at least for any reasonable number of
entries, instead it just looks superlinear.
I recommend adding index for this use case.
Regards,
--
Ilya Kasnacheev
вт, 21 янв. 2020 г. в 19:17, nunob <nu...@finantech.pt>:
> Hello,
>
> Thanks for responding.
>
> Well, it takes 50ms to fetch the first line.
>
> What I don't get is why it takes 8 seconds to fetch the 50th line.
>
> In both cases I'm only fetching ONE row.
>
> I suppose query execution time should be roughly the same whether i want
> the
> 1st or the 50th line.
>
> I mean, aren't OFFSET and LIMIT just discarding rows from the resulting
> dataset? Isn't it a question of looping through the cursor until it finds
> the Nth row? Why does it look like it's responding with exponential time as
> I increase the offset?
>
> Looking at explain it does look like it's doing a full table scan. In the
> following query I have indexes over the joining columns on both tables:
>
> SELECT
> SE__Z1.ISINCODE AS __C0_0,
> M__Z0.SYMBOL AS __C0_1,
> NULL AS __C0_2,
> CURRENT_TIMESTAMP() AS __C0_3,
> M__Z0.LASTTIME AS __C0_4,
> M__Z0.OPENPRICE AS __C0_5,
> M__Z0.HIGHPRICE AS __C0_6,
> M__Z0.LOWPRICE AS __C0_7,
> M__Z0.CLOSEPRICE AS __C0_8,
> M__Z0.LASTPRICE AS __C0_9,
> M__Z0.LASTQUANTITY AS __C0_10,
> M__Z0.BIDPRICE AS __C0_11,
> M__Z0.ASKPRICE AS __C0_12,
> M__Z0.BIDSIZE AS __C0_13,
> M__Z0.ASKSIZE AS __C0_14
> FROM SERVERDEAL.MARKETDATA M__Z0
> /* SERVERDEAL.MARKETDATA.__SCAN_ */
> LEFT OUTER JOIN SIMS.SECURITYEXCHANGE SE__Z1
> /* SIMS.SECURITYEXCHANGE.__SCAN_ */
> ON (SE__Z1.SECURITYTYPE = M__Z0.MARKET)
> AND ((SE__Z1.EXCHANGEID = M__Z0.EXCHANGE)
> AND (SE__Z1.SYMBOL = M__Z0.SYMBOL))
> LIMIT 11
>
>
>
>
>
>
>
>
>
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
Re: Query performance varying with LIMIT keyword
Posted by nunob <nu...@finantech.pt>.
Hello,
Thanks for responding.
Well, it takes 50ms to fetch the first line.
What I don't get is why it takes 8 seconds to fetch the 50th line.
In both cases I'm only fetching ONE row.
I suppose query execution time should be roughly the same whether i want the
1st or the 50th line.
I mean, aren't OFFSET and LIMIT just discarding rows from the resulting
dataset? Isn't it a question of looping through the cursor until it finds
the Nth row? Why does it look like it's responding with exponential time as
I increase the offset?
Looking at explain it does look like it's doing a full table scan. In the
following query I have indexes over the joining columns on both tables:
SELECT
SE__Z1.ISINCODE AS __C0_0,
M__Z0.SYMBOL AS __C0_1,
NULL AS __C0_2,
CURRENT_TIMESTAMP() AS __C0_3,
M__Z0.LASTTIME AS __C0_4,
M__Z0.OPENPRICE AS __C0_5,
M__Z0.HIGHPRICE AS __C0_6,
M__Z0.LOWPRICE AS __C0_7,
M__Z0.CLOSEPRICE AS __C0_8,
M__Z0.LASTPRICE AS __C0_9,
M__Z0.LASTQUANTITY AS __C0_10,
M__Z0.BIDPRICE AS __C0_11,
M__Z0.ASKPRICE AS __C0_12,
M__Z0.BIDSIZE AS __C0_13,
M__Z0.ASKSIZE AS __C0_14
FROM SERVERDEAL.MARKETDATA M__Z0
/* SERVERDEAL.MARKETDATA.__SCAN_ */
LEFT OUTER JOIN SIMS.SECURITYEXCHANGE SE__Z1
/* SIMS.SECURITYEXCHANGE.__SCAN_ */
ON (SE__Z1.SECURITYTYPE = M__Z0.MARKET)
AND ((SE__Z1.EXCHANGEID = M__Z0.EXCHANGE)
AND (SE__Z1.SYMBOL = M__Z0.SYMBOL))
LIMIT 11
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Query performance varying with LIMIT keyword
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
This would suggest that fetching a single row takes around 100 ms, in a
slightly superlinear fashion.
Why is it a problem? What indexes do you have? Can you provide EXPLAIN
plans?
Regards,
--
Ilya Kasnacheev
пн, 20 янв. 2020 г. в 22:23, nunob <nu...@finantech.pt>:
> Also if I fix the LIMIT and vary the OFFSET I get responses of this
> magnitude:
>
> LIMIT 1 OFFSET 0: 50ms
> LIMIT 1 OFFSET 1: 200ms
> LIMIT 1 OFFSET 5: 700ms
> LIMIT 1 OFFSET 10: 1.3s
> LIMIT 1 OFFSET 50: 7.8s
>
> Why is this?
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
Re: Query performance varying with LIMIT keyword
Posted by nunob <nu...@finantech.pt>.
Also if I fix the LIMIT and vary the OFFSET I get responses of this
magnitude:
LIMIT 1 OFFSET 0: 50ms
LIMIT 1 OFFSET 1: 200ms
LIMIT 1 OFFSET 5: 700ms
LIMIT 1 OFFSET 10: 1.3s
LIMIT 1 OFFSET 50: 7.8s
Why is this?
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/