Linked Questions

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 ...
user avatar
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 ...
user avatar
  • 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), ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ( ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 610