Re: Query performance

Hi,

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

"Hakan Kocaman" <Hakan.Kocaman@digame.de> wrote on 08/03/06 11:08 am:
> Hi,
>
>
>> -----Original Message-----
>> From: Richard Huxton [mailto:dev@archonet.com]
>> Sent: Thursday, August 03, 2006 11:00 AM
>> To: Christian Rengstl
>> Cc: Hakan Kocaman; pgsql-general@postgresql.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.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-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.kocaman@digame.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