X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 05B6E56627 for ; Thu, 17 Mar 2005 09:37:10 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 59260-06 for ; Thu, 17 Mar 2005 09:37:01 +0000 (GMT) Received: from floppy.pyrenet.fr (unknown [194.116.145.2]) by svr1.postgresql.org (Postfix) with ESMTP id CF87654DAC for ; Thu, 17 Mar 2005 09:36:59 +0000 (GMT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id D450B30959; Thu, 17 Mar 2005 10:36:59 +0100 (MET) From: Daniel Schuchardt X-Newsgroups: pgsql.performance Subject: Re: cpu_tuple_cost Date: Thu, 17 Mar 2005 10:37:05 +0100 Organization: Hub.Org Networking Services Lines: 88 Message-ID: References: <5580.1110781746@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org User-Agent: Mozilla Thunderbird 0.8 (Windows/20040913) X-Accept-Language: en-us, en In-Reply-To: <5580.1110781746@sss.pgh.pa.us> To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200503/400 X-Sequence-Number: 11105 Tom Lane wrote: > > Reducing random_page_cost is usually the best way to get the planner to > favor indexscans more. > Ok, I tried a bit with random_page_cost and I have set it to 1 to become PG using the index on mitpln: CIMSOFT=# ANALYSE mitpln; ANALYZE CIMSOFT=# SET random_page_cost=2; SET CIMSOFT=# EXPLAIN ANALYSE SELECT * FROM mitpln WHERE date_to_yearmonth_dec(mpl_date)='20050'; QUERY PLAN -------------------------------------------------------------------------------- Seq Scan on mitpln (cost=0.00..1173.78 rows=1431 width=69) (actual time=219.000..1125.000 rows=1266 loops=1) Filter: ((date_to_yearmonth_dec((mpl_date)::timestamp without time zone))::text = '20050'::text) Total runtime: 1125.000 ms (3 rows) CIMSOFT=# SET random_page_cost=1; SET CIMSOFT=# EXPLAIN ANALYSE SELECT * FROM mitpln WHERE date_to_yearmonth_dec(mpl_date)='20050'; QUERY PLAN -------------------------------------------------------------------------------- Index Scan using mitpln_yearmonth_dec on mitpln (cost=0.00..699.01 rows=1431 width=69) (actual time=0.000..16.000 rows=1266 loops=1) Index Cond: ((date_to_yearmonth_dec((mpl_date)::timestamp without time zone))::text = '20050'::text) Total runtime: 16.000 ms (3 rows) CIMSOFT=# \d mitpln Table "public.mitpln" Column | Type | Modifiers --------------+-----------------------+----------------------------------------- mpl_id | integer | not null default nextval('public.mitpln_mpl_id_seq'::text) mpl_date | date | mpl_minr | integer | not null mpl_tpl_name | character varying(20) | mpl_feiertag | character varying(50) | mpl_min | real | mpl_saldo | real | mpl_buch | boolean | not null default false mpl_absaldo | real | mpl_vhz | real | dbrid | character varying | default nextval('db_id_seq'::text) Indexes: "mitpln_pkey" PRIMARY KEY, btree (mpl_id) "mitpln_idindex" UNIQUE, btree (dbrid) "xtt5126" UNIQUE, btree (mpl_date, mpl_minr) "mitpln_yearmonth_dec" btree (date_to_yearmonth_dec(mpl_date::timestamp with out time zone)) "mpl_minr" btree (mpl_minr) "mpl_minr_nobuch" btree (mpl_minr) WHERE NOT mpl_buch CIMSOFT=# SELECT count(*) FROM mitpln; count ------- 26330 (1 row) CREATE OR REPLACE FUNCTION date_to_yearmonth_dec(TIMESTAMP) RETURNS VARCHAR AS' BEGIN RETURN extract(year FROM $1) || extract(month FROM $1)-1; END'LANGUAGE plpgsql IMMUTABLE; Daniel PS : thats a 2.4 GHZ P4 Server with 1 GB Ram and RAID - SCSI (WIN2000, PG8.0.1)