pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSql Query :: Any advice ?
5+ messages / 3 participants
[nested] [flat]
* Sql Query :: Any advice ?
@ 2016-11-15 13:27 Henrik Ekenberg <henrik@ekenberg.pw>
2016-11-15 13:50 ` Re: Sql Query :: Any advice ? vinny <vinny@xs4all.nl>
0 siblings, 1 reply; 5+ messages in thread
From: Henrik Ekenberg @ 2016-11-15 13:27 UTC (permalink / raw)
To: pgsql-performance
Hi,
I have some data to join and I want to get som advice from you.
Any tips ? Any comments are apreciated
//H
select trade_no
from
forecast_trades.hist_account_balance
left join trades using (trade_no)
where trade_date > current_date - 120
and trade_date < current_date - 30
and forex = 'f'
and options = 'f'
group by trade_no
having max(account_size) > 0
;
( Query Plan : https://explain.depesz.com/s/4lOD )
QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=34760605.76..34773866.26 rows=1060840 width=15)
(actual time=1142816.632..1150194.076 rows=2550634 loops=1)
Group Key: hist_account_balance.trade_no
Filter: (max(hist_account_balance.account_size) > 0::numeric)
Rows Removed by Filter: 18240023
-> Hash Join (cost=3407585.35..34530512.29 rows=46018694 width=15)
(actual time=60321.201..1108647.151 rows=44188963 loops=1)
Hash Cond: (hist_account_balance.trade_no =
trades.trade_no)
-> Seq Scan on hist_account_balance
(cost=0.00..14986455.20 rows=570046720 width=15) (actual
time=0.016..524427.140 rows=549165594 loops=1)
-> Hash (cost=3159184.13..3159184.13 rows=19872098
width=12) (actual time=60307.001..60307.001 rows=20790658 loops=1)
Buckets: 2097152 Batches: 1 Memory Usage:
913651kB
-> Index Scan using trades_trade_date_index
on trades (cost=0.58..3159184.13 rows=19872098 width=12) (actual
time=0.078..52213.976 rows=20790658 loops=1)
Index Cond: ((trade_date >
(('now'::cstring)::date - 120)) AND (trade_date < (('now'::cstring)::date -
30)))
Filter: ((NOT forex) AND (NOT
options))
Rows Removed by Filter: 2387523
Planning time: 2.157 ms
Execution time: 1151234.290 ms
(15 rows)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Sql Query :: Any advice ?
2016-11-15 13:27 Sql Query :: Any advice ? Henrik Ekenberg <henrik@ekenberg.pw>
@ 2016-11-15 13:50 ` vinny <vinny@xs4all.nl>
2016-11-15 14:30 ` Re: Sql Query :: Any advice ? Henrik Ekenberg <henrik@ekenberg.pw>
0 siblings, 1 reply; 5+ messages in thread
From: vinny @ 2016-11-15 13:50 UTC (permalink / raw)
To: henrik@ekenberg.pw; +Cc: pgsql-performance; pgsql-performance-owner@postgresql.org
On 2016-11-15 14:27, Henrik Ekenberg wrote:
> Hi,
>
> I have some data to join and I want to get som advice from you.
>
> Any tips ? Any comments are apreciated
>
> //H
>
> select trade_no
> from
> forecast_trades.hist_account_balance
> left join trades using (trade_no)
> where trade_date > current_date - 120
> and trade_date < current_date - 30
> and forex = 'f'
> and options = 'f'
> group by trade_no
> having max(account_size) > 0
> ;
>
> ( Query Plan : https://explain.depesz.com/s/4lOD )
>
> QUERY PLAN
>
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> HashAggregate (cost=34760605.76..34773866.26 rows=1060840 width=15)
> (actual time=1142816.632..1150194.076 rows=2550634 loops=1)
> Group Key: hist_account_balance.trade_no
> Filter: (max(hist_account_balance.account_size) > 0::numeric)
> Rows Removed by Filter: 18240023
> -> Hash Join (cost=3407585.35..34530512.29 rows=46018694
> width=15) (actual time=60321.201..1108647.151 rows=44188963 loops=1)
> Hash Cond: (hist_account_balance.trade_no = trades.trade_no)
> -> Seq Scan on hist_account_balance (cost=0.00..14986455.20
> rows=570046720 width=15) (actual time=0.016..524427.140 rows=549165594
> loops=1)
> -> Hash (cost=3159184.13..3159184.13 rows=19872098
> width=12) (actual time=60307.001..60307.001 rows=20790658 loops=1)
> Buckets: 2097152 Batches: 1 Memory Usage: 913651kB
> -> Index Scan using trades_trade_date_index on trades
> (cost=0.58..3159184.13 rows=19872098 width=12) (actual
> time=0.078..52213.976 rows=20790658 loops=1)
> Index Cond: ((trade_date >
> (('now'::cstring)::date - 120)) AND (trade_date <
> (('now'::cstring)::date - 30)))
> Filter: ((NOT forex) AND (NOT options))
> Rows Removed by Filter: 2387523
> Planning time: 2.157 ms
> Execution time: 1151234.290 ms
> (15 rows)
What kind of indexes have you created for those tables?
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Sql Query :: Any advice ?
2016-11-15 13:27 Sql Query :: Any advice ? Henrik Ekenberg <henrik@ekenberg.pw>
2016-11-15 13:50 ` Re: Sql Query :: Any advice ? vinny <vinny@xs4all.nl>
@ 2016-11-15 14:30 ` Henrik Ekenberg <henrik@ekenberg.pw>
2016-11-15 15:44 ` Re: Sql Query :: Any advice ? vinny <vinny@xs4all.nl>
0 siblings, 1 reply; 5+ messages in thread
From: Henrik Ekenberg @ 2016-11-15 14:30 UTC (permalink / raw)
To: pgsql-performance
Here are the indexes I have for those queries
Indexes:
hist_account_balance :: "hist_account_balance_ix1" btree (trade_no)
trades :: "trades_pkey" PRIMARY KEY, btree (trade_no)
"trades_trade_date_index" btree (trade_date)
//H
Quoting vinny <vinny@xs4all.nl>:
> On 2016-11-15 14:27, Henrik Ekenberg wrote:
>> Hi,
>>
>> I have some data to join and I want to get som advice from you.
>>
>> Any tips ? Any comments are apreciated
>>
>> //H
>>
>> select trade_no
>> from
>> forecast_trades.hist_account_balance
>> left join trades using (trade_no)
>> where trade_date > current_date - 120
>> and trade_date < current_date - 30
>> and forex = 'f'
>> and options = 'f'
>> group by trade_no
>> having max(account_size) > 0
>> ;
>>
>> ( Query Plan : https://explain.depesz.com/s/4lOD )
>>
>> QUERY PLAN
>>
>>
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>> HashAggregate (cost=34760605.76..34773866.26 rows=1060840 width=15)
>> (actual time=1142816.632..1150194.076 rows=2550634 loops=1)
>> Group Key: hist_account_balance.trade_no
>> Filter: (max(hist_account_balance.account_size) > 0::numeric)
>> Rows Removed by Filter: 18240023
>> -> Hash Join (cost=3407585.35..34530512.29 rows=46018694
>> width=15) (actual time=60321.201..1108647.151 rows=44188963 loops=1)
>> Hash Cond: (hist_account_balance.trade_no = trades.trade_no)
>> -> Seq Scan on hist_account_balance
(cost=0.00..14986455.20
>> rows=570046720 width=15) (actual time=0.016..524427.140 rows=549165594
>> loops=1)
>> -> Hash (cost=3159184.13..3159184.13 rows=19872098
>> width=12) (actual time=60307.001..60307.001 rows=20790658 loops=1)
>> Buckets: 2097152 Batches: 1 Memory Usage:
913651kB
>> -> Index Scan using trades_trade_date_index on
trades
>> (cost=0.58..3159184.13 rows=19872098 width=12) (actual
>> time=0.078..52213.976 rows=20790658 loops=1)
>> Index Cond: ((trade_date >
>> (('now'::cstring)::date - 120)) AND (trade_date <
>> (('now'::cstring)::date - 30)))
>> Filter: ((NOT forex) AND (NOT options))
>> Rows Removed by Filter: 2387523
>> Planning time: 2.157 ms
>> Execution time: 1151234.290 ms
>> (15 rows)
>
> What kind of indexes have you created for those tables?
>
> --
> Sent via pgsql-performance mailing list
(pgsql-performance@postgresql.org)
> To make changes to your
> subscription:http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Sql Query :: Any advice ?
2016-11-15 13:27 Sql Query :: Any advice ? Henrik Ekenberg <henrik@ekenberg.pw>
2016-11-15 13:50 ` Re: Sql Query :: Any advice ? vinny <vinny@xs4all.nl>
2016-11-15 14:30 ` Re: Sql Query :: Any advice ? Henrik Ekenberg <henrik@ekenberg.pw>
@ 2016-11-15 15:44 ` vinny <vinny@xs4all.nl>
2016-11-15 17:23 ` Re: Sql Query :: Any advice ? Henrik <Henrik@ekenberg.pw>
0 siblings, 1 reply; 5+ messages in thread
From: vinny @ 2016-11-15 15:44 UTC (permalink / raw)
To: henrik@ekenberg.pw; +Cc: pgsql-performance; pgsql-performance-owner@postgresql.org
Are the forex and options in the hist_account_balance table?
The sequential scan is on that table so if they are,
so I'm guessing they should probably by in the index.
On 2016-11-15 15:30, Henrik Ekenberg wrote:
> Here are the indexes I have for those queries
>
> Indexes:
>
> hist_account_balance :: "hist_account_balance_ix1" btree (trade_no)
>
> trades :: "trades_pkey" PRIMARY KEY, btree (trade_no)
> "trades_trade_date_index" btree (trade_date)
>
> //H
>
> Quoting vinny <vinny@xs4all.nl>:
>
>> On 2016-11-15 14:27, Henrik Ekenberg wrote:
>>
>>> Hi,
>>>
>>> I have some data to join and I want to get som advice from you.
>>>
>>> Any tips ? Any comments are apreciated
>>>
>>> //H
>>>
>>> select trade_no
>>> from
>>> forecast_trades.hist_account_balance
>>> left join trades using (trade_no)
>>> where trade_date > current_date - 120
>>> and trade_date < current_date - 30
>>> and forex = 'f'
>>> and options = 'f'
>>> group by trade_no
>>> having max(account_size) > 0
>>> ;
>>>
>>> ( Query Plan : https://explain.depesz.com/s/4lOD )
>>>
>>> QUERY PLAN
>>>
>>>
>>
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>>> HashAggregate (cost=34760605.76..34773866.26 rows=1060840
>>> width=15)
>>> (actual time=1142816.632..1150194.076 rows=2550634 loops=1)
>>> Group Key: hist_account_balance.trade_no
>>> Filter: (max(hist_account_balance.account_size) > 0::numeric)
>>> Rows Removed by Filter: 18240023
>>> -> Hash Join (cost=3407585.35..34530512.29 rows=46018694
>>> width=15) (actual time=60321.201..1108647.151 rows=44188963
>>> loops=1)
>>> Hash Cond: (hist_account_balance.trade_no =
>>> trades.trade_no)
>>> -> Seq Scan on hist_account_balance
>>> (cost=0.00..14986455.20
>>> rows=570046720 width=15) (actual time=0.016..524427.140
>>> rows=549165594
>>> loops=1)
>>> -> Hash (cost=3159184.13..3159184.13 rows=19872098
>>> width=12) (actual time=60307.001..60307.001 rows=20790658 loops=1)
>>> Buckets: 2097152 Batches: 1 Memory Usage: 913651kB
>>> -> Index Scan using trades_trade_date_index on
>>> trades
>>> (cost=0.58..3159184.13 rows=19872098 width=12) (actual
>>> time=0.078..52213.976 rows=20790658 loops=1)
>>> Index Cond: ((trade_date >
>>> (('now'::cstring)::date - 120)) AND (trade_date <
>>> (('now'::cstring)::date - 30)))
>>> Filter: ((NOT forex) AND (NOT options))
>>> Rows Removed by Filter: 2387523
>>> Planning time: 2.157 ms
>>> Execution time: 1151234.290 ms
>>> (15 rows)
>> What kind of indexes have you created for those tables?
>>
>> --
>> Sent via pgsql-performance mailing list
>> (pgsql-performance@postgresql.org)
>> To make changes to your
>> subscription:http://www.postgresql.org/mailpref/pgsql-performance
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Sql Query :: Any advice ?
2016-11-15 13:27 Sql Query :: Any advice ? Henrik Ekenberg <henrik@ekenberg.pw>
2016-11-15 13:50 ` Re: Sql Query :: Any advice ? vinny <vinny@xs4all.nl>
2016-11-15 14:30 ` Re: Sql Query :: Any advice ? Henrik Ekenberg <henrik@ekenberg.pw>
2016-11-15 15:44 ` Re: Sql Query :: Any advice ? vinny <vinny@xs4all.nl>
@ 2016-11-15 17:23 ` Henrik <Henrik@ekenberg.pw>
0 siblings, 0 replies; 5+ messages in thread
From: Henrik @ 2016-11-15 17:23 UTC (permalink / raw)
To: vinny@xs4all.nl; +Cc: pgsql-performance; pgsql-performance-owner@postgresql.org
<div dir="ltr">Hi, <br>
</div><div dir="ltr"><br>
</div><div dir="ltr">Forex and options are in trades table<br>
</div><div dir="ltr"><br>
</div><div dir="ltr">Best regards <br>
</div><div dir="ltr">Henrik <br>
</div><div dir="ltr"><br>
</div><div dir="ltr"><br>
</div><div dir="ltr"><br>
</div><div class="wps_signature">Sent from my Mi pad</div><div class="wps_quotion">On vinny <vinny@xs4all.nl>, Nov 15, 2016 6:46 PM wrote:<br type="attribution"><blockquote class="quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><p></p><p dir="ltr">Are the forex and options in the hist_account_balance table?
<br>
The sequential scan is on that table so if they are,
<br>
so I'm guessing they should probably by in the index.
<br>
<br>
On 2016-11-15 15:30, Henrik Ekenberg wrote:
<br>
> Here are the indexes I have for those queries
<br>
>
<br>
> Indexes:
<br>
>
<br>
> hist_account_balance :: "hist_account_balance_ix1" btree (trade_no)
<br>
>
<br>
> trades :: "trades_pkey" PRIMARY KEY, btree (trade_no)
<br>
> "trades_trade_date_index" btree (trade_date)
<br>
>
<br>
> //H
<br>
>
<br>
> Quoting vinny <<a href="mailto:vinny@xs4all.nl">vinny@xs4all.nl</a>>:
<br>
>
<br>
>> On 2016-11-15 14:27, Henrik Ekenberg wrote:
<br>
>>
<br>
>>> Hi,
<br>
>>>
<br>
>>> I have some data to join and I want to get som advice from you.
<br>
>>>
<br>
>>> Any tips ? Any comments are apreciated
<br>
>>>
<br>
>>> //H
<br>
>>>
<br>
>>> select trade_no
<br>
>>> from
<br>
>>> forecast_trades.hist_account_balance
<br>
>>> left join trades using (trade_no)
<br>
>>> where trade_date > current_date - 120
<br>
>>> and trade_date < current_date - 30
<br>
>>> and forex = 'f'
<br>
>>> and options = 'f'
<br>
>>> group by trade_no
<br>
>>> having max(account_size) > 0
<br>
>>> ;
<br>
>>>
<br>
>>> ( Query Plan : <a href="https://explain.depesz.com/s/4lOD">https://explain.depesz.com/s/4lOD</a; )
<br>
>>>
<br>
>>> QUERY PLAN
<br>
>>>
<br>
>>>
<br>
>>
<br>
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<br>
>>> HashAggregate (cost=34760605.76..34773866.26 rows=1060840
<br>
>>> width=15)
<br>
>>> (actual time=1142816.632..1150194.076 rows=2550634 loops=1)
<br>
>>> Group Key: hist_account_balance.trade_no
<br>
>>> Filter: (max(hist_account_balance.account_size) > 0::numeric)
<br>
>>> Rows Removed by Filter: 18240023
<br>
>>> -> Hash Join (cost=3407585.35..34530512.29 rows=46018694
<br>
>>> width=15) (actual time=60321.201..1108647.151 rows=44188963
<br>
>>> loops=1)
<br>
>>> Hash Cond: (hist_account_balance.trade_no =
<br>
>>> trades.trade_no)
<br>
>>> -> Seq Scan on hist_account_balance
<br>
>>> (cost=0.00..14986455.20
<br>
>>> rows=570046720 width=15) (actual time=0.016..524427.140
<br>
>>> rows=549165594
<br>
>>> loops=1)
<br>
>>> -> Hash (cost=3159184.13..3159184.13 rows=19872098
<br>
>>> width=12) (actual time=60307.001..60307.001 rows=20790658 loops=1)
<br>
>>> Buckets: 2097152 Batches: 1 Memory Usage: 913651kB
<br>
>>> -> Index Scan using trades_trade_date_index on
<br>
>>> trades
<br>
>>> (cost=0.58..3159184.13 rows=19872098 width=12) (actual
<br>
>>> time=0.078..52213.976 rows=20790658 loops=1)
<br>
>>> Index Cond: ((trade_date >
<br>
>>> (('now'::cstring)::date - 120)) AND (trade_date <
<br>
>>> (('now'::cstring)::date - 30)))
<br>
>>> Filter: ((NOT forex) AND (NOT options))
<br>
>>> Rows Removed by Filter: 2387523
<br>
>>> Planning time: 2.157 ms
<br>
>>> Execution time: 1151234.290 ms
<br>
>>> (15 rows)
<br>
>> What kind of indexes have you created for those tables?
<br>
>>
<br>
>> --
<br>
>> Sent via pgsql-performance mailing list
<br>
>> (<a href="mailto:pgsql-performance@postgresql.org">pgsql-performance@postgresql.org</a>)
<br>
>> To make changes to your
<br>
>> subscription:<a href="http://www.postgresql.org/mailpref/pgsql-performance">http://www.postgresql.org/mailpref/pgsq...;
<br>
<br>
<br>
--
<br>
Sent via pgsql-performance mailing list (<a href="mailto:pgsql-performance@postgresql.org">pgsql-performance@postgresql.org</a>)
<br>
To make changes to your subscription:
<br>
<a href="http://www.postgresql.org/mailpref/pgsql-performance">http://www.postgresql.org/mailpref/pgsq...;
<br>
</p>
</blockquote></div>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2016-11-15 17:23 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-11-15 13:27 Sql Query :: Any advice ? Henrik Ekenberg <henrik@ekenberg.pw>
2016-11-15 13:50 ` vinny <vinny@xs4all.nl>
2016-11-15 14:30 ` Henrik Ekenberg <henrik@ekenberg.pw>
2016-11-15 15:44 ` vinny <vinny@xs4all.nl>
2016-11-15 17:23 ` Henrik <Henrik@ekenberg.pw>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox