Re: Query performance

Hi,

i would rather compare int4 too, but the snp_id can be something like "abc123" unfortunately.

"Hakan Kocaman" <Hakan(dot)Kocaman(at)digame(dot)de> wrote on 08/03/06 11:08 am:
> Hi,
>
>
>> -----Original Message-----
>> From: Richard Huxton [mailto:dev(at)archonet(dot)com]
>> Sent: Thursday, August 03, 2006 11:00 AM
>> To: Christian Rengstl
>> Cc: Hakan Kocaman; pgsql-general(at)postgresql(dot)org
>> Subject: Re: [GENERAL] 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?
>>
>
> Yep, that bothered me too.
>
>> > 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.
>
> But wouldn't a comparison between int4 be much cheaper.
> If i see smth like "id" (here snp_id) in a fieldname it should be a
> int-type, i think.
>
>>
>> > "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-resou
> rce.html#RUNTIME-CONFIG-RESOURCE-MEMORY
>
> If it's still at 1MB (1024), try 5MB, 10MB, 50MB and see what happens.
>
> --
> Richard Huxton
> Archonet Ltd
>
>
>
> Hakan Kocaman
> Software-Development
>
> digame.de GmbH
> Richard-Byrd-Str. 4-8
> 50829 Köln
>
> Tel.: +49 (0) 221 59 68 88 31
> Fax: +49 (0) 221 59 68 88 98
> Email: hakan(dot)kocaman(at)digame(dot)de
>
> ---------------------------(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

--
Christian Rengstl M.A.
Klinik und Poliklinik für Innere Medizin II
Kardiologie - Forschung
Universitätsklinikum Regensburg
B3 1.388
Franz-Josef-Strauss-Allee 11
93053 Regensburg
Tel.: +49-941-944-7230