Search Postgresql Archives

Re: Query performance

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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)

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.

"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.
> Maybe the text-type is not so ideal.
> 
> 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: pgsql-general-owner@xxxxxxxxxxxxxx 
>> [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of 
>> Christian Rengstl
>> Sent: Thursday, August 03, 2006 10:13 AM
>> To: pgsql-general@xxxxxxxxxxxxxx 
>> Subject: [GENERAL] Query performance
>> 
>> 
>> Hi everyone,
>> 
>> i have a table with around 57 million tuples, with the 
>> following columns: pid(varchar), crit(varchar), 
>> val1(varchar), val2(varchar). Example:
>> pid    crit    val1    val2
>> p1      c1      x        y
>> p1      c2      x        z
>> p1      c3      y        x
>> ...
>> What i am doing is to query all val1 and val2 for one pid and 
>> all crit values:
>> 
>> select val1, val2, crit from mytable where pid='somepid' and 
>> crit in(select crit from myCritTable);
>> where myCritTable is a table that contains all crit values 
>> (around 42.000) ordered by their insertion date.
>> 
>> 
>> QUERY PLAN
>> 
>> --------------------------------------------------------------
>> ------------------
>> ----------------------------------------------------------
>>  Hash IN Join  (cost=1033.67..134959.41 rows=37120 width=23) 
>> (actual time=357.11
>> 6..356984.535 rows=37539 loops=1)
>>    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)
>>          Recheck Cond: ((pid)::text = '1'::text)
>>          ->  Bitmap Index Scan on idx_test2_pid  
>> (cost=0.00..232.92 rows=37120 w
>> idth=0) (actual time=234.516..234.516 rows=37539 loops=1)
>>                Index Cond: ((pid)::text = '1'::text)
>>    ->  Hash  (cost=700.20..700.20 rows=40220 width=13) 
>> (actual time=65.055..65.0
>> 55 rows=40220 loops=1)
>>          ->  Seq Scan on snps_test  (cost=0.00..700.20 
>> rows=40220 width=13) (act
>> ual time=0.020..30.131 rows=40220 loops=1)
>>  Total runtime: 357017.259 ms
>> 
>> Unfortunately the query takes pretty long for the big table, 
>> so maybe one of you has a suggestion on how to make it faster.
>> 
>> --
>> 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
>> 
>> 
>> ---------------------------(end of 
>> broadcast)---------------------------
>> TIP 4: Have you searched our list archives?
>> 
>>                http://archives.postgresql.org 
>> 


--
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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux