Unanswered Questions
332 questions with no upvoted or accepted answers
6
votes
1
answer
1k
views
PostgreSQL predicate not pushed down (through join conditions)
Consider the following data model in a PostgreSQL v13 system;
Here, parent table dim contains a small set of reference data, and child table fact contains a much higher volume of records. A typical ...
CommunityBot
- 1
6
votes
0
answers
3k
views
Slow Postgresql query with left join
I have a query on Postgres, and I also have added proper index. Is there anything missing here?
SELECT orders.*, demo.name as d_name
FROM orders
LEFT JOIN users as demo ON demo.id = orders.dr_id
...
CommunityBot
- 1
5
votes
0
answers
155
views
Row estimates incorrect when using RLS and current_setting in policy
Context
Postgres 14.4.
I have a table which has RLS enabled, and I am using values pulled from current_setting in order to determine which rows should be returned when querying the table:
create table ...
- 213
5
votes
0
answers
3k
views
Postgres index on timestamp column not utilized
I have a database with a column called timestamp of type timestamp without timezone, there is a btree index on this column.
I have a query as below
SELECT
*
FROM
employee
WHERE
timestamp >=...
CommunityBot
- 1
5
votes
0
answers
11k
views
Postgres bulk upsert performance
I am doing bulk UPSERT on a table with 30 million rows. The table has just two columns (varchar as a primary key and integer). The input data is imported to the temporary table first, and then bulk ...
CommunityBot
- 1
5
votes
0
answers
4k
views
PostgreSQL High Disk I/O
I am evaluating PostgreSQL as an alternative to Oracle. I have a database with 533 Tables which contain up to 250,000 entries.
For a performance comparison, I built the datbase both on Oracle and ...
CommunityBot
- 1
5
votes
2
answers
638
views
PostgreSQL - How does multicolumn B-Tree index work with order by on 1st column and IN lookup for 2nd?
I created such table (similar to example from http://use-the-index-luke.com/sql/example-schema/postgresql/performance-testing-scalability )
CREATE TABLE scale_data (
section NUMERIC NOT NULL,
...
CommunityBot
- 1
4
votes
0
answers
324
views
Postgres: Queries are too slow after upgrading to PG17 from PG15
Most of the queries got slower after upgrading our postgres from version 15 to 17 using pg_upgrade. I reconfirmed that "vaccuum, analyze" were all taken care.
To debug, instead of upgrade, I ...
- 312
4
votes
0
answers
2k
views
PostgreSQL: filtering on array and ordering produces wrong plan / bad index choice
This is the story if a nightmare... I have this table:
Table "subscriptions"
Column | Type | Collation | Nullable | ...
- 187k
4
votes
0
answers
761
views
PostgreSQL 12 performance
I'm using PostgreSQL on Windows for Planet OSM database and have noticed considerable decrease in performance when upgrading from v10 to 11 or 12.
Here are the details of the experiment I conducted ...
- 103
4
votes
0
answers
754
views
Postgresql update performance?
We have a table which stores about 20 million records:
Table "public.tbl"
Column | Type | Modifiers
-------------------------+------------...
- 233
4
votes
0
answers
205
views
PostgreSQL/PostGIS: Query seems inordinately time-consuming
I'm doing what I would consider a pretty straightforward query on two tables that aren't huge (~626k records in one; ~47k records in the other).
Both tables have GIST indices on their spatial ...
CommunityBot
- 1
4
votes
1
answer
186
views
How can I avoid this poor Postgres query plan for a sparse aggregate query?
I have the following database schema:
CREATE TABLE foo(
id SERIAL PRIMARY KEY,
tag INTEGER NOT NULL,
val INTEGER NOT NULL
);
CREATE INDEX foo_tag ON foo(tag);
CREATE INDEX foo_val ON foo(val);
...
- 79
4
votes
1
answer
6k
views
PostgreSQL Enum : Search on ENUM type
Defined an ENUM
CREATE TYPE currency AS ENUM('GBP', 'EUR', 'USD');
Using this as a type in a table
CREATE TABLE if not exists transaction(
id BIGSERIAL NOT NULL PRIMARY KEY ,
amount NUMERIC(35,4)...
CommunityBot
- 1
3
votes
0
answers
8k
views
UPSERT multiple rows, do ON CONFLICT DO UPDATE SET conditional check/update row-wise
I'm trying to UPSERT multiple rows into a Postgres table (ie. INSERT ON CONFLICT DO UPDATE SET multiple rows).
But I want the ON CONFLICT DO UPDATE SET conditional check/update do be done row-wise (ie....
- 143