Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id EDE4A476FC7; Thu, 23 Jan 2003 11:17:50 -0500 (EST) Received: from biomax.de (unknown [212.6.137.236]) by postgresql.org (Postfix) with ESMTP id 45FD74770A3; Thu, 23 Jan 2003 10:53:09 -0500 (EST) Received: from biomax.de (guffert.biomax.de [192.168.3.166]) by biomax.de (8.8.8/8.8.8) with ESMTP id QAA18901; Thu, 23 Jan 2003 16:52:51 +0100 Message-ID: <3E300FD3.3030100@biomax.de> Date: Thu, 23 Jan 2003 16:52:51 +0100 From: Chantal Ackermann User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.2.1) Gecko/20021130 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane , Stephan Szabo Cc: pgsql-general@postgresql.org, pgsql-performance@postgresql.org Subject: Re: [PERFORM] optimizing query References: <20030122081422.Y96911-100000@megazone23.bigpanda.com> <3E2FB2D1.9020904@biomax.de> <25976.1043335579@sss.pgh.pa.us> In-Reply-To: <25976.1043335579@sss.pgh.pa.us> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/928 X-Sequence-Number: 36049 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 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++