Alexandre Leclerc (alexandre.le...@gmail.com)

Hi all,

I don't want to double post, but I see nothing hapening in the general
mailling list, so I post here in case any one has an idea about what
is going on.

We have a column (varchar) that has plain text time and it is indexed.
When I do a query with the index, all the data is in the right order,
but when I user ORDER BY .. DESC, the order is messed up. Example:

By index 1: (date, time, data)
SELECT * from t1;
date (date type)  time (varchar)  data
2007-01-17         8h40           d1
2007-01-30         9h30           d2
2007-01-30        12h00           d3
2007-01-30        13h45           d4
2007-01-30        17h20           d5

SELECT * from t1 ORDER BY date, time DESC;
date (date type)  time (varchar)  data
2007-01-30         9h30           d2
2007-01-30        17h20           d5
2007-01-30        13h45           d4
2007-01-30        12h00           d3
2007-01-17         8h40           d1

I don't know why, this is like if the 'time' varchar was trimmed then
used for the ordering.

How can I fix that so that the result is exactly like the first one but
perfectly reversed in it's order?

Best regards.

By the way, I think the "performance" mailling list would be the ideal
place to post you question since it is all about optimisation (and not
management).

Best regards.

/Usually/, they are always called internally if not by the coder; it
is good practice to call them. It does optimize the speed.

All this is prety straight forward.

As for the transaction you are crating, is the process of 'split
message' actually do on the DB side or are you doing this process on
you side? Maybe this is the actual work of splitting the message by
the DB that is very slow.

/Maybe/ if you do this data-processing on your side, then sending that
to the DB will save you time. If you really want that job being done
on the server side, you could build yourself an extension module
adding a special function that you would call to do the job. Maybe you
could win couple cycles...

But all that could be confirmed or not by someone else which knows
more about PG internals.

Best regards.

Hi all,

I tried to "move" the /data folder to another HD. The service refuse
to start after that and does not raise any errors.

- Stop service
- Copy /Program Files/PostgreSQl/8.1/data folder to, let say, d:/data
- Changed /Program Files/PostgreSQl/8.1/data/postgresql.conf:
data_directory =3D 'd:/data/'
- Started service, but will stop without any error message

* Under XP home; I cant' tell about the security params of the files
since it looks like in xphome I can't see them. But a copy paste
should leave the things alright.
* No error in windows logs... for what it worth any-way.

I don't know what I'm missing.

Regards.

Sorry, not in the good list. I tought I suscribed to 'admin' list.

Hi all,

I tried to "move" the /data folder to another HD. The service refuse
to start after that and does not raise any errors.

- Stop service
- Copy /Program Files/PostgreSQl/8.1/data folder to, let say, d:/data
- Changed /Program Files/PostgreSQl/8.1/data/postgresql.conf:
data_directory =3D 'd:/data/'
- Started service, but will stop without any error message

* Under XP home; I cant' tell about the security params of the files
since it looks like in xphome I can't see them. But a copy paste
should leave the things alright.
* No error in windows logs... for what it worth any-way.

I don't know what I'm missing.

Regards.

On Fri, 4 Feb 2005 12:48:43 -0500, Merlin Moncure
<merlin.moncure@rcsonline.com> wrote:
> > Alexandre Leclerc wrote:
> > Sorry for jumping in on this thread so late -- I haven't been able to
> > select * from crosstab(
> > 'select product_id, department_id, req_time
> > from product_department_time order by 1',
> > 'select ''A'' union all select ''C'' union all select ''D'''
> > ) as (product_id int, a int, c int, d int);
>
> I forgot you could do this...This would certainly be easier than parsing
> array values returned from array_accum. It will probably be faster as
> well...but with the array approach the query would not have to be
> modified each time a new department was added. That said, a crosstab
> based query could be built easily enough from a department query on the
> client and then you have the best of both worlds.

Hello Merlin,

Well, I'm glad because with all this i've learn a lot of new things.

Finally, the crosstab solution is very fast and is simple for me to
use. I get my super-bug-jumbo-dbkiller-query run in about 210ms
(seeking many tables and so). I had a score of 2480ms before. (This is
a much more complex query; the cross table thing had to be included in
this one.) This is much better! :)

