Linked Questions
31 questions linked to/from Can spatial index help a "range - order by - limit" query
95
votes
5
answers
42k
views
Working of indexes in PostgreSQL
I have a couple of questions regarding working of indexes in PostgreSQL.
I have a Friends table with the following index:
Friends ( user_id1 ,user_id2)
user_id1 and user_id2 are foreign keys to ...
- 1,983
38
votes
1
answer
78k
views
Index optimization with dates
I have a large table of objects (15M+ row) in PostgreSQL 9.0.8, for which I want to query for outdated field.
I want to divide the query by millions, for scalability & concurrency purposes, and I ...
- 561
16
votes
3
answers
11k
views
Algorithm for finding the longest prefix
I have two tables.
First one is a table with prefixes
code name price
343 ek1 10
3435 nt 4
3432 ek2 2
Second is call records with phone numbers
number time
834353212 10
...
- 285
21
votes
2
answers
15k
views
What index to use with lots of duplicate values?
Let's make a few assumptions:
I have table that looks like this:
a | b
---+---
a | -1
a | 17
...
a | 21
c | 17
c | -3
...
c | 22
Facts about my set:
Size of the whole table is ~ 1010 ...
- 313
12
votes
3
answers
45k
views
GROUP BY one column, while sorting by another in PostgreSQL
How can I GROUP BY one column, while sorting only by another.
I'm trying to do the following:
SELECT dbId,retreivalTime
FROM FileItems
WHERE sourceSite='something'
GROUP BY seriesName
...
- 1,336
10
votes
2
answers
8k
views
Are results from UNION ALL clauses always appended in order?
As per standard SQL UNION / UNION ALL do not guarantee any particular sort order without an outer ORDER BY clause - like there is hardly any place in SQL where sort order is guaranteed without ORDER ...
- 176k
12
votes
2
answers
12k
views
Why would adding LIMIT 200 cause a query to slow down?
I am trying to debug a slow query on a PostgreSQL 9.1.13 database, and I am a bit at loss. The exact query generated by the ORM framework is:
SELECT "core_product"."sales_price", "core_product"."...
- 241
7
votes
3
answers
2k
views
Postgres sometimes uses inferior index for WHERE a IN (...) ORDER BY b LIMIT N
We have a PostgreSQL table with ~5 billion rows that has developed a nasty habit of missing the proper indices and doing a Primary Key scan on certain LIMIT operations.
The problem generally ...
- 185
6
votes
4
answers
16k
views
Does it make sense to have CASE .. END in an ORDER BY?
Queries like SELECT * FROM t ORDER BY case when _parameter='a' then column_a end, case when _parameter='b' then column_b end are possible, but: Is this a good practice?
It is common to use parameters ...
- 13.4k
4
votes
3
answers
7k
views
Optimize a query on two big tables
I have a very important query in my system that is taking too long to execute due to huge amount of data on tables. I'm a junior DBA and i need the best optimization I can get for this. Tables have ...
8
votes
2
answers
10k
views
Filter on array text[] and sort on timestamp
Description
PostgreSQL 9.6 on Linux, size of tags_tmp table ~ 30 GB (10 million rows), tags is a text[] and has only 6 values.
tags_tmp(id int, tags text[], maker_date timestamp, value text)
id ...
- 1,962
7
votes
1
answer
6k
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 ...
- 173
3
votes
2
answers
8k
views
Real time much greater than `EXPLAIN ANALYZE`'s execution time (index scan)
I want to fetch up to 100 rows based on their id. The id is the primary key of the table.
The query that I had written looks like this:
select * from table where id = any ($1);
where $1 is ...
- 525
4
votes
2
answers
7k
views
Why is this query with WHERE, ORDER BY and LIMIT so slow?
Given this table posts_lists:
Table "public.posts_lists"
Column | Type | Collation | Nullable | Default
------------+------------------------+-----------+-...
- 43
2
votes
4
answers
5k
views
MySql Order by isnull() Performance Problem
my sql below is use for listing stock added from 10 days ago.
Order by isnull(Price) is use so that stock without any price yet will still being listed.
AddDate and Price has an index.
SELECT Id, ...
- 31
6
votes
2
answers
1k
views
Optimize a query with small LIMIT, predicate on one column and order by another
I'm using Postgres 9.3.4 and I have 4 queries that have very similar inputs but have vastly different response times:
Query #1
EXPLAIN ANALYZE SELECT posts.* FROM posts
WHERE posts.source_id IN (...
- 239
3
votes
4
answers
2k
views
Fastest way to choose distinct rows and a different order by without using a subquery
PostgresSQL - Join and get distinct left table rows only without gigantic subquery sort
We have a midsized database that has 50k rows of products and 33m rows of product prices at various store ...
- 53
6
votes
1
answer
615
views
How to reduce query size with many repeated UNION subqueries?
I use Postgres 13 and have a table defined with the following DDL:
CREATE TABLE item_codes (
code bytea NOT NULL,
item_id bytea NOT NULL,
time ...
- 185
0
votes
2
answers
3k
views
How to speed up single-column date range query in Postgres?
How would you speed up a Postgres query that's trying to filter on a date column between a start and end date?
I'm running a query like:
SELECT * FROM record WHERE tag_id IN (1,2,3) AND person_id = 1 ...
- 1,355
3
votes
2
answers
1k
views
Migrating and normalizing data
We are rewriting a large Django application which uses a MySQL database, and the database schema needs to undergo major modifications. We're also porting the app to use PostgreSQL, for a couple of ...
- 402
0
votes
2
answers
2k
views
How to search a table with 80 million records faster?
I have a table with about 80 million records, I want to find all the activities of lists and workspaces that a user has access to. So first, I get the ids of the lists and workspaces and then I run ...
- 1
5
votes
1
answer
530
views
Index Definition Order and ORDER BY Clause
So i was doing my morning blog reading and stumbled upon this fun exercise:
https://www.erikdarlingdata.com/sql-server/lets-design-an-index-together-part-3/
Here is the query in question from the ...
- 349
1
vote
1
answer
1k
views
JSONB Array of Strings (with GIN index) versus Split Rows (B-Tree Index)
I have a database which stores receiver to indicate which account the data relates to. This has led to tons of duplication of data, as one set of data may create 3 separate rows, where all column data ...
- 13
0
votes
3
answers
1k
views
Postgressql text search: pg_trgm index with order by going very slowly
I have a really slow query(~30 seconds) on a large text search DB that I could really use some help with.
I'm searching for ordered text, in a long file path string. So the path could be: folder1/cat ...
- 99
2
votes
1
answer
1k
views
OFFSET and LIMIT on complex query
I am doing a pretty complex query in Postgres 9.3.4:
SELECT p.*
FROM unnest('{19082, 19075, 20705, 18328, 19110, 24965, 18329, 27600
, 17804, 20717, 27598, 27599}'::int[]) s(source_id)...
- 239
0
votes
3
answers
527
views
Array containment queries in Postgres
Question on GIN index on array.
I have 2 million rows in a work table.And I need to find work that a user can do, based on the skills he has and also the highest priority work. User always will have ...
- 103
4
votes
0
answers
1k
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 | ...
- 613
8
votes
3
answers
481
views
How to optimize indexes on MySQL query with various sorts
I have an INNODB table levels:
+--------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+---------...
- 269
2
votes
2
answers
88
views
Simple query with two WHERE clauses uses inferior index scan
I have a table named "db_log" with a size of 11GB and approximately 10 million rows in PostgreSQL 13.7 version. The issue is that a simple query is not performing an full index scan for ...
- 31
3
votes
2
answers
113
views
Looking for a possible multi-column index
I have a table highscore containing the columns:
game (text)
date (timestamp)
score (integer)
more irrelevant ones...
The query most often run on it is:
SELECT *
FROM highscore
WHERE game = :...
