All Questions
Tagged with postgresql-performance sqlperformance
11 questions
1
vote
0
answers
31
views
will CTE run again if we join in other CTE or with a main query? [duplicate]
I have a question regarding whether CTE in a query runs only once or every time it is getting joined.
Causing we are facing a CPU consumption issue in postgres metrics.
if it runs only once then fine. ...
- 59
0
votes
0
answers
71
views
Postgresql slow query performance
One of the Postgres table that I am working on has more 300 million rows. I am trying to select rows from that table based on a column (status_code) having possible values as 'Complete', 'In_Progress',...
- 67
2
votes
2
answers
243
views
SQL statement timing out on large data set
Trying to complete a web process, I'm getting the error canceling statement due to statement timeout. Debugging the codebase it turns out that the below query is timing out due to large data set. I ...
- 911
1
vote
3
answers
364
views
How can I speed up this PostgreSQL UPDATE FROM sql query? It currently takes days to finish running
How can I speed up the PostgreSQL UPDATE FROM sql query below? It currently takes days to finish running.
UPDATE import_parts ip
SET part_part_id = pp.id
FROM parts.part_parts pp
WHERE pp.upc = ip.upc
...
- 1,665
2
votes
0
answers
279
views
PostgreSQL - citext perform than text for SELECT
There are mentions regarding the performance issues in citext data type in PostgreSQL.In PostgreSQL, weird issue about citext performance?. I wanted to measure actual performance difference in my ...
- 119
0
votes
0
answers
76
views
PostgreSQL - Why does planner underestimate number of rows when doing a Foreign Scan? [duplicate]
I am running a query with an INNER JOIN through a foreign data wrapper (postgres_fdw), and when I view the EXPLAIN ANALYZE, it shows one of the Foreign Scan subnodes underestimates that it will only ...
- 626
4
votes
2
answers
1k
views
How can I efficiently paginate the results of a complex SQL query?
I have a fairly complex SQL query which first fetches some data into a CTE and then performs several self-joins on the CTE in order to compute a value. Here's an abberivated exmaple, with some ...
- 328
1
vote
1
answer
531
views
Speeding up the query with multiple joins, group by and order by
I have a SQL query as:
SELECT
title,
(COUNT(DISTINCT A.id)) AS "count_title"
FROM
B
INNER JOIN D ON B.app = D.app
INNER JOIN A ON D.number = A.number
INNER JOIN C ON A.id = C.id
GROUP BY C....
- 330
2
votes
1
answer
1k
views
Will NVME SSD speed up heavy queries, e.g. selects at large offsets / table scans etc?
If the database data will be on NVME SSD which let's assume is 4x faster than an ordinary SSD will queries like SELECT something OFFSET 100000 and the like typically also be much faster (of course ...
- 6,193
0
votes
1
answer
234
views
PSQL replacement for stored procedure, too slow
I have property, each property has contracts, each contract has integer field rental_area.
Previously I had to get the rental_area of all contracts summed by property and this worked.
SELECT
...
- 16.6k
4
votes
3
answers
9k
views
postgres large table select optimization
I have to extract DB to external DB server for licensed software.
DB has to be Postgres and I cannot change select query from application (cannot change source code).
Table (it has to be 1 table) ...
- 65