All Questions
Tagged with postgresql-performance performance
56 questions
0
votes
1
answer
118
views
How to improve performance of the following query to get nearby ship location
I have a Postgres table named shipData(total rows 700 millions). Which has ship location with reporting time and location in geometry format(combination of lat & lon). I have a shipID, I want to ...
- 39
-1
votes
2
answers
65
views
PostgreSQL better query performance
I have customers and visits table, and I would like to know which of these two queries has the better performance:
(I have indexes defined for those columns)
Query 1
SELECT
customers.id as id,
...
- 599
1
vote
1
answer
407
views
Query stops parallelizing when adding LIMIT or ROW_NUMBER
I have 20 tables with identical structure, and I need to aggregate the results.
I'm using UNION ALL to collect the results, in the following structure:
SELECT something, SUM(total) AS overall_total
...
- 11
0
votes
1
answer
149
views
Different execution time for the same query with different time ranges
I have this query running on a Postgres 12 server with WHERE datetime BETWEEN datetimeA and datetimeB. Time difference between datetimeA and datetimeB is always 30 minutes, number of data involved is ...
0
votes
1
answer
406
views
Query with small LIMIT gets slow with many hits
I have a slow Postgres query and I may need better indexes. But I lack experience with indexing, so I just indexed foreign keys. I use Postgres 14.3 and Django 3.0.
This is the slow query. It's only ...
- 215
0
votes
0
answers
275
views
PostgreSQL slow query when querying large table by PK (int)
We have a large table (265M rows), which has about 50 columns in total, mostly either integers, dates, or varchars. The table has a primary key defined on an autoincremented column.
A query loads temp ...
- 325
0
votes
1
answer
914
views
Optimizing row_number to not scan all table
I have a table created as
CREATE TABLE T0
(
id text,
kind_of_datetime text,
... 10 more text fields
),
PRIMARY KEY(id, kind_of_datetime)
It is about 31M rows with about 800K of unique id ...
- 73
0
votes
1
answer
35
views
inneficient subquery postgresql
Hi I have this query:
select distinct r.fparams::json->>'uuid_level_2' as uuid_level_2
from jhft.run r
where r.ts_run >= :ts_run
which returns in 323ms:
49c954c3-9d57-4777-99cb-...
0
votes
1
answer
520
views
Postgres data base need suggestion creating an index for table
I a have a table structure as below. For fetching the data from table I am having search criteria as mentioned below. I am writing a singe sql query as per requirement(sample query I mentioned below). ...
- 1
3
votes
1
answer
2k
views
Why query in wrapped PLPGSQL function is much slower than itself?
Have been working with PostgreSQL 12.8 (Ubuntu 12.8-1.pgdg18.04+1) for a while. We have a significant time difference between normal queries and the same queries wrapped inside PLPGSQL functions with ...
- 1,345
3
votes
2
answers
2k
views
Performance impact of view on aggregate function vs result set limiting
The problem
Using PostgreSQL 13, I ran into a performance issue selecting the highest id from a view that joins two tables, depending on the select statement I execute.
Here's a sample setup:
CREATE ...
- 1,741
1
vote
5
answers
2k
views
WHERE clause is slower with value from CTE than with constant?
I want to have a variable cached during a query performing on Postgres 12. I followed the approach of having CTE like below:
-- BEGIN PART 1
with cached_vars as (
select max(datetime) as ...
- 305
1
vote
1
answer
3k
views
Why does distinct on in subqueries hurt performance in PostgreSQL?
I've got a table users with fields id and email. id is the primary key and email is indexed as well.
database> \d users
+-----------------------------+-----------------------------+-----------------...
- 975
0
votes
0
answers
89
views
Speed of Postgres SELECT
I am quite new to optimizing the speed of a select, but I have the one below which is time consuming. I would be grateful for suggestions to improve performance.
SELECT DISTINCT p.id "pub_id",
...
- 111
0
votes
1
answer
60
views
Postgres SQL - strange performance issue in select
I have query (simplified version):
WITH temp AS (SELECT id, foo(id) AS foo FROM test)
SELECT id FROM temp WHERE foo = 4;
foo(id) is function that returns 0, 2 or 4 (only these values)
Above query ...
- 743