In all, thanks for your help. Regards.

Thank you. The RPM was not installing, but I manage to extract it's
contact and grap the .sql file in the contrib. So I installed the
function manually.

Now it's time to evaluate performance of this! Thanks for your help!

I'm a little bit confused on how to install this contirb. I know my
contrib package is installed, but I don't know how to make it work in
postgresql. (Using 7.4.5-1mdk on Mandrake Linux.)

Yep, this is the only thing I also tought: a trigger to add / remove
columns when the user add or remove a department... but this is not
exactly what I wanted (this is not a very nice db design, from my
perspective).

Thank you for you help.

On Thu, 27 Jan 2005 16:05:09 -0500, Merlin Moncure
<merlin.moncure@rcsonline.com> wrote:
> Alexandre wrote:
> > like I'm doing right now, that "de-normalizing" in an array is the way
> > to go.
>
> Only sometimes. Looping application code is another tactic. There may
> be other things to do as well that don't involve arrays or application
> code. Consider arrays a (very postgresql specific) tool in your
> expanding toolchest.

I take good notes of that. All this opened to me other ways for
solutions, so I'm glad of that. I'll take more time to think about all
that.

> De-normalization is a loaded term because we are only presenting queried
> data in an alternate format (normalization normally applying to data
> structured within the database). There are many people on this list who
> will tell you not to de-normalize anything, ever (and most of the time,
> you shouldn't!).

Thank you for all you help and time for this.

Best regards.

On Thu, 27 Jan 2005 13:02:48 -0500, Merlin Moncure
<merlin.moncure@rcsonline.com> wrote:
> Alexandre wrote:
> > On Thu, 27 Jan 2005 10:44:45 -0500, Merlin Moncure
> > <merlin.moncure@rcsonline.com> wrote:
> > > Alexandre wrote:
> > > Let's start with the normalized result set.
> > >
> > > product_id |  department_id    |  sum
> > > 924           a                   6000
> > > 924           c                   1575
> > > 907           a                   1500
> > > [...]
> >
> Right. I expanding departments into columns is basically a dead end.
> First of all, SQL is not really designed to do this, and second of all
> (comments continued below)

Ok, I got it. The basic message is to avoid making columns out of rows
like I'm doing right now, that "de-normalizing" in an array is the way
to go. So I should query and get the results in an array then after my
application will parse the array into the good columns. (I'm
developping a software.)

If I still got it wrong, this is because the 'geek' section of my
brain is in vacation: leave a message and when it'll come back, it'll
explain all this to me! :)

So I found the array_accum function in the doc, so I did create it.

CREATE AGGREGATE array_accum (
    sfunc = array_append,
    basetype = anyelement,
    stype = anyarray,
    initcond = '{}'
);

Then I created this new select:
SELECT
  product_id, 
  array_accum(department_id) as a_department_id,
  array_accum(req_time) as a_req_time
FROM (SELECT * FROM design.product_department_time) AS tmp
GROUP BY product_id;

It gives:
 product_id | a_department_id |    a_req_time
------------+-----------------+------------------
        924 | {A,C}           | {6000,1575}
        907 | {A,C,D}         | {1500,1500,4575}
        906 | {A,C,D}         | {3000,3000,1935}

So, the performance should be much better using this agregate approach?

No I thing I'll merge the results in my software, unless you think
that at this point doing a LEFT JOIN with my jobs table is the way to
go, beacuse the performance will be good. (Personally I don't know the
answer of this one.)

> If parsing an array string is a pain I happen to have a C++ class handy
> that can compose/decompose a postgresql array string if:
> a: no more than 1 dimension and
> b: array bounds are known
>
> Let me know if you need it and I'll send it over.

Thank you for your offer. I think parsing an array is the easiest
thing to do for me in all this. :) If I encounter any problem, I'll
drop you a mail.

Regards.

Thank you for this help Dawid, I'll have to take some time to look at
this suggestion. If I must create a domain with all the departments
I'll have a problem because the user is creating and deleting
departments as it pleases him.

Any counter-ideas?

Regards.

