Re: Convert Cursor to array - Mailing list pgsql-general

hi

i'm a friend of nicolas and i'm working with him on this project

so this is the problem :
we are working on an application allowing to manage documentation in xml format.
At the the first level, there is the root, and at the next level there are the childs (example : chapters).
the application allows to make evolve docs (by adding chapters for example).
so, if we consider 2 chapters and that we want to add one between them, we need to have an index.
the first idea was to re-number all the index chapter when 1 was added, but it was too slow.
the second idea was to use float index. if we consider chapter 1 with index 1.0 and chapter 2 with index 2.0, and that we want to add new one between them, we set the new index at 1.5 but performances wasn't good.

so we choice to use a different solution which consist on using the index of a chapter and its evolution.
if we have this data :
chapter_id | evolution | index
   1       |     0     |   1
   2       |     0     |   2
   3       |     0     |   3
   4       |     1     |   2

by using our sort function we obtain this :
chapter_id | evolution | index
   1       |     0     |   1
   4       |     1     |   2
   2       |     0     |   2
   3       |     0     |   3

in consequence a new chapter has been added between two others.
this solution works fine under oracle, but under postgresql, performance are bad and we are working on the tunning of the DB.

although this solution works, we don't find it very elegant and we think that we can find a better one.
the principal problem is how to add leaf between two others whitout modifing too much data.
the size of the documents are between 200MB and 800MB;

By hoping to have been clear.

-----Message d'origine-----
De : pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]De la part de Richard Huxton
Envoyé : lundi 14 mars 2005 09:31
À : GIROIRE Nicolas (COFRAMI)
Cc : 'pgsql-general@postgresql.org'
Objet : Re: [GENERAL] Convert Cursor to array

GIROIRE Nicolas (COFRAMI) wrote:
> my sort algorithm is a very particular solution.
> In fact, I put record in array to allow me to sort record. I sort them by
> using one column of my table but the column indx can have duplicate value
> and it's volontary.

I still can't see why you can't sort by that column with "order by"
There's nothing obvious in your description that rules that out. Are you
aware that PostgreSQL offers functional and partial indexes? They can
make complex selection/ordering quite efficient.

> here is the order algorithm :
> for i in 1..recordcount loop                 
>                       tmp_row := children[i];
>                       indx := tmp_row[5];
>
>                       if (indx<i) then
>                               -- on déplace les éléments pour laisser la
> place à l'élément qu'on déplace
>                               for j in 0..(i-indx-1) loop
>                                       children[i-j] := children[i-j-1];
>                               end loop;
>                               -- on met l'élément à sa nouvelle position
>                               children[indx] := tmp_row;
>                       end if;
>               end loop;
>
> It's particular to my system. I won't go to explain my choice but if someone
> has an idea to save time.

If you are going to sort, this looks a particularly slow algorithm for
large values of "recordcount". Personally, I'd use plperl/plpython or
something with built-in hash sorting capabilities. That will almost
certainly be tens or hundreds of times faster.

--
   Richard Huxton
   Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

This mail has originated outside your organization,
either from an external partner or the Global Internet.
Keep this in mind if you answer this message.