Thread: Get master-detail relationship metadata

   Hi All,

I'm working on a client program that iterates over master-detail
relationships in a loop chain.

Pseudo code:

for row_1 in table_1:
     table_2 = get_details(row_1,"table2")
     for row_2 in table_2:
         row_3 = get_details(row_2,"table3")
         .... etc.
                 process_data(row1,row_2,row_3,....)

My task is to write the "get_details" iterator effectively. The obvious
way to do it is to query details in every get_details() call, but that
is not efficient. We have relationships where one master only has a few
details. For 1 million master rows, that would result in execution of
millions of SQL SELECT commands, degrading the performance by
magnitudes. My idea was that the iterator should pre-fetch and cache
data for many master records at once. The get_details() would use the
cached rows, thus reducing the number of SQL SELECT statements needed.
Actually I wrote the iterator, and it works fine in some cases. For example:

producers = get_rows("producer")
for producer in producers:
     products = get_getails(producer,"product")
     for product in products:
         prices = get_details(product,"prices")
         for price in prices:
             process_product_price(producer,product,price)

This works fine if one producer has not more than 1000 products and one
product has not more than 10 prices. I can easly keep 10 000 records in
memory. The actual code executes about 15 SQL queries while iterating
over 1 million rows. Compared to the original "obvious" method,
performance is increased to 1500%

But sometimes it just doesn't work. If a producer has 1 million
products, and one product has 100 prices, then it won't work, because I
cannot keep 100 million prices in memory. My program should somehow
figure out, how much rows it will get for one master, and select between
the cached and not cached methods.

So here is the question: is there a way to get this information from
PostgreSQL itself? I know that the query plan contains information about
this, but I'm not sure how to extract. Should I run an ANALYZE command
of some kind, and parse the result as a string? For example:

EXPLAIN select * from product where producer_id=1008;
                               QUERY PLAN
----------------------------------------------------------------------
  Seq Scan on product  (cost=0.00..1018914.74 rows=4727498 width=1400)
    Filter: (producer_id = 1008)
(2 rows)


Then I could extract "rows=4727498" to get an idea about how much detail
rows I'll get for the master.

Is there any better way to do it? And how reliable is this?


Thanks,

    Laszlo

On 2/3/2011 5:40 AM, Laszlo Nagy wrote:
>
> Hi All,
>
> I'm working on a client program that iterates over master-detail
> relationships in a loop chain.
>
> Pseudo code:
>
> for row_1 in table_1:
> table_2 = get_details(row_1,"table2")
> for row_2 in table_2:
> row_3 = get_details(row_2,"table3")
> .... etc.
> process_data(row1,row_2,row_3,....)
>
> My task is to write the "get_details" iterator effectively. The obvious
> way to do it is to query details in every get_details() call, but that
> is not efficient. We have relationships where one master only has a few
> details. For 1 million master rows, that would result in execution of
> millions of SQL SELECT commands, degrading the performance by
> magnitudes. My idea was that the iterator should pre-fetch and cache
> data for many master records at once. The get_details() would use the
> cached rows, thus reducing the number of SQL SELECT statements needed.
> Actually I wrote the iterator, and it works fine in some cases. For
> example:
>
> producers = get_rows("producer")
> for producer in producers:
> products = get_getails(producer,"product")
> for product in products:
> prices = get_details(product,"prices")
> for price in prices:
> process_product_price(producer,product,price)
>
> This works fine if one producer has not more than 1000 products and one
> product has not more than 10 prices. I can easly keep 10 000 records in
> memory. The actual code executes about 15 SQL queries while iterating
> over 1 million rows. Compared to the original "obvious" method,
> performance is increased to 1500%
>
> But sometimes it just doesn't work. If a producer has 1 million
> products, and one product has 100 prices, then it won't work, because I
> cannot keep 100 million prices in memory. My program should somehow
> figure out, how much rows it will get for one master, and select between
> the cached and not cached methods.
>
> So here is the question: is there a way to get this information from
> PostgreSQL itself? I know that the query plan contains information about
> this, but I'm not sure how to extract. Should I run an ANALYZE command
> of some kind, and parse the result as a string? For example:
>
> EXPLAIN select * from product where producer_id=1008;
> QUERY PLAN
> ----------------------------------------------------------------------
> Seq Scan on product (cost=0.00..1018914.74 rows=4727498 width=1400)
> Filter: (producer_id = 1008)
> (2 rows)
>
>
> Then I could extract "rows=4727498" to get an idea about how much detail
> rows I'll get for the master.
>
> Is there any better way to do it? And how reliable is this?
>
>
> Thanks,
>
> Laszlo
>
>

One way would be to join the master to the detail, and write your code
expecting duplicates.

q = get_rows("select * from product inner join price ... order by
productid, priceid");

lastprodid = ''
for x in q:
    prodid = q.prodid
    if prodid <> lastprodid:
        # we saw the last product, prepare to move to the next product
        lastprodid = prodid

... etc

 > Is there any better way to do it? And how reliable is this?

It makes the sql really easy, but the code complex... so pick your poison.

-Andy