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 2FBE03A5431 for ; Wed, 1 Dec 2004 04:05:17 +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 54001-02 for ; Wed, 1 Dec 2004 04:05:11 +0000 (GMT) Received: from bluebamboo.ph (unknown [202.175.252.53]) by svr1.postgresql.org (Postfix) with ESMTP id 4184F3A5445 for ; Wed, 1 Dec 2004 04:05:09 +0000 (GMT) Received: from irridescens ([192.168.0.180]) by bluebamboo.ph (8.11.6/8.11.6) with ESMTP id iB145Ei21391; Wed, 1 Dec 2004 12:05:14 +0800 From: "BBI Edwin Punzalan" To: "'Iain'" , "'gnari'" , Subject: Re: FW: Index usage Date: Wed, 1 Dec 2004 12:05:18 +0800 Message-ID: <006101c4d75a$f87a0030$b400a8c0@bluebamboo.ph> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.4510 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 In-reply-to: <00d101c4d75a$4bdc1700$7201a8c0@mst1x5r347kymb> Importance: Normal 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: 200412/6 X-Sequence-Number: 9381 Hi. Thanks for your reply. The date column data type is date already. = :D -----Original Message----- From: Iain [mailto:iain@mst.co.jp]=20 Sent: Wednesday, December 01, 2004 12:00 PM To: BBI Edwin Punzalan; 'gnari'; pgsql-performance@postgresql.org Subject: Re: [PERFORM] FW: Index usage If it's any help, i just ran this test on 7.4.6, my table has about = 7000000=20 rows and the index is an integer. The item id ranges from 1 to 20000. As you can see from the following plans, the optimizer changed it's plan = depending on the value of the item id condition, and will use an index = when=20 it determines that the number of values that will be returned is a low % = of=20 the total table size. The item_id is an integer, but It looked like you are using a character=20 field to store date information. Also, the dates you entered in your = test=20 case seem to be in the format DD/MM/YY which won't be amenable to useful = comparative searching (I didn't read any of the earlier posts so if that = isn't the case, just ignore this). If this is the case, try storing the = data in a date column and see what happens then. regards Iain test=3D# explain analyse select * from bigtable where item_id <=3D 1000; =20 QUERY=20 PLAN -------------------------------------------------------------------------= --- --------------------------------------- -------------------------------------------- Index Scan using d_bigtable_idx2 on bigtable (cost=3D0.00..118753.57=20 rows=3D59553 width=3D80) (actual time=3D0.069..704.401 rows=3D58102 loops=3D1) Index Cond: ((item_id)::integer <=3D 1000) Total runtime: 740.786 ms (3 rows) test=3D# explain analyse select * from bigtable where item_id <=3D = 100000000; QUERY PLAN -------------------------------------------------------------------------= --- --------------------------------------- --------------- Seq Scan on d_hi_mise_item_uri (cost=3D0.00..194285.15 rows=3D7140589=20 width=3D80) (actual time=3D0.027..18599.032 rows=3D71 14844 loops=3D1) Filter: ((item_id)::integer <=3D 100000000) Total runtime: 23024.986 ms ----- Original Message -----=20 From: "BBI Edwin Punzalan" To: "'gnari'" ; Sent: Wednesday, December 01, 2004 11:33 AM Subject: Re: [PERFORM] FW: Index usage > > Thanks but whatever it does, it didn't work. :D > > Do you think upgrading will fix this problem? > > = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= > db=3D# alter table chatlogs alter column date set statistics 300; = ALTER > db=3D# analyze chatlogs; > ANALYZE > db=3D# explain analyze select * from chatlogs where date >=3D = '12/1/04'; > NOTICE: QUERY PLAN: > > Index Scan using chatlogs_type_idx on chatlogs (cost=3D0.00..6053.61=20 > rows=3D3357 width=3D212) (actual time=3D22.14..138.53 rows=3D1312 > loops=3D1) > Total runtime: 139.42 msec > > EXPLAIN > morphTv=3D# explain analyze select * from chatlogs where date >=3D=20 > '11/03/04'; > NOTICE: QUERY PLAN: > > Seq Scan on chatlogs (cost=3D0.00..27252.86 rows=3D271882 = width=3D212)=20 > (actual time=3D12.24..13419.36 rows=3D257137 loops=3D1) Total runtime: = > 13573.70 msec > > EXPLAIN > = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= > > > > -----Original Message----- > From: gnari [mailto:gnari@simnet.is] > Sent: Wednesday, December 01, 2004 10:08 AM > To: BBI Edwin Punzalan; pgsql-performance@postgresql.org > Subject: Re: [PERFORM] FW: Index usage > > > From: "BBI Edwin Punzalan" > > >> >> Hi, what do you mean by increasing the statistics on the date column? > > alter table chatlogs alter column date set statistics 300; analyze > chatlogs; > >> > > Our version is 7.2.1 >> > >> > upgrade time ? >> >> We never had any upgrade on it. > > 7.2 is a bit dated now that 8.0 is in beta > > if you want to stay with 7.2, you should at least upgrade > to the latest point release (7.2.6 ?), as several serious bugs have=20 > been fixed > > gnari > > > ---------------------------(end of=20 > broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to = majordomo@postgresql.org=20