Thread: How to save images in a table.
I'm looking for a way to save jpg's in a postgresql table. Does anyone know how I can do this? TIA Stuart
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
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?
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
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
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
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
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,
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
> > 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