Re: [PERFORM] simple join uses indexes, very slow

2006-03-31 Thread Jim C. Nasby
On Wed, Mar 29, 2006 at 01:08:15AM -0500, stef wrote: > > If your looking for suggestions, I would suggest updating the 8.1.x you > have installed to the latest version, as of typing this is 8.1.3 ;) Most > notable is some of the -bug- fixes that are in since 8.1.0, for example; > > * Fix inco

Re: [PERFORM] simple join uses indexes, very slow

2006-03-29 Thread george young
On Wed, 29 Mar 2006 01:08:15 -0500 stef <[EMAIL PROTECTED]> threw this fish to the penguins: > > If your looking for suggestions, I would suggest updating the 8.1.x you > have installed to the latest version, as of typing this is 8.1.3 ;) Most > notable is some of the -bug- fixes that are in s

Re: [PERFORM] simple join uses indexes, very slow

2006-03-28 Thread stef
If your looking for suggestions, I would suggest updating the 8.1.x you have installed to the latest version, as of typing this is 8.1.3 ;) Most notable is some of the -bug- fixes that are in since 8.1.0, for example; * Fix incorrect optimizations of outer-join conditions (Tom) You know, mi

Re: [PERFORM] simple join uses indexes, very slow

2006-03-28 Thread Chris
george young wrote: [PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1] I have a simple join on two tables that takes way too long. Can you help me understand what's wrong? There are indexes defined on the relevant columns. I just did a fresh vacuum --full --analyze on the

Re: [PERFORM] simple join uses indexes, very slow

2006-03-28 Thread Dave Dutcher
> -Original Message- > From: [EMAIL PROTECTED] [mailto:pgsql-performance- > [EMAIL PROTECTED] On Behalf Of Steinar H. Gunderson > A merge join requires sorted inputs. > > > Most of the time was spent sorting the parameters parameters table by > > opset_num even though opset_num is indexed

Re: [PERFORM] simple join uses indexes, very slow

2006-03-28 Thread george young
On Tue, 28 Mar 2006 19:17:49 +0100 Simon Riggs <[EMAIL PROTECTED]> threw this fish to the penguins: > On Tue, 2006-03-28 at 10:22 -0500, george young wrote: > > > work_mem= 1024 > > Set that higher. > > Try a couple of other plans using enable_* and let us have the EXPLAIN > ANALYZE plans. I tr

Re: [PERFORM] simple join uses indexes, very slow

2006-03-28 Thread Simon Riggs
On Tue, 2006-03-28 at 10:22 -0500, george young wrote: > work_mem= 1024 Set that higher. Try a couple of other plans using enable_* and let us have the EXPLAIN ANALYZE plans. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 5: don't forget

Re: [PERFORM] simple join uses indexes, very slow

2006-03-28 Thread Steinar H. Gunderson
On Tue, Mar 28, 2006 at 11:20:19AM -0600, Dave Dutcher wrote: > I guess what I am really curious about is why was the OP getting an > expensive sort when the planner tried a merge join? A merge join requires sorted inputs. > Most of the time was spent sorting the parameters parameters table by >

Re: [PERFORM] simple join uses indexes, very slow

2006-03-28 Thread Jim C. Nasby
On Tue, Mar 28, 2006 at 06:29:08PM +0200, Steinar H. Gunderson wrote: > On Tue, Mar 28, 2006 at 10:18:25AM -0600, Dave Dutcher wrote: > >> "parameters_idx" btree (run, opset_num, step_num, opset, > > opset_ver, > >> step, step_ver, name, split, wafers) > >> "parameters_opset_idx" btree (ops

Re: [PERFORM] simple join uses indexes, very slow

2006-03-28 Thread Dave Dutcher
> -Original Message- > From: [EMAIL PROTECTED] [mailto:pgsql-performance- > [EMAIL PROTECTED] On Behalf Of Steinar H. Gunderson > Sent: Tuesday, March 28, 2006 10:29 AM > > An index on (A,B,C) can be used for a query on (A,B) or (A), so it doesn't > really matter. It isn't usable for a que

Re: [PERFORM] simple join uses indexes, very slow

2006-03-28 Thread Steinar H. Gunderson
On Tue, Mar 28, 2006 at 10:18:25AM -0600, Dave Dutcher wrote: >> "parameters_idx" btree (run, opset_num, step_num, opset, > opset_ver, >> step, step_ver, name, split, wafers) >> "parameters_opset_idx" btree (opset, step, name) >> "parameters_step_idx" btree (step, name) > Have you tried

Re: [PERFORM] simple join uses indexes, very slow

2006-03-28 Thread Dave Dutcher
> -Original Message- > From: [EMAIL PROTECTED] [mailto:pgsql-performance- > [EMAIL PROTECTED] On Behalf Of george young > Sent: Monday, March 27, 2006 12:48 PM > To: pgsql-performance@postgresql.org > Subject: [PERFORM] simple join uses indexes, very slow >

Re: [PERFORM] simple join uses indexes, very slow

2006-03-28 Thread Markus Schaber
Hi, George, george young wrote: >>Looks like a hash join might be faster. What is your work_mem set to? > > work_mem= 1024 This is 1 Megabyte. By all means, increase it, if possible. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Developme

Re: [PERFORM] simple join uses indexes, very slow

2006-03-28 Thread george young
On Tue, 28 Mar 2006 09:30:54 +0100 Simon Riggs <[EMAIL PROTECTED]> threw this fish to the penguins: > On Mon, 2006-03-27 at 13:47 -0500, george young wrote: > > > Table sizes: parameters has 2.1512e+07 tuples, run_opsets has 211745 tuples. > > > > explain analyze SELECT ro.run, ro.opset_num, p.s

Re: [PERFORM] simple join uses indexes, very slow

2006-03-28 Thread Simon Riggs
On Mon, 2006-03-27 at 13:47 -0500, george young wrote: > Table sizes: parameters has 2.1512e+07 tuples, run_opsets has 211745 tuples. > > explain analyze SELECT ro.run, ro.opset_num, p.step_num FROM run_opsets ro, > parameters p WHERE ro.run = p.run AND ro.opset_num = p.opset_num and > ro.run='

[PERFORM] simple join uses indexes, very slow

2006-03-27 Thread george young
[PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1] I have a simple join on two tables that takes way too long. Can you help me understand what's wrong? There are indexes defined on the relevant columns. I just did a fresh vacuum --full --analyze on the two tables. Is there s