Thread: 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. 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
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?
> > 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
=> 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
>
>
> => 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
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