Skip site navigation (1) Skip section navigation (2)

Re: [PERFORM] optimizing query

From: Chantal Ackermann <chantal(dot)ackermann(at)biomax(dot)de>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>,Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
Cc: pgsql-general(at)postgresql(dot)org, pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] optimizing query
Date: 2003-01-23 15:52:51
Message-ID: 3E300FD3.3030100@biomax.de (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-performance
hi Stephan, hi Tom,

sort_mem was at its default: 1024. I increased it, and the query takes 
even longer (~ 36 secs). I tried two different values: 4096 and 8192, 
this last time I reduced the shared_buffers to 25600 (--> ~ 37 secs).
Another point is: after a vacuum, the cost would slightly increase.

would it help to cluster the index? but as I am using several indexes I 
find it difficult to decide on which index to cluster.

(I paste the output from vacuum full verbose analyze)

Thanks!
Chantal


++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

INFO:  --Relation public.disease_occurrences_puid--
INFO:  Pages 2079: Changed 0, reaped 0, Empty 0, New 0; Tup 471915: Vac 
0, Keep/VTL 0/0, UnUsed 0, MinLen 32, MaxLen 32; Re-using: Free/Avail. 
Space 648/648; EndEmpty/Avail. Pages 0/1.
         CPU 0.02s/0.05u sec elapsed 0.07 sec.
INFO:  Index disease_occpd_puid_i: Pages 1036; Tuples 471915.
         CPU 0.00s/0.03u sec elapsed 0.03 sec.
INFO:  Index disease_id_puid_uni: Pages 1297; Tuples 471915.
         CPU 0.03s/0.05u sec elapsed 0.23 sec.
INFO:  Rel disease_occurrences_puid: Pages: 2079 --> 2079; Tuple(s) 
moved: 0.
         CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  Analyzing public.disease_occurrences_puid

INFO:  --Relation public.gene_occurrences_puid--
INFO:  Pages 1495: Changed 0, reaped 0, Empty 0, New 0; Tup 339347: Vac 
0, Keep/VTL 0/0, UnUsed 0, MinLen 32, MaxLen 32; Re-using: Free/Avail. 
Space 648/648; EndEmpty/Avail. Pages 0/1.
         CPU 0.01s/0.04u sec elapsed 0.05 sec.
INFO:  Index gene_occpd_puid_i: Pages 746; Tuples 339347.
         CPU 0.01s/0.02u sec elapsed 0.03 sec.
INFO:  Index gene_id_puid_uni: Pages 934; Tuples 339347.
         CPU 0.00s/0.02u sec elapsed 0.02 sec.
INFO:  Rel gene_occurrences_puid: Pages: 1495 --> 1495; Tuple(s) moved: 0.
         CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  Analyzing public.gene_occurrences_puid

INFO:  --Relation public.disease--
INFO:  Pages 1522: Changed 0, reaped 0, Empty 0, New 0; Tup 164597: Vac 
0, Keep/VTL 0/0, UnUsed 0, MinLen 44, MaxLen 232; Re-using: Free/Avail. 
Space 56920/38388; EndEmpty/Avail. Pages 0/603.
         CPU 0.00s/0.04u sec elapsed 0.04 sec.
INFO:  Index disease_name_i: Pages 1076; Tuples 164597.
         CPU 0.05s/0.02u sec elapsed 0.18 sec.
INFO:  Index disease_pkey: Pages 364; Tuples 164597.
         CPU 0.00s/0.00u sec elapsed 0.03 sec.
INFO:  Index disease_uni: Pages 1168; Tuples 164597.
         CPU 0.08s/0.04u sec elapsed 0.22 sec.
INFO:  Rel disease: Pages: 1522 --> 1521; Tuple(s) moved: 75.
         CPU 0.00s/0.03u sec elapsed 0.04 sec.
INFO:  Index disease_name_i: Pages 1077; Tuples 164597: Deleted 75.
         CPU 0.00s/0.03u sec elapsed 0.03 sec.
INFO:  Index disease_pkey: Pages 364; Tuples 164597: Deleted 75.
         CPU 0.01s/0.02u sec elapsed 0.02 sec.
INFO:  Index disease_uni: Pages 1168; Tuples 164597: Deleted 75.
         CPU 0.00s/0.03u sec elapsed 0.03 sec.
INFO:  --Relation pg_toast.pg_toast_7114632--
INFO:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0, 
Keep/VTL 0/0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space 
0/0; EndEmpty/Avail. Pages 0/0.
         CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  Index pg_toast_7114632_index: Pages 1; Tuples 0.
         CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  Analyzing public.disease

INFO:  --Relation public.gene--
INFO:  Pages 1566: Changed 0, reaped 0, Empty 0, New 0; Tup 218085: Vac 
0, Keep/VTL 0/0, UnUsed 0, MinLen 44, MaxLen 348; Re-using: Free/Avail. 
Space 48692/25408; EndEmpty/Avail. Pages 0/365.
         CPU 0.01s/0.04u sec elapsed 0.04 sec.
INFO:  Index gene_pkey: Pages 481; Tuples 218085.
         CPU 0.00s/0.01u sec elapsed 0.01 sec.
INFO:  Index gene_uni: Pages 1038; Tuples 218085.
         CPU 0.04s/0.01u sec elapsed 0.19 sec.
INFO:  Index gene_name_uni: Pages 917; Tuples 218085.
         CPU 0.06s/0.00u sec elapsed 0.15 sec.
INFO:  Rel gene: Pages: 1566 --> 1564; Tuple(s) moved: 230.
         CPU 0.01s/0.06u sec elapsed 0.11 sec.
INFO:  Index gene_pkey: Pages 482; Tuples 218085: Deleted 230.
         CPU 0.00s/0.03u sec elapsed 0.02 sec.
INFO:  Index gene_uni: Pages 1041; Tuples 218085: Deleted 230.
         CPU 0.00s/0.04u sec elapsed 0.03 sec.
INFO:  Index gene_name_uni: Pages 918; Tuples 218085: Deleted 230.
         CPU 0.00s/0.04u sec elapsed 0.03 sec.
INFO:  --Relation pg_toast.pg_toast_7114653--
INFO:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0, 
Keep/VTL 0/0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space 
0/0; EndEmpty/Avail. Pages 0/0.
         CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  Index pg_toast_7114653_index: Pages 1; Tuples 0.
         CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  Analyzing public.gene

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


In response to

pgsql-performance by date

Next:From: Josh BerkusDate: 2003-01-23 17:00:02
Subject: Re: Same query, same performance
Previous:From: Curt SampsonDate: 2003-01-23 15:50:27
Subject: Re: [PERFORM] Terrible performance on wide selects

pgsql-general by date

Next:From: Ian HardingDate: 2003-01-23 15:58:17
Subject: createlang and Schemas
Previous:From: Andrei Verovski (aka MacGuru)Date: 2003-01-23 15:51:36
Subject: PHP Abstraction Layer - Your Opinion Please

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group