Thread

1. Re: [HACKERS] postgres performance

   Shiby Thomas <sthomas@cise.ufl.edu> — 1998-01-15T18:33:08Z

   => 	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
   
   
   
   
   

2. Re: [HACKERS] postgres performance

   Bruce Momjian <maillist@candle.pha.pa.us> — 1998-01-16T00:26:45Z

   > 
   > 
   > => 	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
   
   

3. Re: [HACKERS] postgres performance

   Vadim B. Mikheev <vadim@sable.krasnoyarsk.su> — 1998-01-16T03:30:22Z

   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