How to save images in a table.

Hello,

I have 2 tables :

CREATE TABLE e_kalkn (
id INT4 NOT NULL,
roba VARCHAR(6) NOT NULL,
dat DATE NOT NULL,
datv DATE NOT NULL,
val VARCHAR(3) NOT NULL,
kod VARCHAR(3) NOT NULL,
tb VARCHAR(2) NOT NULL,
ts VARCHAR(2) NOT NULL,
dob VARCHAR(6),
status VARCHAR(1)
);
CREATE TABLE e_kalkns (
id INT4 NOT NULL,
redbr INT NOT NULL,
osn NUMERIC(30,6),
proc NUMERIC(30,6),
izn NUMERIC(30,6)
);

and indexes :

create unique index e_kalkn_id on e_kalkn(id);
create index e_kalkns_id on e_kalkns(id);

Table e_kalkn have 4668 rows and e_kalkns 101170 rows.

Query :

select roba,sum(izn)
from e_kalkn k,e_kalkns ks
where k.id=ks.id
group by roba
order by roba

is 2.5 times faster on one commercial database (there are tests on Internet
that say 'Postgresql is faster than that database).
I can't say which database it is.

Regards

If your query is not using the correct index files, you're query will run
slowly. Please post the output from EXPLAIN.

-r

At 09:00 PM 5/15/01 +0200, snpe wrote:

On Tue, 15 May 2001, snpe wrote:

Table e_kalkn have 4668 rows and e_kalkns 101170 rows.

Query :

select roba,sum(izn)
from e_kalkn k,e_kalkns ks
where k.id=ks.id
group by roba
order by roba

is 2.5 times faster on one commercial database (there are tests on Internet
that say 'Postgresql is faster than that database).
I can't say which database it is.

Have you run vacuum analyze (since loading the data) and what does explain
show for the query. Also, what version are you using?

snpe wrote:
[Snip]

Query :

select roba,sum(izn)
from e_kalkn k,e_kalkns ks
where k.id=ks.id
group by roba
order by roba

is 2.5 times faster on one commercial database (there are tests on Internet
that say 'Postgresql is faster than that database).
I can't say which database it is.

The 'order by' statement is redundant since you use already 'group by'.
Furthermore you can speed up the query by creating an index on your
'group by' fields (in this case 'roba').

If one database is faster than the other depends on what you test. I've
read that PostgreSQL can be slow with queries that contain a group by,
although I did not experience such thing myself.

HTH,

Nils

On Tuesday 15 May 2001 17:28, Stephan Szabo wrote:

On Tue, 15 May 2001, snpe wrote:

Table e_kalkn have 4668 rows and e_kalkns 101170 rows.

Query :

select roba,sum(izn)
from e_kalkn k,e_kalkns ks
where k.id=ks.id
group by roba
order by roba

is 2.5 times faster on one commercial database (there are tests on
Internet that say 'Postgresql is faster than that database).
I can't say which database it is.

Have you run vacuum analyze (since loading the data) and what does explain
show for the query. Also, what version are you using?

I have run :

vacuumdb --analyze -v -d mytest

I try index on column roba in table e_kalkn, but all is same.

This is explain :
psql:up1:4: NOTICE: QUERY PLAN:

Aggregate (cost=11132.18..11286.42 rows=3085 width=32)
-> Group (cost=11132.18..11209.30 rows=30849 width=32)
-> Sort (cost=11132.18..11132.18 rows=30849 width=32)
-> Hash Join (cost=121.35..8831.95 rows=30849 width=32)
-> Seq Scan on e_kalkns ks (cost=0.00..2041.10
rows=101710 width=16)
-> Hash (cost=109.68..109.68 rows=4668 width=16)
-> Seq Scan on e_kalkn k (cost=0.00..109.68
rows=4668 width=16)

EXPLAIN

On Tuesday 15 May 2001 17:28, Stephan Szabo wrote:

On Tue, 15 May 2001, snpe wrote:

Table e_kalkn have 4668 rows and e_kalkns 101170 rows.

Query :

select roba,sum(izn)
from e_kalkn k,e_kalkns ks
where k.id=ks.id
group by roba
order by roba

is 2.5 times faster on one commercial database (there are tests on
Internet that say 'Postgresql is faster than that database).
I can't say which database it is.

Have you run vacuum analyze (since loading the data) and what does explain
show for the query. Also, what version are you using?

version postgresql 7.1.1

snpe <snpe@infosky.net> writes:

select roba,sum(izn)
from e_kalkn k,e_kalkns ks
where k.id=ks.id
group by roba
order by roba

is 2.5 times faster on one commercial database

That other DBMS is probably implementing the GROUP BY + aggregate
using a hash table to compute all the aggregates in parallel,
rather than sorting first as Postgres does. You still need to sort
in the end to meet the ORDER BY spec, but you are only sorting the
aggregate results not the inputs. Disadvantage: if there are a lot
of distinct values of ROBA then your hash table may overrun memory.

We have a TODO item to implement hashed aggregation. Teaching the
planner to make an intelligent choice between sorted and hashed
aggregation will probably be harder than the actual execution code.

regards, tom lane

I found this same problem. It's the "group by" that is the killer.
PostgreSQL's implementation of "group by" requires a sort of the entire
table on the group by criteria, even if there is an index on the group
by column. It's to bad this caused us not to be able to use Postgresql
for a project.

-----Original Message-----
From: snpe [mailto:snpe@infosky.net]
Sent: Tuesday, May 15, 2001 11:59 AM
To: Stephan Szabo
Cc: PostgreSQL-General
Subject: Re: [GENERAL] Performance aggregates

On Tuesday 15 May 2001 17:28, Stephan Szabo wrote:

On Tue, 15 May 2001, snpe wrote:

Table e_kalkn have 4668 rows and e_kalkns 101170 rows.

Query :

select roba,sum(izn)
from e_kalkn k,e_kalkns ks
where k.id=ks.id
group by roba
order by roba

is 2.5 times faster on one commercial database (there are tests on
Internet that say 'Postgresql is faster than that database).
I can't say which database it is.

Have you run vacuum analyze (since loading the data) and

what does explain

show for the query. Also, what version are you using?

I have run :

vacuumdb --analyze -v -d mytest

I try index on column roba in table e_kalkn, but all is same.

This is explain :
psql:up1:4: NOTICE: QUERY PLAN:

Aggregate (cost=11132.18..11286.42 rows=3085 width=32)
-> Group (cost=11132.18..11209.30 rows=30849 width=32)
-> Sort (cost=11132.18..11132.18 rows=30849 width=32)
-> Hash Join (cost=121.35..8831.95 rows=30849
width=32)
-> Seq Scan on e_kalkns ks (cost=0.00..2041.10
rows=101710 width=16)
-> Hash (cost=109.68..109.68 rows=4668 width=16)
-> Seq Scan on e_kalkn k
(cost=0.00..109.68
rows=4668 width=16)

EXPLAIN

---------------------------(end of
broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

This email message is for the sole use of the intended recipient(s) and may
contain proprietary and confidential information. Any unauthorized review,
use, disclosure or distribution is prohibited. If you are not the intended
recipient, please contact the sender by reply email and destroy all copies
of the original message. Thank you

On Tuesday 15 May 2001 14:40, you wrote:

Hello,

I have 2 tables :

CREATE TABLE e_kalkn (
id INT4 NOT NULL,
roba VARCHAR(6) NOT NULL,
dat DATE NOT NULL,
datv DATE NOT NULL,
val VARCHAR(3) NOT NULL,
kod VARCHAR(3) NOT NULL,
tb VARCHAR(2) NOT NULL,
ts VARCHAR(2) NOT NULL,
dob VARCHAR(6),
status VARCHAR(1)
);
CREATE TABLE e_kalkns (
id INT4 NOT NULL,
redbr INT NOT NULL,
osn NUMERIC(30,6),
proc NUMERIC(30,6),
izn NUMERIC(30,6)
);

and indexes :

create unique index e_kalkn_id on e_kalkn(id);
create index e_kalkns_id on e_kalkns(id);

Table e_kalkn have 4668 rows and e_kalkns 101170 rows.

Query :

select roba,sum(izn)
from e_kalkn k,e_kalkns ks
where k.id=ks.id
group by roba
order by roba

is 2.5 times faster on one commercial database (there are tests on Internet
that say 'Postgresql is faster than that database).
I can't say which database it is.

MySQL is 2.5 times faster, too.

regards,

is 2.5 times faster on one commercial database (there are tests on Internet
that say 'Postgresql is faster than that database).
I can't say which database it is.

The 'order by' statement is redundant since you use already 'group by'.
Furthermore you can speed up the query by creating an index on your
'group by' fields (in this case 'roba').

If one database is faster than the other depends on what you test. I've
read that PostgreSQL can be slow with queries that contain a group by,
although I did not experience such thing myself.

Is there a TODO item I can add here folks?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Is there a TODO item I can add here folks?

Hash-based aggregation. Isn't it on the list already?

regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Is there a TODO item I can add here folks?

Hash-based aggregation. Isn't it on the list already?

Added now:

* Add hash-based aggregates

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026