Re: Query performance

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(dot)Kocaman(at)digame(dot)de> 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