Re: [PERFORM] simple join uses indexes, very slow
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 incorrect optimizations of outer-join conditions (Tom) You know, minor point releases aren't adding new features or changing basic functionality, they are pure and simple bugfixes. If I was in -your- position, I would run (don't walk ;) and install upto 8.1.3 More important, there are data loss bugfixes between 8.1.0 and 8.1.3. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] simple join uses indexes, very slow
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 since 8.1.0, for example;
* Fix incorrect optimizations of outer-join conditions (Tom)
You know, minor point releases aren't adding new features or changing
basic functionality, they are pure and simple bugfixes. If I was in
-your- position, I would run (don't walk ;) and install upto 8.1.3
I just did this(8.1.3). I also moved the server to a host with more
ram and faster cpu. And I did cluster on the main index of the large
parameters table. The result is less than a second instead of 70
seconds.
Sorry I didn't have time to isolate the individual effects
of the above changes, but sometimes you just have to do a bunch of
good things and move on. For your enjoyment here's the latest analyze:
newschm3= 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='team9';
QUERY PLAN
---
Nested Loop (cost=0.00..6194.18 rows=9186 width=22) (actual
time=0.477..175.554 rows=43050 loops=1)
- Index Scan using run_opsets_pkey on run_opsets ro (cost=0.00..122.27
rows=68 width=18) (actual time=0.222..1.093 rows=263 loops=1)
Index Cond: (run = 'team9'::text)
- Index Scan using parameters_idx on parameters p (cost=0.00..88.72
rows=46 width=22) (actual time=0.023..0.498 rows=164 loops=263)
Index Cond: (('team9'::text = p.run) AND (outer.opset_num =
p.opset_num))
Total runtime: 190.821 ms
Thank you all very much for you help!
-- George Young
of course, thats jst my 2c, feel free to ignore :D
Regards
Stef
Chris wrote:
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 two tables.
Is there something I'm not seeing?
[CPU is 950Mhz AMD, 256MB RAM, 15k rpm scsi disk]
-- George Young
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='team9';
QUERY PLAN
Nested Loop (cost=2.16..7957.40 rows=6707 width=22) (actual
time=14.986..70197.129 rows=43050 loops=1)
- Index Scan using run_opsets_pkey on run_opsets ro
(cost=0.00..128.75 rows=71 width=18) (actual time=0.386..62.959
rows=263 loops=1)
Index Cond: (run = 'team9'::text)
- Bitmap Heap Scan on parameters p (cost=2.16..109.93 rows=27
width=22) (actual time=1.591..266.211 rows=164 loops=263)
Recheck Cond: (('team9'::text = p.run) AND
(outer.opset_num = p.opset_num))
- Bitmap Index Scan on parameters_idx (cost=0.00..2.16
rows=27 width=0) (actual time=1.153..1.153 rows=164 loops=263)
Index Cond: (('team9'::text = p.run) AND
(outer.opset_num = p.opset_num))
Total runtime: 70237.727 ms
(8 rows)
Table public.run_opsets
Column|Type |Modifiers
--+-+-
run | text| not null
opset| text|
opset_ver| integer |
opset_num| integer | not null
status | opset_status|
date_started | timestamp without time zone |
date_done| timestamp without time zone |
work_started | timestamp without time zone |
lock_user| text| default 'NO-USER'::text
lock_pid | integer |
needs_review | text|
Indexes:
run_opsets_pkey PRIMARY KEY, btree (run, opset_num) CLUSTER
-- Table public.parameters
Column | Type | Modifiers
---+-+---
run | text| not null
opset_num | integer | not null
opset | text| not null
opset_ver | integer | not null
step_num | integer | not null
step | text| not null
step_ver | integer |
Re: [PERFORM] simple join uses indexes, very slow
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='team9';
QUERY PLAN
Nested Loop (cost=2.16..7957.40 rows=6707 width=22) (actual
time=14.986..70197.129 rows=43050 loops=1)
- Index Scan using run_opsets_pkey on run_opsets ro (cost=0.00..128.75
rows=71 width=18) (actual time=0.386..62.959 rows=263 loops=1)
Index Cond: (run = 'team9'::text)
- Bitmap Heap Scan on parameters p (cost=2.16..109.93 rows=27 width=22)
(actual time=1.591..266.211 rows=164 loops=263)
Recheck Cond: (('team9'::text = p.run) AND (outer.opset_num =
p.opset_num))
- Bitmap Index Scan on parameters_idx (cost=0.00..2.16 rows=27
width=0) (actual time=1.153..1.153 rows=164 loops=263)
Index Cond: (('team9'::text = p.run) AND (outer.opset_num =
p.opset_num))
Total runtime: 70237.727 ms
(8 rows)
The planner appears to be underestimating the number of rows retrieved
in both cases, then multiplying them together to make it worse.
Multi-column indexes provide less accurate estimates (right now).
Looks like a hash join might be faster. What is your work_mem set to?
Can you SET enable_nestloop=off and rerun the EXPLAIN ANALYZE?
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [PERFORM] simple join uses indexes, very slow
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.step_num FROM run_opsets ro,
parameters p WHERE ro.run = p.run AND ro.opset_num = p.opset_num and
ro.run='team9';
QUERY PLAN
Nested Loop (cost=2.16..7957.40 rows=6707 width=22) (actual
time=14.986..70197.129 rows=43050 loops=1)
- Index Scan using run_opsets_pkey on run_opsets ro
(cost=0.00..128.75 rows=71 width=18) (actual time=0.386..62.959 rows=263
loops=1)
Index Cond: (run = 'team9'::text)
- Bitmap Heap Scan on parameters p (cost=2.16..109.93 rows=27
width=22) (actual time=1.591..266.211 rows=164 loops=263)
Recheck Cond: (('team9'::text = p.run) AND (outer.opset_num =
p.opset_num))
- Bitmap Index Scan on parameters_idx (cost=0.00..2.16 rows=27
width=0) (actual time=1.153..1.153 rows=164 loops=263)
Index Cond: (('team9'::text = p.run) AND (outer.opset_num
= p.opset_num))
Total runtime: 70237.727 ms
(8 rows)
The planner appears to be underestimating the number of rows retrieved
in both cases, then multiplying them together to make it worse.
Multi-column indexes provide less accurate estimates (right now).
Looks like a hash join might be faster. What is your work_mem set to?
work_mem= 1024
Can you SET enable_nestloop=off and rerun the EXPLAIN ANALYZE?
newschm3= set enable_nestloop=off ;
SET
newschm3= 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='team9';
QUERY PLAN
Merge Join (cost=34177.87..34291.36 rows=6707 width=22) (actual
time=68421.681..68547.686 rows=43050 loops=1)
Merge Cond: (outer.opset_num = inner.opset_num)
- Sort (cost=130.93..131.11 rows=71 width=18) (actual
time=107.744..107.901 rows=263 loops=1)
Sort Key: ro.opset_num
- Index Scan using run_opsets_pkey on run_opsets ro
(cost=0.00..128.75 rows=71 width=18) (actual time=57.641..106.096 rows=263
loops=1)
Index Cond: (run = 'team9'::text)
- Sort (cost=34046.94..34070.02 rows=9231 width=22) (actual
time=68301.325..68358.087 rows=43050 loops=1)
Sort Key: p.opset_num
- Bitmap Heap Scan on parameters p (cost=272.31..33438.97 rows=9231
width=22) (actual time=526.462..67363.577 rows=43050 loops=1)
Recheck Cond: ('team9'::text = run)
- Bitmap Index Scan on parameters_idx (cost=0.00..272.31
rows=9231 width=0) (actual time=483.500..483.500 rows=43050 loops=1)
Index Cond: ('team9'::text = run)
Total runtime: 68595.868 ms
(13 rows)
-- George Young
--
Are the gods not just? Oh no, child.
What would become of us if they were? (CSL)
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [PERFORM] simple join uses indexes, very slow
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 TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] simple join uses indexes, very slow
-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 [Snip] Indexes: 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 creating some different indexes on parameters? I don't know if it should matter or not, but I would try some indexes like: (run, opset_num) //Without all the other columns (opset_num, run) //Backwards (opset_num) I don't really know Postgres internals all that well. It just seems to me that parameters_idx has a lot of columns this query is not interested in. I'd just be curious to see what happens. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] simple join uses indexes, very slow
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 creating some different indexes on parameters? I don't know if it should matter or not, but I would try some indexes like: (run, opset_num) //Without all the other columns (opset_num, run) //Backwards (opset_num) 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 query on (B), (C) or (B,C), though. (The index rows will get bigger, of course, so you'll need more I/O if you want to scan large parts of it, but I guess that's beside the point.) /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] simple join uses indexes, very slow
-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 query on (B), (C) or (B,C), though. (The index rows will get bigger, of course, so you'll need more I/O if you want to scan large parts of it, but I guess that's beside the point.) I guess what I am really curious about is why was the OP getting an expensive sort when the planner tried a merge join? Most of the time was spent sorting the parameters parameters table by opset_num even though opset_num is indexed. Isn't Postgres able to walk the index instead of sorting? I was wondering if maybe Postgres wasn't recognizing that it could just walk the index because the opset_num column isn't the first in the index. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] simple join uses indexes, very slow
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 (opset, step, name) parameters_step_idx btree (step, name) Have you tried creating some different indexes on parameters? I don't know if it should matter or not, but I would try some indexes like: (run, opset_num) //Without all the other columns (opset_num, run) //Backwards (opset_num) 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 query on (B), (C) or (B,C), though. (The index rows will get bigger, of course, so you'll need more I/O if you want to scan large parts of it, but I guess that's beside the point.) Note that given how statistics currenly work, there are many situations where the planner will refuse to use a multi-column index. This probably won't change until there's some concept of multi-column statistics, at least for multi-column indexes. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] simple join uses indexes, very slow
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 to increase your free space map settings
Re: [PERFORM] simple join uses indexes, very slow
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 tried this, but it doesn't seem to have made much difference that I can see:
newschm3= show work_mem;
work_mem
--
8024
newschm3= 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='team9';
QUERY PLAN
---
Nested Loop (cost=2.16..7957.40 rows=6707 width=22) (actual
time=292.739..107672.525 rows=43050 loops=1)
- Index Scan using run_opsets_pkey on run_opsets ro (cost=0.00..128.75
rows=71 width=18) (actual time=115.134..197.818 rows=263 loops=1)
Index Cond: (run = 'team9'::text)
- Bitmap Heap Scan on parameters p (cost=2.16..109.93 rows=27 width=22)
(actual time=2.559..408.125 rows=164 loops=263)
Recheck Cond: (('team9'::text = p.run) AND (outer.opset_num =
p.opset_num))
- Bitmap Index Scan on parameters_idx (cost=0.00..2.16 rows=27
width=0) (actual time=2.099..2.099 rows=164 loops=263)
Index Cond: (('team9'::text = p.run) AND (outer.opset_num =
p.opset_num))
Total runtime: 107860.493 ms
(8 rows)
newschm3= shoe enable_nestloop;
ERROR: syntax error at or near shoe at character 1
LINE 1: shoe enable_nestloop;
^
newschm3= show enable_nestloop;
enable_nestloop
-
on
(1 row)
newschm3= set enable_nestloop=off;
SET
newschm3= 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='team9';
QUERY PLAN
---
Merge Join (cost=34177.87..34291.36 rows=6707 width=22) (actual
time=64654.744..64760.875 rows=43050 loops=1)
Merge Cond: (outer.opset_num = inner.opset_num)
- Sort (cost=130.93..131.11 rows=71 width=18) (actual time=62.177..62.333
rows=263 loops=1)
Sort Key: ro.opset_num
- Index Scan using run_opsets_pkey on run_opsets ro
(cost=0.00..128.75 rows=71 width=18) (actual time=40.415..55.745 rows=263
loops=1)
Index Cond: (run = 'team9'::text)
- Sort (cost=34046.94..34070.02 rows=9231 width=22) (actual
time=64592.526..64615.228 rows=43050 loops=1)
Sort Key: p.opset_num
- Bitmap Heap Scan on parameters p (cost=272.31..33438.97 rows=9231
width=22) (actual time=333.975..64126.200 rows=43050 loops=1)
Recheck Cond: ('team9'::text = run)
- Bitmap Index Scan on parameters_idx (cost=0.00..272.31
rows=9231 width=0) (actual time=309.199..309.199 rows=43050 loops=1)
Index Cond: ('team9'::text = run)
Total runtime: 64919.714 ms
(13 rows)
--
Are the gods not just? Oh no, child.
What would become of us if they were? (CSL)
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [PERFORM] simple join uses indexes, very slow
-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. Isn't Postgres able to walk the index instead of sorting? The time of an index scan vs. a sequential scan + sort depends on several factors, so it's not just a matter of walking the index whenever there is one. I was just looking this over again and I realized I misread the query plan. The slowest step was the Bitmap Heap Scan not the sort. (The sort was relatively fast.) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] simple join uses indexes, very slow
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 two tables.
Is there something I'm not seeing?
[CPU is 950Mhz AMD, 256MB RAM, 15k rpm scsi disk]
-- George Young
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='team9';
QUERY PLAN
Nested Loop (cost=2.16..7957.40 rows=6707 width=22) (actual
time=14.986..70197.129 rows=43050 loops=1)
- Index Scan using run_opsets_pkey on run_opsets ro (cost=0.00..128.75
rows=71 width=18) (actual time=0.386..62.959 rows=263 loops=1)
Index Cond: (run = 'team9'::text)
- Bitmap Heap Scan on parameters p (cost=2.16..109.93 rows=27 width=22)
(actual time=1.591..266.211 rows=164 loops=263)
Recheck Cond: (('team9'::text = p.run) AND (outer.opset_num =
p.opset_num))
- Bitmap Index Scan on parameters_idx (cost=0.00..2.16 rows=27
width=0) (actual time=1.153..1.153 rows=164 loops=263)
Index Cond: (('team9'::text = p.run) AND (outer.opset_num =
p.opset_num))
Total runtime: 70237.727 ms
(8 rows)
Table public.run_opsets
Column|Type |Modifiers
--+-+-
run | text| not null
opset| text|
opset_ver| integer |
opset_num| integer | not null
status | opset_status|
date_started | timestamp without time zone |
date_done| timestamp without time zone |
work_started | timestamp without time zone |
lock_user| text| default 'NO-USER'::text
lock_pid | integer |
needs_review | text|
Indexes:
run_opsets_pkey PRIMARY KEY, btree (run, opset_num) CLUSTER
-- Table public.parameters
Column | Type | Modifiers
---+-+---
run | text| not null
opset_num | integer | not null
opset | text| not null
opset_ver | integer | not null
step_num | integer | not null
step | text| not null
step_ver | integer | not null
name | text| not null
value | text|
split | boolean | not null default false
wafers| text[] | not null default '{}'::text[]
Indexes:
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)
More for my own information (because nobody else has suggested it),
would it make a difference if 'run' was a varchar field rather than text?
--
Postgresql php tutorials
http://www.designmagick.com/
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] simple join uses indexes, very slow
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, minor point releases aren't adding new features or changing
basic functionality, they are pure and simple bugfixes. If I was in
-your- position, I would run (don't walk ;) and install upto 8.1.3
of course, thats jst my 2c, feel free to ignore :D
Regards
Stef
Chris wrote:
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 two tables.
Is there something I'm not seeing?
[CPU is 950Mhz AMD, 256MB RAM, 15k rpm scsi disk]
-- George Young
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='team9';
QUERY PLAN
Nested Loop (cost=2.16..7957.40 rows=6707 width=22) (actual
time=14.986..70197.129 rows=43050 loops=1)
- Index Scan using run_opsets_pkey on run_opsets ro
(cost=0.00..128.75 rows=71 width=18) (actual time=0.386..62.959
rows=263 loops=1)
Index Cond: (run = 'team9'::text)
- Bitmap Heap Scan on parameters p (cost=2.16..109.93 rows=27
width=22) (actual time=1.591..266.211 rows=164 loops=263)
Recheck Cond: (('team9'::text = p.run) AND
(outer.opset_num = p.opset_num))
- Bitmap Index Scan on parameters_idx (cost=0.00..2.16
rows=27 width=0) (actual time=1.153..1.153 rows=164 loops=263)
Index Cond: (('team9'::text = p.run) AND
(outer.opset_num = p.opset_num))
Total runtime: 70237.727 ms
(8 rows)
Table public.run_opsets
Column|Type |Modifiers
--+-+-
run | text| not null
opset| text|
opset_ver| integer |
opset_num| integer | not null
status | opset_status|
date_started | timestamp without time zone |
date_done| timestamp without time zone |
work_started | timestamp without time zone |
lock_user| text| default 'NO-USER'::text
lock_pid | integer |
needs_review | text|
Indexes:
run_opsets_pkey PRIMARY KEY, btree (run, opset_num) CLUSTER
-- Table public.parameters
Column | Type | Modifiers
---+-+---
run | text| not null
opset_num | integer | not null
opset | text| not null
opset_ver | integer | not null
step_num | integer | not null
step | text| not null
step_ver | integer | not null
name | text| not null
value | text|
split | boolean | not null default false
wafers| text[] | not null default '{}'::text[]
Indexes:
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)
More for my own information (because nobody else has suggested it),
would it make a difference if 'run' was a varchar field rather than text?
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[PERFORM] simple join uses indexes, very slow
[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 something I'm not seeing?
[CPU is 950Mhz AMD, 256MB RAM, 15k rpm scsi disk]
-- George Young
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='team9';
QUERY PLAN
Nested Loop (cost=2.16..7957.40 rows=6707 width=22) (actual
time=14.986..70197.129 rows=43050 loops=1)
- Index Scan using run_opsets_pkey on run_opsets ro (cost=0.00..128.75
rows=71 width=18) (actual time=0.386..62.959 rows=263 loops=1)
Index Cond: (run = 'team9'::text)
- Bitmap Heap Scan on parameters p (cost=2.16..109.93 rows=27 width=22)
(actual time=1.591..266.211 rows=164 loops=263)
Recheck Cond: (('team9'::text = p.run) AND (outer.opset_num =
p.opset_num))
- Bitmap Index Scan on parameters_idx (cost=0.00..2.16 rows=27
width=0) (actual time=1.153..1.153 rows=164 loops=263)
Index Cond: (('team9'::text = p.run) AND (outer.opset_num =
p.opset_num))
Total runtime: 70237.727 ms
(8 rows)
Table public.run_opsets
Column|Type |Modifiers
--+-+-
run | text| not null
opset| text|
opset_ver| integer |
opset_num| integer | not null
status | opset_status|
date_started | timestamp without time zone |
date_done| timestamp without time zone |
work_started | timestamp without time zone |
lock_user| text| default 'NO-USER'::text
lock_pid | integer |
needs_review | text|
Indexes:
run_opsets_pkey PRIMARY KEY, btree (run, opset_num) CLUSTER
-- Table public.parameters
Column | Type | Modifiers
---+-+---
run | text| not null
opset_num | integer | not null
opset | text| not null
opset_ver | integer | not null
step_num | integer | not null
step | text| not null
step_ver | integer | not null
name | text| not null
value | text|
split | boolean | not null default false
wafers| text[] | not null default '{}'::text[]
Indexes:
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)
Are the gods not just? Oh no, child.
What would become of us if they were? (CSL)
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
