Christian Rengstl wrote:
Hi,
the complete query is the one i posted, but here comes the schema for mytable:
entry_no int8 NOT NULL DEFAULT nextval('entry_no_seq''::regclass),
pid varchar(15) NOT NULL,
crit varchar(13) NOT NULL,
val1 varchar(1),
val2 varchar(1),
aendat text,
aennam varchar(8),
CONSTRAINT "PK_ENTRY" PRIMARY KEY (entry_no)
myCritTable:
crit varchar(13) NOT NULL,
chr int2,
aendat timestamp,
CONSTRAINT pk_crit_master PRIMARY KEY (crit)
Still doesn't match the EXPLAIN output - where's snp_id? Where's table
test2?
My server is 8.1.4. As a matter of fact, i have no idea where the text
> type comes from, because as you can see from above there are only
> varchar with maximum 15 characters.
PG is casting it to text. There's no real difference between the types
(other than the size limit) and it's not expensive.
"Hakan Kocaman" <Hakan.Kocaman@xxxxxxxxx> wrote on 08/03/06 10:34 am:
Hi,
can you post the complete query,schema- and
table-definition,server-version etc. ?
This will help to identity the main problem.
So at the moment i'm just guessing:
Hash Cond: (("outer".snp_id)::text = ("inner".snp_id)::text)
-> Bitmap Heap Scan on test2 (cost=232.92..132766.66 rows=37120
width=23)
(actual time=291.600..356707.737 rows=37539 loops=1)
This part is very expensive, but i got no clue why.
Yep, it looks like the "Bitmap Heap Scan" is at the heart of this. You
might want to increase work_mem, it could be that the bitmap is spilling
to disk (which is much slower than keeping it all in RAM)
http://www.postgresql.org/docs/8.1/static/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-MEMORY
If it's still at 1MB (1024), try 5MB, 10MB, 50MB and see what happens.
--
Richard Huxton
Archonet Ltd
