Linked Questions

14
votes
2answers
22k 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 ...
6
votes
1answer
3k 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 ...
2
votes
2answers
3k 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 ...
2
votes
1answer
4k 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 ...
3
votes
1answer
4k 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 ( ...
2
votes
1answer
1k 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 ...
5
votes
1answer
544 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 ...
4
votes
1answer
1k 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 ...
1
vote
1answer
2k 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 ...
1
vote
1answer
333 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 ...
1
vote
1answer
101 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), ...
3
votes
1answer
67 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 ...
1
vote
1answer
47 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 ...