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" <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED]) > > > -- > Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC > Postgresql support, programming shared hosting and dedicated hosting. > +1-503-667-4564 - [EMAIL PROTECTED] - 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:[EMAIL PROTECTED] > 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 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
