All Questions
Tagged with index-tuning postgresql
242 questions
0
votes
0
answers
60
views
Increased query latency in Postgres after reindex
I reindexed (concurrently) an index on the biggest table used in a query, but for some reason the latency of the query has increased from aroun 6 ms to 150-200 ms. The reindex was performed because ...
- 1
0
votes
2
answers
70
views
optimise postgresql query with group by and calculated value on a big table
In PostgreSQL 16.9 I have a table Time (duration, resourceId, date, companyId) representing timesheet entries and table Resources (id, name);
I want to list sum of Time durations per week and employee ...
- 11
0
votes
1
answer
114
views
Postgres Query Slow 0.05% of the time
I have the following tables:
CREATE TABLE IF NOT EXISTS users
(
id NUMERIC(20, 0) NOT NULL DEFAULT NEXTVAL('users_sequence') PRIMARY KEY,
list_id ...
- 175
0
votes
1
answer
61
views
How does PostgreSQL Use my composite index without the middle column in the query?
I have the following table:
CREATE TABLE transactions
(
id NUMERIC(20, 0) NOT NULL DEFAULT NEXTVAL('transactions_sequence') PRIMARY KEY,...
- 175
0
votes
0
answers
57
views
Why does Postgresql use a Seq Scan on the first column of the PK?
This is my table:
CREATE TABLE IF NOT EXISTS public.ob_samples_sc5555 (
stamp timestamp(6) with time zone NOT NULL,
oblvl smallint NOT NULL,
olots integer NOT NULL,
CONSTRAINT ...
- 413
0
votes
0
answers
193
views
How to do fuzzy text search in PostgreSQL using multiple columns with GIN or GIST (against arbitrary unicode strings)?
A few questions related to properly indexing and querying words by prefix/suffix/contained in a fuzzy way.
Say I have this relevant words table, and referenced languages table:
CREATE TABLE languages (...
- 221
0
votes
2
answers
91
views
Aggregate the last two entries per item efficiently
I'd like your help to optimise an increasingly slow query - or better yet, help me understand what is the problem and point me in the right direction.
Every day I scrape 3 supermarkets and I record ...
- 103
0
votes
1
answer
82
views
slow postgres 10 query - have I done something stupid with my indexes?
I have a database with a large (50M) table of time series observations and locations (900 rows). The following query takes quite some time:
SELECT *
FROM observations
WHERE location IN (SELECT ...
- 101
0
votes
0
answers
54
views
Are multi column indexes for JSONB columns better?
we are using PostgreSQL and we have a table we are using JSONB due to the unstructured nature of the data. We are integrating with a ton of different CRMs, so a part of the records data is pretty ...
1
vote
1
answer
100
views
Huge index size on table
I have a large index on a specific table, and I wonder if it's better to drop and recreate it or use the REINDEX command. The table's total size is 322GB, with an index size of 166GB and an actual ...
- 45
1
vote
1
answer
297
views
Can I optimize UPDATE statements using EXPLAIN?
I have a few UPDATE calls on my production system that are running slow and I've been instructed to try and optimize them. I'm reading into how to understand EXPLAIN with (ANALYZE, BUFFERS) as well to ...
- 113
1
vote
1
answer
104
views
Impact of Reordering Composite Index on PostgreSQL: Performance, Space, and Overhead Considerations
I am optimizing SQL query performance by reordering a composite index in PostgreSQL. I need to understand potential repercussions, including space usage, data saving overhead, and any other impacts of ...
- 13
0
votes
1
answer
72
views
PostgreSQL - How to optimize slow Bitmap Heap Scan?
My app is calculating the SuperTrend indicator over the price of multiple cryptocurrencies. I have the following table and indices:
-- DDL generated by Postico 2.1.1
-- Not all database features are ...
- 23
1
vote
0
answers
36
views
Very slow to work with table using PostgreSQL [closed]
I have a table labelled SALES. It has indexes like this list
"sales_pkey" PRIMARY KEY, btree (id)
"sales_order_date_idx" btree (order_date)
"sales_product_id_idx&...
- 111
1
vote
0
answers
155
views
Poor Postgres Query Performance with a Partitioned Table - pg_hint_plan does better
Simplified, I have a table with this layout:
CREATE TABLE IF NOT EXISTS public.scans
(
id bigint NOT NULL DEFAULT nextval('new_scans_id_seq'::regclass),
geo geometry(Point) NOT NULL,
...
- 11