Message-ID: <20040827213348.88178.qmail@web11901.mail.yahoo.com>
Date: Fri, 27 Aug 2004 14:33:48 -0700 (PDT)
From: Artimenko Igor <igorart7@yahoo.com>
Subject: Re: [PERFORM] Why those queries do not utilize indexes?
To: pgsql-sql@postgresql.org
In-Reply-To: <412FA450.4020605@commandprompt.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Thanks a lot but none of those queries was effecient.

1. This query is longest.
explain analyze SELECT * FROM messageinfo  WHERE user_id::bigint = 20000 and msgstatus::smallint =
0;

2. This one is the same as my original in performance and bad index usage.
explain analyze SELECT * FROM messageinfo  WHERE user_id = 20000::bigint and msgstatus =
0::smallint;


--- "Joshua D. Drake" <jd@commandprompt.com> wrote:

> Artimenko Igor wrote:
> > I could force Postgres to use the best index by removing condition "msgstatus = CAST( 0 AS
> > smallint );" from WHERE clause & set enable_seqscan to off;
> > Total runtime in this case dropped from 1883 ms ( sequential reads ) to 1.598 ms ( best index
> ).
> 
> 
> WHERE user_id::bigint = 20000 and msgstatus:smallint = 0;
> 
> Sincerely,
> 
> Joshau D. Drake
> 
> 
> > 
> > But unfortunatelly It does not resolve my problem. I can not remove above condition. I need to
> > find a way to use whole condition "WHERE user_id = CAST( 20000 AS BIGINT ) and msgstatus =
> CAST( 0
> > AS smallint );" and still utilyze index.  
> > 
> > Yes you are right. Using "messagesStatus" index is even worse for my data set then sequential
> > scan.
> > 
> > Igor Artimenko
> > 
> > --- Dennis Bjorklund <db@zigo.dhs.org> wrote:
> > 
> > 
> >>On Fri, 27 Aug 2004, Artimenko Igor wrote:
> >>
> >>
> >>>1. Sequential search and very high cost if set enable_seqscan to on;
> >>>Seq scan on messageinfo ( cost=0.00..24371.30, rows =36802 )
> >>>
> >>>2. Index scan but even bigger cost if set enable_seqscan to off;
> >>>Index “messagesStatus” on messageinfo ( Cost=0.00..27220.72, rows=36802 )
> >>
> >>So pg thinks that a sequential scan will be a little bit faster (The cost 
> >>is a little bit smaller). If you compare the actual runtimes maybe you 
> >>will see that pg was right. In this case the cost is almost the same so 
> >>the runtime is probably almost the same.
> >>
> >>When you have more data pg will start to use the index since then it will 
> >>be faster to use an index compared to a seq. scan.
> >>
> >>-- 
> >>/Dennis Björklund
> >>
> >>
> > 
> > 
> > 
> > 
> > 		
> > _______________________________
> > Do you Yahoo!?
> > Win 1 of 4,000 free domain names from Yahoo! Enter now.
> > http://promotions.yahoo.com/goldrush
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 
> 
> -- 
> Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
> Postgresql support, programming shared hosting and dedicated hosting.
> +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
> Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
> > begin:vcard
> fn:Joshua D. Drake
> n:Drake;Joshua D.
> org:Command Prompt, Inc.
> adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA
> email;internet:jd@commandprompt.com
> title:Consultant
> tel;work:503-667-4564
> tel;fax:503-210-0334
> note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support
> provider. We  provide the only commercially viable integrated PostgreSQL replication solution,
> but also custom programming, and support. We authored  the book Practical PostgreSQL, the
> procedural language plPHP, and adding trigger capability to plPerl.
> x-mozilla-html:FALSE
> url:http://www.commandprompt.com/
> version:2.1
> end:vcard
> 
> 


=====
Thanks a lot
Igor Artimenko
I specialize in 
Java, J2EE, Unix, Linux, HP, AIX, Solaris, Progress, Oracle, DB2, Postgres, Data Modeling


__________________________________
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail