Received: from smtp010.mail.yahoo.com (smtp010.mail.yahoo.com [216.136.173.30]) by postgresql.org (8.11.3/8.11.1) with SMTP id f6UIsGf90107 for ; Mon, 30 Jul 2001 14:54:16 -0400 (EDT) (envelope-from chrup999@yahoo.com) Received: from proxy.compucom.com (HELO Z161842) (204.214.145.2) by smtp.mail.vip.sc5.yahoo.com with SMTP; 30 Jul 2001 18:54:13 -0000 X-Apparently-From: Message-ID: <00b501c11929$096a4ec0$5dd26383@corp.compucom.com> From: "Chris Ruprecht" To: "pgsql" References: <006401c11913$9a563ae0$5dd26383@corp.compucom.com> <01d501c11916$b81603f0$48d210ac@jecw2k1> Subject: Re: Big table - using wrong index - why? Date: Mon, 30 Jul 2001 13:54:18 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200107/386 X-Sequence-Number: 4200 Hi Joe, I found the problem - it was a typical "aaaarrrgggghhh" - error. Since pseq is declared int8, I need to say select * from phonelog where entity = '001' and pseq >= 9120::int8 and pseq <= 9123::int8; (casting the two numbers). Then, it works like a charm ... Best regards, Chris ----- Original Message ----- From: "Joe Conway" To: "Chris Ruprecht" ; "pgsql" Sent: Monday, July 30, 2001 11:43 AM Subject: Re: [SQL] Big table - using wrong index - why? > > phones=# \d i_pl_pseq > > Index "i_pl_pseq" > > Attribute | Type > > -----------+---------------------- > > entity | character varying(3) > > pseq | bigint > > btree > > > > phones=# explain select * from phonelog where entity = '001' and pseq >= > > 9120 and pseq <= 9123; > > NOTICE: QUERY PLAN: > > > > Index Scan using i_pl_loadtimestamp on phonelog (cost=0.00..209247.39 > > rows=607 width=137) > > > > EXPLAIN > > > > phones=# \d i_pl_loadtimestamp > > Index "i_pl_loadtimestamp" > > Attribute | Type > > -----------+---------------------- > > entity | character varying(3) > > loaddate | date > > loadtime | time > > btree > > Just a guess, but what happens if you build i_pl_pseq(pseq, entity), i.e. > reverse the key fields? Also, has the table been vacuum analyzed? > > -- Joe > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com