Hi, > -----Original Message----- > From: Richard Huxton [mailto:dev@xxxxxxxxxxxx] > Sent: Thursday, August 03, 2006 11:00 AM > To: Christian Rengstl > Cc: Hakan Kocaman; pgsql-general@xxxxxxxxxxxxxx > 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@xxxxxxxxx> 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@xxxxxxxxx
