All Questions
Tagged with postgresql-performance aggregate-functions
8 questions
2
votes
1
answer
915
views
Slow LEFT JOIN on CTE with time intervals
I am trying to debug a query in PostgreSQL that I've built to bucket market data in time buckets in arbitrary time intervals. Here is my table definition:
CREATE TABLE historical_ohlcv (
...
- 2,480
16
votes
2
answers
1k
views
Best performance in sampling repeated value from a grouped column
This question is about the functionality of first_value(), using another function or workaround.
It is also about "little gain in performance" in big tables. To use eg. max() in the explained context ...
- 14k
0
votes
4
answers
1k
views
Counting preceding occurences of an event within a given interval for each event row with a window function
I have table storing events occurring to users as shown in http://sqlfiddle.com/#!15/2b559/2/0
event_id(integer)
user_id(integer)
event_type(integer)
timestamp(timestamp)
A sample of the data looks ...
- 1,723
1
vote
4
answers
106
views
Use the results of SUM in the same query
SELECT
business_period,
SUM(transaction.transaction_value) AS total_transaction_value,
SUM(transaction.loss_value) AS total_loss_value,
(total_transaction_value - total_loss_value) AS net_value
FROM ...
- 20.7k
2
votes
3
answers
2k
views
Why does the following join increase the query time significantly?
I have a star schema here and I am querying the fact table and would like to join one very small dimension table. I can't really explain the following:
EXPLAIN ANALYZE SELECT
COUNT(impression_id), ...
- 8,662
2
votes
1
answer
2k
views
PostgreSQL cumulative count within last X months
Given the following table:
CREATE TABLE cnts(
user_id INT,
month_d DATE,
cnt INT
)
I want to query cumulative counts for the last 6 months of each (user_id, month_d) pair. I can do it with the ...
- 4,934
5
votes
2
answers
2k
views
Optimize a max() aggregate that works for "no rows", too
I have a query that I am trying to optimize for PostgreSQL 9.2:
select coalesce(max(id),0) as m from tbl
It takes for forever to run, so I figured I could rewrite it as
select id from tbl order by ...
- 190
2
votes
1
answer
897
views
PostgreSQL query efficiency
I'm working with PostgreSQL (I'm a rookie in the database world) and I'd like to know your opinion on the efficiency of this kind of queries I found in the code I'm working with. These queries have a ...
- 43