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.Kocaman@digame.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