Linked Questions
14 questions linked to/from Can Postgres use an index-only scan for this query with joined tables?
1
vote
1
answer
104
views
Optimizing expensive GROUP BY / ORDER BY query on 690,000 row table
I am writing a query to calculate hiscore rankings for a game. It performs a GROUP BY to aggregate hiscore statistics for individual player stats that are stored across 23 different rows (separated by ...
- 13
5
votes
1
answer
856
views
Pre Caching Index on a large table in PostgrSQL
I have a table with about 10mln rows in it with a primary key and an index defined on it:
create table test.test_table(
date_info date not null,
string_data varchar(64) not null,
data ...
- 181
1
vote
1
answer
226
views
How to prevent seek pagination from doing sequential scan over entire table
I have 3 tables and a materialized view:
resource_categories contains all category names and metadata
create table if not exists resource_categories (
category_id INT,
title VARCHAR(255),
...
- 266
1
vote
1
answer
529
views
Multiply by value from related row with latest date
I have two tables, one with staff time entries, another with staff rates starting from a particular date:
time_table
id | staff_id | entry_date | hours
----------------------------------
1 | 1 ...
- 181
4
votes
1
answer
2k
views
Slow left join lateral in subquery
I have three tables: units (apartment units), leases, and line_items (a better name would have been recurring rents). A unit has many leases and lease has many line items (associated with each rent ...
- 143
3
votes
1
answer
5k
views
PostgreSQL performance with (col = value or col is NULL)
This question deals with PostgreSQL 9.5 query performance.
The table is:
CREATE TABLE big_table
(
id integer NOT NULL,
flag bigint NOT NULL,
time timestamp with timezone NOT NULL,
val ...
- 83
1
vote
1
answer
3k
views
Improve Performance on GROUP BY - large table PostgreSQL
I have a Postgres table containing the following three columns:
origin (INT, b tree index)
destination (INT, b tree index)
minutes (SMALLINT, b tree index)
The table has ~ 1.4 billion rows. Each row ...
- 11
1
vote
1
answer
60
views
Merge 2 columns and replace with specific output
The setup is a Debian 8 with Postgres 9.4 (64bit if that matters).
Given following table I want to "search" for a combination of columns first and column sub and replace their output with another ...
- 405
6
votes
1
answer
5k
views
Optimize Bitmap Heap Scan
I am trying to understand why my query takes a really long time even though I have the required columns indexed:
SELECT entity_id,
id,
report_date
FROM own_inst_detail
WHERE ( ...
- 185
2
votes
2
answers
4k
views
Optimizing a simple query joining two big tables
I don't understand why the following query is so slow (with Postgresql 9.6):
SELECT s.pkid AS pkid_site , s.geom AS geom_site , z.pkid AS pkidEmprise, z.geom AS geom_emprise, z.précision_contour
FROM ...
- 291
6
votes
1
answer
4k
views
What is the recommended way to join junction tables for efficient ordering/pagination?
Summary: I have a simple database schema but even with just a few 10's of thousands of records the performance on basic queries is already becoming a problem.
Database: PostgreSQL 9.6
Simplified ...
- 163
2
votes
1
answer
2k
views
Efficiently return two aggregated arrays from a m:n table
I have a table for a many-to-many relationship over a users table to represent a follow relationship between users:
CREATE TABLE users (
id text PRIMARY KEY,
username text NOT NULL
);
CREATE ...
- 193
3
votes
1
answer
71
views
Return values at more/less specific time points
In PostgreSQL 10.0, I have a big table with two columns:
CREATE TABLE xs (
ts timestamp with time zone NOT NULL,
x integer NOT NULL
)
Now, if I have 100 given time points, how do I return ...
- 177
15
votes
2
answers
28k
views
Do covering indexes in PostgreSQL help JOIN columns?
I have a whole lot of tables that look vaguely like this:
CREATE TABLE table1(id INTEGER PRIMARY KEY, t1c1 INTEGER, t1c2 INTEGER);
CREATE TABLE table2(id INTEGER PRIMARY KEY, t1 INTEGER REFERENCES ...
- 610