On Thu, 27 Jan 2005 10:44:45 -0500, Merlin Moncure
<merlin.moncure@rcsonline.com> wrote:
> Alexandre wrote:
> > Here a normal listing of design.product_department_time:
> >  product_id | department_id | req_time
> > ------------+---------------+----------
> >         906 | A             |     3000
> >         906 | C             |     3000
> >         906 | D             |     1935
> >         907 | A             |     1500
> >         907 | C             |     1500
> >         907 | D             |     4575
> >         924 | A             |     6000
> >         924 | C             |     1575
> >  product_id |  a   |  c   |  d
> > ------------+------+------+------
> >         924 | 6000 | 1575 |
> >         907 | 1500 | 1500 | 4575
> >         906 | 3000 | 3000 | 1935
>
> ok, you have a couple of different options here. The first thing that
> jumps out at me is to use arrays to cheat using arrays.
>   Let's start with the normalized result set.
>
> select product_id, department_id, sum(req_time) group by product_id,
> department_id
>
> product_id |  department_id    |  sum
> 924           a                   6000
> 924           c                   1575
> 907           a                   1500
> [...]

Hello Merlin,

First of all, thanks for your time. Yes this is exactly what I'm doing
right now (if I understand well your point here). All records in
design.product_department_time are unique for each (product_id,
req_time) combo and 0-null values are not possible. This is the first
listing you have.

In my query I added the sum() and GROUP BY stuff to avoid having such a listing:

 product_id |  a   |  c   |  d
------------+------+------+------
        906 | 3000 |      |
        906 |      | 3000 |
        906 |      |      | 1935
        907 | 1500 |      |
        907 |      | 1500 |
        907 |      |      | 4575
        924 | 6000 |      |
        924 |      | 1575 |

So that for a given product_id I have all the times by departments in
a single row (second listing I posted).

> If you absolutely must have 1 record/product, you can cheat using
> arrays:
>
> select q.product_id,
>        array_accum(q.department_id) as depts,
>        array_accum(q.req_time)      as times
>          from
>        (
> select product_id, department_id, sum(req_time) as req_time
> group by product_id, department_id
>        ) q
>         group by q.product_id;
>
> select product_id, array_accum(department_id) sum(req_time) group by
> product_id
>
> product_id |  department_id    |  sum
> 924           {a, c}              {1500, 1575}
>  [...]

I did not used arrays because I didn't think about it, but I don't
know if this is still the most efficient way. My software will have to
work out the data, unless the array expands in good columns. But I'm
not an expert at all. I try to do good DB design, but sometimes this
is more complicated to work with the data.

Here is the table definition if it can help:

design.products (product_id     serial     PRIMARY KEY, ...);
prod.departments (department_id varchar(3) PRIMARY KEY, ...);

design.product_department_time (
product_id          integer     REFERENCES design.products ON DELETE
CASCADE ON UPDATE CASCADE,
department_id       varchar(3)  REFERENCES prod.departments ON DELETE
CASCADE ON UPDATE CASCADE,
req_time            integer     NOT NULL DEFAULT 0 CHECK (req_time >= 0),
CONSTRAINT product_department_time_pkey PRIMARY KEY (product_id, department_id)
);

And i also have a jobs table which has one product_id attached to one
job with the required quantity to produce. So I must shouw the user
how much time this will require by departments for each jobs. :) This
is a nice report, but I don't want to kill the database each time the
user want to see it.

Thanks for your contrib so far, this will help me looking for other
ways doing it. I'm always ready to hear more!

Regards.

Good morning,

I have a table that links two tables and I need to flatten one.
(Please, if I'm just not in the good forum for this, tell me. This is
a performance issue for me, but you might consider this as an SQL
question. Feel free to direct me at the good mailling-list.)

design.products ---> design.product_department_time <--- design.departments

This allows us to fixe a given required time by department for a given product.
- Departments are defined by the user
- Products also
- Time is input for a department (0 and NULL are impossible).

Here a normal listing of design.product_department_time:
product_id | department_id | req_time
------------+---------------+----------
        906 | A             |     3000
        906 | C             |     3000
        906 | D             |     1935
        907 | A             |     1500
        907 | C             |     1500
        907 | D             |     4575
        924 | A             |     6000
        924 | C             |     1575

