pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedTime bucketing query performance
2+ messages / 2 participants
[nested] [flat]
* Time bucketing query performance
@ 2018-05-07 23:33 Julian Wilson <julianwlsn@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Julian Wilson @ 2018-05-07 23:33 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Hi,
I'm trying to debug improve the performance of my time bucketing query.
What I'm trying to do is essentially bucket by an arbitrary interval and
then do some aggregations within that interval (min,max,sum, etc). I am
using a `max` in the query I posted. For context in the data, it is 1
minute candles of cryptocurrency data (open price, high price, low price,
close price, volume, for an interval). I want to transform this to a 10
minute interval, on demand, and that is what this query is meant to do.
I understand the slow part of my query is in the LEFT JOIN, but I just
can't quite figure out how to do it without the LEFT JOIN.
Here is my pastebin with all the details so I don't clutter the message. I
tried to follow everything in the 'Slow Query Questions' WIKI page. There
is also a depesz link there.
https://ybin.me/p/9d3f52d88b4b2a46#kYLotYpNuIjjbp2P4l3la8fGSJIV0p+opH4sPq1m2/Y=
Thank for your help,
Julian
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Time bucketing query performance
@ 2018-05-08 01:08 Justin Pryzby <pryzby@telsasoft.com>
parent: Julian Wilson <julianwlsn@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Justin Pryzby @ 2018-05-08 01:08 UTC (permalink / raw)
To: Julian Wilson <julianwlsn@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Mon, May 07, 2018 at 07:33:17PM -0400, Julian Wilson wrote:
> Hi,
>
> I'm trying to debug improve the performance of my time bucketing query.
> What I'm trying to do is essentially bucket by an arbitrary interval and
> then do some aggregations within that interval (min,max,sum, etc). I am
> using a `max` in the query I posted. For context in the data, it is 1
> minute candles of cryptocurrency data (open price, high price, low price,
> close price, volume, for an interval). I want to transform this to a 10
> minute interval, on demand, and that is what this query is meant to do.
>
> I understand the slow part of my query is in the LEFT JOIN, but I just
> can't quite figure out how to do it without the LEFT JOIN.
>
> Here is my pastebin with all the details so I don't clutter the message. I
> tried to follow everything in the 'Slow Query Questions' WIKI page. There
> is also a depesz link there.
>
> https://ybin.me/p/9d3f52d88b4b2a46#kYLotYpNuIjjbp2P4l3la8fGSJIV0p+opH4sPq1m2/Y=
Thsse may not be a substantial part of the issue, but I have some suggestions:
0) You're using CTE, which cannot have stats (unlike temporary table). Can you
rewrite without, perhaps with GROUP BY date_trunc('hour', time_open) ?
1) you're querying on start_time AND end_time, and I believe the planner thinks
those conditions are independent, but they're not. Try getting rid of the
frame_end and move the "5 months" into the main query using BETWEEN or two
ANDed conditions on the same variable. See if the rowcount estimate is more
accurate:
-> Index Scan using historical_ohlcv_pkey on historical_ohlcv ohlcv (cost=0.56..2488.58 ROWS=12110 width=22) (actual time=3.709..4.403 ROWS=9 loops=3625)
Index Cond: ((exchange_symbol = 'BINANCE'::text) AND (symbol_id = 'ETHBTC'::text) AND (time_open >= g.start_time))
Filter: (time_close < g.end_time)
Alternately, you could try:
CREATE STATISTICS (dependencies) ON (time_open,time_close) FROM historical_ohlcv ;
ANALYZE historical_ohlcv;
2) Is your work_mem really default? 64kb? Recommend changing it to see if the
plan changes (although it looks like that's not the issue).
3) If you have SSD, you should probably CREATE TABLESPACE tmp LOCATION /srv/pgsql_tmp and
ALTER SYSTEM SET temp_tablespaces='tmp' and SELECT pg_reload_conf();
4) If those don't help, as a test, try running with SET enable_nestloop=off.
I'm guessing that fixing rowcount estimate in (1) will be sufficient.
5) May not be important, but rerun with explain (ANALYZE,BUFFERS) and show the
results.
Justin
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2018-05-08 01:08 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-05-07 23:33 Time bucketing query performance Julian Wilson <julianwlsn@gmail.com>
2018-05-08 01:08 ` Justin Pryzby <pryzby@telsasoft.com>
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