Hi, maybe you should overthink your db-design, but thats a bit premature whithout your complete table-definitions(including table-names, datatypes, indexes, foreign-key constraints,etc.) If your are using pgadmin3 just cut'n paste the content of the window on the bottom left for the corresponding tables. If you're using psql try \d yur-table-name. Best regards 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 > -----Original Message----- > From: Christian Rengstl > [mailto:Christian.Rengstl@xxxxxxxxxxxxxxxxxxxxxxxx] > Sent: Thursday, August 03, 2006 11:18 AM > To: Richard Huxton; Hakan Kocaman > Cc: pgsql-general@xxxxxxxxxxxxxx > Subject: Re: [GENERAL] Query performance > > > 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 > >
