Hi, i would rather compare int4 too, but the snp_id can be something like "abc123" unfortunately. "Hakan Kocaman" <Hakan.Kocaman@xxxxxxxxx> wrote on 08/03/06 11:08 am: > 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 > > ---------------------------(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
