All Questions
Tagged with index execution-plan
126 questions
0
votes
0
answers
47
views
Why does Postgres use filter instead of index scan when there are non-negligible number of null records?
This is my table:
learning_postgres=# create table r(id serial primary key, x int not null, y int);
CREATE TABLE
learning_postgres=# create index r_x_btree_idx on r using btree (x);
CREATE INDEX
...
- 155
17
votes
2
answers
2k
views
Unnecessary sort with TOP PERCENT?
Setup
-- Create a heap table of numbers from 1 to 100
SELECT TOP (100)
i = IDENTITY(int, 1, 1)
INTO #T
FROM master.dbo.spt_values;
-- Add a clustered primary key
ALTER TABLE #T
ADD PRIMARY ...
- 95.3k
2
votes
2
answers
179
views
How to predict whether a query plan will involve the use of an index?
I am using the latest PostgreSQL docker image to create a local DB (on an Apple M1 Pro - MacOS Sonoma 14.5 machine). I create a table table0 with a single column col0 within it, and fill it with ...
- 169
2
votes
1
answer
140
views
Query plan seems to depend on time interval between query and table setup?
I am working with PostgreSQL 17. As an example, I have a table table0 with a column col0, with randomly generated strings as GIN-indexed values. I use the following setup.sql to create such a table:
...
- 169
0
votes
2
answers
238
views
Postgres query planner suddenly changing his mind
My Postgres table has a range column containing timestamps with time zone.
I have created an index on the lower bound of the range, like so:
CREATE INDEX bdg_sys_period_start_idx ON building USING ...
- 121
0
votes
3
answers
286
views
Why is the Postgres query planner choosing the wrong strategy?
I have a Postgres/Postgis table with ~50M rows. I am making requests similar to this:
select * from building b where ST_Intersects(b.point, some_fixed_geometry)
order by id limit 20 offset X;
The ...
- 121
1
vote
1
answer
309
views
SQL Server management Studio does not show Index recommendation
I have installed SQL Server management studio version 20 (and also tried with 19 and 18) and surprisingly unable to see the Missing Index Hints while explaining a SQL Query. Whenever i receive a SQL ...
- 11
1
vote
0
answers
401
views
Why is PostgreSQL performing a sequential scan, except when my CTE is materialized?
The issue
My application is encountering performance issues on a query that, to the best of my understanding, should be performant. In this post, I have simplified the query and schema while still ...
- 111
0
votes
1
answer
203
views
Is it possible to use both GIN index and pgvector index for querying in postgres?
I have a table like below in postgres:
create table posts (
id bigserial,
tags text[],
content text,
content_embedding vector(512)
);
create index on posts using GIN(tags);
-- from ...
0
votes
2
answers
111
views
Query is much slower with indexes
For a select query on a table with a few million rows, I notice that the performance is much slower with indexing.
I have replicated the problem on an example table with fewer columns:
CREATE TABLE ...
- 833
0
votes
3
answers
237
views
Increased LIMIT results in performance degradation
This appears to be a variation of a common problem where a small change to the LIMIT clause in a query changes the query plan to one that has vastly inferior performance. In this case, I have two ...
- 1
2
votes
3
answers
1k
views
EXPLAIN ANALYZE does not show what takes time with my GIN index scan
Context
I have a table named companies_establishments that holds ~33M rows.
I created a GIN index with trigrams, so I can make LIKE queries much faster.
CREATE INDEX companies_establishments_id_index ...
- 123
2
votes
0
answers
579
views
Postgres: One-to-many relationship with WHERE, ORDER BY and LIMIT not scalable?
I'm currently using Postgres 12.
A few years ago, I set up the following model: a lot table with a one-to-many relationship to a line table. My idea was to mutualize data that are common for multiple ...
- 21
1
vote
1
answer
597
views
Using btree index with LIKE operator but not equal (=)
In summary, Postgres prefers doing the equivalent of a [datetime index seqscan]+[filter (using the heavy function)] instead of using the index when using = to find what I like. When simply replacing = ...
- 113
5
votes
1
answer
447
views
How to optimize PostgreSQL OR query on two indexed columns
I have a large partitioned table that stores monetary transactions between accounts.
CREATE TABLE "transactions" (
"from" BYTEA NOT NULL -- sender account
,"to&...
- 157