Thread: How to save images in a table.

How to save images in a table.

From
"Stuart Foster"
Date:
I'm looking for a way to save jpg's in a postgresql table.
Does anyone know how I can do this?

TIA
Stuart

Performance aggregates

From
snpe
Date:
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

Re: Performance aggregates

From
Stephan Szabo
Date:
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?


Re: Performance aggregates

From
snpe
Date:
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

Re: Performance aggregates

From
snpe
Date:
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



Re: Performance aggregates

From
Tom Lane
Date:
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

Re: Performance aggregates

From
Ryan Mahoney
Date:
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 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
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>
>
>---
>Incoming mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01

Re: Performance aggregates

From
snpe
Date:
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,

Re: Performance aggregates

From
Nils Zonneveld
Date:

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

Re: Re: Performance aggregates

From
Bruce Momjian
Date:
> > 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

Re: Re: Performance aggregates

From
Tom Lane
Date:
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