Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #425

Re: Best way to do recursive query for tree like records ?

Show all headers | View raw

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.

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

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

csiph-web