Hi,

I have installed PostgreSQL6.2.1 from my own user account (I was just trying
to test out something first). It seems to be running too slow. For example,
the self join of a table with roughly 300K records takes 2-3 hours. There is
an index on the join attribute.
I am running it on a 8 processor (each is a 248 MHz SUNW,UltraSPARC-II)
 machine with a total of 2.0 GB main memory.
But, since it is not parallelized, it may get the power of only one processor.

Does it make a difference in performance since I have installed it from my
user account, and not the root. Also, I compiled it using gcc on a 
solaris 2.5 machine and the machine I ran it is a solaris 2.6 machine. I did
that since the solaris 2.6 machine is the fastest we have here and the 
executable
compiled on 2.5 was running properly also. Can that have an impact on the
performance?

Thanks
--shiby


Thu, 15 Jan 1998 13:01:37 -0500
Shiby Thomas <sthomas@cise.ufl.edu>
postgres performance
On Thu, 15 Jan 1998, Shiby Thomas wrote:

> Hi,
> 
> I have installed PostgreSQL6.2.1 from my own user account (I was just trying
> to test out something first). It seems to be running too slow. For example,
> the self join of a table with roughly 300K records takes 2-3 hours. There is
> an index on the join attribute.
> I am running it on a 8 processor (each is a 248 MHz SUNW,UltraSPARC-II)
>  machine with a total of 2.0 GB main memory.
> But, since it is not parallelized, it may get the power of only one processor.
> 
> Does it make a difference in performance since I have installed it from my
> user account, and not the root. Also, I compiled it using gcc on a 
> solaris 2.5 machine and the machine I ran it is a solaris 2.6 machine. I did
> that since the solaris 2.6 machine is the fastest we have here and the 
> executable
> compiled on 2.5 was running properly also. Can that have an impact on the
> performance?

	There may be optimizations in the 2.6 libraries that would improve
performance, but I wouldn't suspect that it would make *that* big of a
difference.  What is your SQL/join statemnt?  How are you running
postmaster?  What does 'explain' show?



Thu, 15 Jan 1998 13:15:59 -0500 (EST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] postgres performance
> 
> Hi,
> 
> I have installed PostgreSQL6.2.1 from my own user account (I was just trying
> to test out something first). It seems to be running too slow. For example,
> the self join of a table with roughly 300K records takes 2-3 hours. There is
> an index on the join attribute.
> I am running it on a 8 processor (each is a 248 MHz SUNW,UltraSPARC-II)
>  machine with a total of 2.0 GB main memory.
> But, since it is not parallelized, it may get the power of only one processor.

You said a self-join.  I think we have a performance problem there. 
Vadim?


> 
> Does it make a difference in performance since I have installed it from my
> user account, and not the root. Also, I compiled it using gcc on a 
> solaris 2.5 machine and the machine I ran it is a solaris 2.6 machine. I did
> that since the solaris 2.6 machine is the fastest we have here and the 
> executable
> compiled on 2.5 was running properly also. Can that have an impact on the
> performance?
> 
> Thanks
> --shiby
> 
> 
> 


-- 
Bruce Momjian
maillist@candle.pha.pa.us

Thu, 15 Jan 1998 13:23:07 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] postgres performance

=> 	There may be optimizations in the 2.6 libraries that would improve
=> performance, but I wouldn't suspect that it would make *that* big of a
=> difference.  What is your SQL/join statemnt?  How are you running
=> postmaster?  What does 'explain' show?
=> 
The complete query is this:

select item1, item2, count(t1.tid) into table f2_temp from data t1, data t2, 
c2
where t1.item = c2.item1 and t2.item = c2.item2 and t1.tid = t2.tid group by 
ite
m1, item2

data is a table with 2 integer columns (tid, item) and it has ~300K records
c2 is a table (item1, item2), both integers and has ~1.5K records.

I was directly running postgres with the -B and -S flags to give more buffers
and sortMem. I also tried several join plans by the -f flags. Hash join works
the best and that itself is too slow (perhaps due to the self join)

--shiby




Thu, 15 Jan 1998 13:33:08 -0500
Shiby Thomas <sthomas@cise.ufl.edu>
Re: [HACKERS] postgres performance 
> 
> 
> => 	There may be optimizations in the 2.6 libraries that would improve
> => performance, but I wouldn't suspect that it would make *that* big of a
> => difference.  What is your SQL/join statemnt?  How are you running
> => postmaster?  What does 'explain' show?
> => 
> The complete query is this:
> 
> select item1, item2, count(t1.tid) into table f2_temp from data t1, data t2, 
> c2
> where t1.item = c2.item1 and t2.item = c2.item2 and t1.tid = t2.tid group by 
> ite
> m1, item2
> 
> data is a table with 2 integer columns (tid, item) and it has ~300K records
> c2 is a table (item1, item2), both integers and has ~1.5K records.
> 
> I was directly running postgres with the -B and -S flags to give more buffers
> and sortMem. I also tried several join plans by the -f flags. Hash join works
> the best and that itself is too slow (perhaps due to the self join)
> 

I have a possible workaround.  Turn GEQO on:

	SET GEQO ON=1

and try it.  Let us know.

-- 
Bruce Momjian
maillist@candle.pha.pa.us

Thu, 15 Jan 1998 19:26:45 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] postgres performance
Shiby Thomas wrote:
> 
> =>      There may be optimizations in the 2.6 libraries that would improve
> => performance, but I wouldn't suspect that it would make *that* big of a
> => difference.  What is your SQL/join statemnt?  How are you running
> => postmaster?  What does 'explain' show?
> =>
> The complete query is this:
> 
> select item1, item2, count(t1.tid) into table f2_temp from data t1, data t2,
> c2
> where t1.item = c2.item1 and t2.item = c2.item2 and t1.tid = t2.tid group by
> ite
> m1, item2
> 
> data is a table with 2 integer columns (tid, item) and it has ~300K records
> c2 is a table (item1, item2), both integers and has ~1.5K records.
> 
> I was directly running postgres with the -B and -S flags to give more buffers
> and sortMem. I also tried several join plans by the -f flags. Hash join works
> the best and that itself is too slow (perhaps due to the self join)

Indices ?
EXPLAIN ?

Vadim

Fri, 16 Jan 1998 10:30:22 +0700
"Vadim B. Mikheev" <vadim@sable.krasnoyarsk.su>
Re: [HACKERS] postgres performance