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 =
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 =
--- "Joshua D. Drake" <jd(at)commandprompt(dot)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;
> 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(at)zigo(dot)dhs(dot)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 Bjrklund
> > _______________________________
> > 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(at)postgresql(dot)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(at)commandprompt(dot)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
> 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.
Thanks a lot
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!
pgsql-sql by date
|Next:||From: Gaetano Mendola||Date: 2004-08-28 02:16:45|
|Subject: Re: backup of a specific schema|
|Previous:||From: Artimenko Igor||Date: 2004-08-27 20:53:38|
|Subject: Re: Copy command freezes but INSERT works fine with trigger on insert. |