pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
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 14:50:43 +0100
Message-ID: <7cecad43ac720668a96cb2fc112cac9b@xs4all.nl> (raw)
In-Reply-To: <20161115142713.Horde.2yANpKWbi2sgTkv2Qzw7bVs@box1242.bluehost.com>
References: <20161115142713.Horde.2yANpKWbi2sgTkv2Qzw7bVs@box1242.bluehost.com>
List-Unsubscribe:  <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>

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



view thread (5+ messages)  latest in thread

Message-ID: <7cecad43ac720668a96cb2fc112cac9b@xs4all.nl>
Permalink:  ../7cecad43ac720668a96cb2fc112cac9b@xs4all.nl/
Also on:    postgresql.org/message-id/7cecad43ac720668a96cb2fc112cac9b@xs4all.nl

 · 

reply
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: <7cecad43ac720668a96cb2fc112cac9b@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