I need to JOIN this data with the product listing we have to produce
and multiply the quantity with this time by departments, and all that
in a row. So departments entries become columns.

I did the following (I formated the query to help out):

SELECT
  product_id,
  sum(CASE WHEN department_id = 'A' THEN req_time END) AS a,
  sum(CASE WHEN department_id = 'C' THEN req_time END) AS c,
  sum(CASE WHEN department_id = 'D' THEN req_time END) AS d
FROM design.product_department_time
GROUP BY product_id;

 product_id |  a   |  c   |  d
------------+------+------+------
        924 | 6000 | 1575 |
        907 | 1500 | 1500 | 4575
        906 | 3000 | 3000 | 1935

Now in my software I know all the departments, so programatically I
build a query with a CASE for each department (just like the above).
This is nice, this is working, there is less than 10 departements for
now and about 250 jobs actives in the system. So PostgeSQL will not
die. (My example is more simple because this was an hard-coded test
case, but I would create a case entry for each department.)

But I am wondering what is the most efficient way to do what I need?

After that I need to link (LEFT JOIN) this data with the jobs in the
system. Each job has a product_id related to it, so USING (product_id)
and I multiply the time of each department with the quantity there is
to product. So someone can know how much work time there is to do by
departments.


Thanks for any input, comments, tips, help, positive criticism to
learn more, etc.

Thank you for this additionnal information. This will help out in the
futur. In my situation this is a good thing to have integer key where
I decided to have them. Event if I was obliged to add UNIQUE
constraints to some other columns. I think they call this "candidate
key" and it's still 3NF (whatever; but only if my db is correctly
organised)... I try to be logical and efficient for good performance.
But in the end, the time (the db will get bigger) and good EXPLAIN
ANALYSE commands will help fine tuning later! This will give me more
experience at that point.

> Actually I see one interesting exception to my policy in my current database
> schema. And I don't think I would do this one differently given the choice
> either. The primary key of the postal code table is the postal code. (postal
> codes are up here in the great white north like zip codes down there.)

(I do understand this one, living in the province of Quebec. ;) And
the great white north is still not arrived; end november! - Still, not
very exceptionnal.)

Regards.

Thanks for those tips. I'll print and keep them. So in my case, the
product_code being varchar(24) is:
4 bytes + string size (so possibly up to 24) = possible 28 bytes. I
did the good thing using a serial. For my shorter keys (4 bytes + up
to 6 char) I will use the natural key.

This is interesting, because this is what I did right now.

The "transparent surrogate keying" proposal that is discussed bellow
in the thread is a very good idea. It would be nice to see that. It
would be easier for the DB admin and the coder; the moment this is not
slowing the system. : )

Best regards.

Mr Caillaud,

Merci! Many points you bring were also my toughts. I was asking myself
really this was the way to go. I'm happy to see that my view of the
problem was good.

Encore merci! (Thanks again!)

Good day,

I'm asking myself if there is a performance issue in using an integer
of varchar(24) PRIMARY KEY in a product table.

I've read that there is no speed issue in the query, but that the only
performance issue is the database size of copying the key in other
tables that require it.

My product_id is copied in orders, jobs, and other specific tables.

What is the common approach? Should I use directly the product_code as
my ID, or use a sequantial number for speed? (I did the same for the
company_id, this is a 'serial' and not the shor name of the customer.
I just don't know what is usually done.

Right now I did the following:
CREATE TABLE design.products (
product_id          serial      PRIMARY KEY,
company_id          integer     NOT NULL REFERENCES sales.companies ON
UPDATE CASCADE,
product_code        varchar(24) NOT NULL,
...
CONSTRAINT product_code_already_used_for_this_company UNIQUE
(company_id, product_code)
);

CREATE TABLE sales.companies (
company_id        integer      PRIMARY KEY,
company_name      varchar(48)  NOT NULL UNIQUE,
...
);

The company_id is also copied in many tables like product, contacts, etc.

Thank you very much for any good pointers on this 'already seen' issue.