Re: Query performance

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