Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.databases.postgresql > #423 > unrolled thread
| Started by | Ziliang Chen <zlchen.ken@gmail.com> |
|---|---|
| First post | 2012-11-22 20:32 -0800 |
| Last post | 2012-11-23 05:47 -0800 |
| Articles | 4 — 3 participants |
Back to article view | Back to comp.databases.postgresql
Best way to do recursive query for tree like records ? Ziliang Chen <zlchen.ken@gmail.com> - 2012-11-22 20:32 -0800
Re: Best way to do recursive query for tree like records ? Jasen Betts <jasen@xnet.co.nz> - 2012-11-23 09:50 +0000
Re: Best way to do recursive query for tree like records ? "M. Strobel" <sorry_no_mail_here@nowhere.dee> - 2012-11-23 11:05 +0100
Re: Best way to do recursive query for tree like records ? Ziliang Chen <zlchen.ken@gmail.com> - 2012-11-23 05:47 -0800
| From | Ziliang Chen <zlchen.ken@gmail.com> |
|---|---|
| Date | 2012-11-22 20:32 -0800 |
| Subject | Best way to do recursive query for tree like records ? |
| Message-ID | <8d44d8a0-8c8b-4a40-bb70-15f6a430d8ee@googlegroups.com> |
Hi Guys, I have billions of tree like records in PostgreSQL 9.2 database on RHEL 5 X64, what is the best way to do the query in PostgreSQL, like : "find out all records (grand records ...) under one (several) specific parent node(s) ? or find out all parent nodes (grandparent nodes...) for one (several) specific child node(s) ?" PS: the billions of records are partitioned into several tables, we can treat each sub table will contain around 10 million records and usually we only query the tree records in only one sub table. I tried to use "with recursive and connectby", but the performance is not good. For with recursive, it takes 2+ mins to find out all 10 millions child records belong to a specific parent node. Proper indexing is created. For connectby, it never finishes. Thanks a zillion!
[toc] | [next] | [standalone]
| From | Jasen Betts <jasen@xnet.co.nz> |
|---|---|
| Date | 2012-11-23 09:50 +0000 |
| Message-ID | <k8ngtu$3oa$1@reversiblemaps.ath.cx> |
| In reply to | #423 |
On 2012-11-23, Ziliang Chen <zlchen.ken@gmail.com> wrote: > Hi Guys, > > I have billions of tree like records in PostgreSQL 9.2 database on > RHEL 5 X64, what is the best way to do the query in PostgreSQL, like : > "find out all records (grand records ...) under one (several) specific > parent node(s) ? or find out all parent nodes (grandparent nodes...) > for one (several) specific child node(s) ?" Not clear. parentheses mean what? -- ⚂⚃ 100% natural --- news://freenews.netfront.net/ - complaints: news@netfront.net ---
[toc] | [prev] | [next] | [standalone]
| From | "M. Strobel" <sorry_no_mail_here@nowhere.dee> |
|---|---|
| Date | 2012-11-23 11:05 +0100 |
| Message-ID | <ah902qFtbtU1@mid.uni-berlin.de> |
| In reply to | #423 |
Am 23.11.2012 05:32, schrieb Ziliang Chen:
> Hi Guys,
>
> I have billions of tree like records in PostgreSQL 9.2 database on RHEL 5 X64, what is the best way to do the query in PostgreSQL, like : "find out all records (grand records ...) under one (several) specific parent node(s) ? or find out all parent nodes (grandparent nodes...) for one (several) specific child node(s) ?"
>
> PS: the billions of records are partitioned into several tables, we can treat each sub table will contain around 10 million records and usually we only query the tree records in only one sub table.
> I tried to use "with recursive and connectby", but the performance is not good. For with recursive, it takes 2+ mins to find out all 10 millions child records belong to a specific parent node. Proper indexing is created. For connectby, it never finishes.
>
> Thanks a zillion!
>
It depends how you represent the data in your tables. Then ask your favorite search
engine for "sql hierarchical data" or something like that.
If you have such a big database it is probable that you are quite knowledgeable about
sql, and would better start a discussion with the performance experts on
gmane.comp.db.postgresql.{general,sql,performance}
/Str.
[toc] | [prev] | [next] | [standalone]
| From | Ziliang Chen <zlchen.ken@gmail.com> |
|---|---|
| Date | 2012-11-23 05:47 -0800 |
| Message-ID | <d816494a-9656-4de7-af79-7cfc6c215bcd@googlegroups.com> |
| In reply to | #425 |
On Friday, November 23, 2012 6:05:15 PM UTC+8, M. Strobel wrote:
> Am 23.11.2012 05:32, schrieb Ziliang Chen:
>
> > Hi Guys,
>
> >
>
> > I have billions of tree like records in PostgreSQL 9.2 database on RHEL 5 X64, what is the best way to do the query in PostgreSQL, like : "find out all records (grand records ...) under one (several) specific parent node(s) ? or find out all parent nodes (grandparent nodes...) for one (several) specific child node(s) ?"
>
> >
>
> > PS: the billions of records are partitioned into several tables, we can treat each sub table will contain around 10 million records and usually we only query the tree records in only one sub table.
>
> > I tried to use "with recursive and connectby", but the performance is not good. For with recursive, it takes 2+ mins to find out all 10 millions child records belong to a specific parent node. Proper indexing is created. For connectby, it never finishes.
>
> >
>
> > Thanks a zillion!
>
> >
>
>
>
> It depends how you represent the data in your tables. Then ask your favorite search
>
> engine for "sql hierarchical data" or something like that.
>
>
>
> If you have such a big database it is probable that you are quite knowledgeable about
>
> sql, and would better start a discussion with the performance experts on
>
> gmane.comp.db.postgresql.{general,sql,performance}
>
>
>
> /Str.
Thanks guys. I find a way to solve this problem.
[toc] | [prev] | [standalone]
Back to top | Article view | comp.databases.postgresql
csiph-web