Groups > comp.databases.postgresql > #423

Best way to do recursive query for tree like records ?

Newsgroups	comp.databases.postgresql
Date	2012-11-22 20:32 -0800
Message-ID	<8d44d8a0-8c8b-4a40-bb70-15f6a430d8ee@googlegroups.com> (permalink)
Subject	Best way to do recursive query for tree like records ?
From	Ziliang Chen <zlchen.ken@gmail.com>

Show all headers | View raw

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!

Back to comp.databases.postgresql | Previous | Next — 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