Skip to main content

All Questions

Filter by
Sorted by
Tagged with
5 votes
1 answer
324 views

Why does this CTE return the bulk of its results immediately, but then take several minutes to finish?

I've got a schema roughly like this (this is a simplification of my actual schema): CREATE TABLE foo ( key1 NUMERIC(6) NOT NULL, key2 VARCHAR(32) NOT NULL, val VARCHAR(255) NULL, ...
JAF's user avatar
  • 163
4 votes
1 answer
2k views

How to optimize a recursive query in Postgresql?

I have a recursive query that takes too long - 30+ ms where doing the individual queries to extract the same data manually takes < 0.12 ms. So we're talking 250x as long. I have the following ...
Robert Elliot's user avatar
4 votes
3 answers
2k views

Best way to design / query multi-level relationship table

My company is going to develop a mobile app selling services across different regions. We are expecting users are going to hugely depend on the Search function. Thus, I need to make sure the database ...
Wai Hong's user avatar
4 votes
1 answer
11k views

Recursive cte avoiding loops

I'm trying to write a recursive CTE to explore a workflow system. Unfortunately, I'm getting maximum recursion errors due to loops: with cteActs as ( select a.id as [id], aa.TASKNEXTID as [...
Paul's user avatar
  • 1,463
3 votes
2 answers
560 views

Optimization of recursive view

I have an optimization problem when using a recursive view on PostgreSQL. When I perform a trivial query using this view, the execution time is abnormally long. To expose my problem, here is a ...
Gabriel Valvin's user avatar
2 votes
1 answer
3k views

Finding the end of a Relationship chain optimally

I have a table that contains an id column, a parent column, and it's child column. So for every record, I know what record comes before it and what will come after it in a chain relationship, but from ...
Eric S's user avatar
  • 503
2 votes
2 answers
3k views

Optimizing a recursive CTE or replacing it with a temporary table

I have a recursive query like this: with recursive PCte(id) as ( select p1.id from Product p1 where p1.parent_id is null and p1.serial_number in ('123', '124','125', 'n') union all ...
Itan Reimbergh's user avatar
0 votes
1 answer
1k views

Filtering a recursive CTE as view

i'm using MSSQL Server 2017 and have a CTE used in a view to get the root element of a hirarchical tree linked to one specific element. If the CTE is used directly and the base elements are allready ...
Findnix's user avatar
0 votes
0 answers
235 views

Optimising a recursive SQL query that processes several million records in BQ

I need help optimizing a recursive SQL query in BQ. I have hierarchical data stored in a table as parent-child relationships, i.e will be stored as parent_item_id child_item_id 1 1 1 2 1 3 3 4 ...
Roman Dryndik's user avatar
0 votes
0 answers
385 views

How to optimize (or get an alternative faster solution to) this recursive query that groups using recursive CTEs in Postgres?

I am on the quest to implement an NMS filter for labels in a Database. For context, here's a link to my first question, where I explain what NMS is and what I'm trying to achieve (I will edit that ...
J Pablo F's user avatar
0 votes
1 answer
116 views

Optimizing query that finds closest unique records

I've been trying out different queries to get the best performance when querying hierarchical data on Postgres 13.6. Fiddle: https://dbfiddle.uk/060f_h96 Explain: https://explain.dalibo.com/plan/...
mreaglejr's user avatar
0 votes
0 answers
31 views

Fastest way of updating a spider web of data

I have a large table with over 60 million rows, which has relational data inside of it. I need to update each of these rows to the newest date, of the newest member, including members that that ...
Justin M's user avatar