From: vinny <vinny@xs4all.nl>
To: henrik@ekenberg.pw
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Cc: pgsql-performance-owner@postgresql.org
Subject: Re: Sql Query :: Any advice ?
Date: Tue, 15 Nov 2016 16:44:47 +0100
Message-ID: <1fd2cd0b983d53719d7a0f50855303a6@xs4all.nl> (raw)
In-Reply-To: <20161115153053.Horde.FdPqz_uJLMknz4L8pIlTdDQ@box1242.bluehost.com>
References: <20161115142713.Horde.2yANpKWbi2sgTkv2Qzw7bVs@box1242.bluehost.com>
<7cecad43ac720668a96cb2fc112cac9b@xs4all.nl>
<20161115153053.Horde.FdPqz_uJLMknz4L8pIlTdDQ@box1242.bluehost.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: vinny@xs4all.nl, henrik@ekenberg.pw, pgsql-performance-owner@postgresql.org
Subject: Re: Sql Query :: Any advice ?
In-Reply-To: <1fd2cd0b983d53719d7a0f50855303a6@xs4all.nl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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