Re: Query performance - Mailing list pgsql-general

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