Re: [PERFORM] Why those queries do not utilize indexes?

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(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;
>
> 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(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
> email;internet:jd(at)commandprompt(dot)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