Re: Query Speed!!!

Try this then re-run the querry.

  CREATE INDEX date_indx ON loom_details (date);
  VACUUM  FULL ANALYZE;

I don't like using "SELECT *" in code because if later I
add a new column I have to go back and fix all those querries.
And also the querry is hard to read later with out looking up
the table's structure.  But clearly "select * works and I use
i at the psql prompt all the time.

--- Thirumoorthy Bhuvneswari <tbhuvneswari@yahoo.com> wrote:
> hi,
> my query looks like this:
>
> select * from loom_details where date>='2002-06-01'
> and date<='2002-06-30'.
>
> * I do give 'select *' since all the columns are
> required for the report.
>
> * When I give the above query in the 'psql' prompt it
> waits for 1 second to fetch the records and displays
> the entire rows(about 3000).
>
> * There are about 10,000 records total in the table.
>
> * When I run the report, it actually does not increase
> the RAM usage but takes about 90% of the CPU time
> leaving all the other queries behind it.
>
> Please tell me how can I increase the speed. thanks.
>
> regards,
> T.Bhuvaneswari.
>
> --- Chris Albertson <chrisalbertson90278@yahoo.com>
> wrote:
> >
> > --- Hans-Juergen Schoenig <hs@cybertec.at> wrote:
> > > Thirumoorthy Bhuvneswari wrote:
> > > > hi,
> > > > I am using Postgresql-7.1.3 with RedHat
> > Linux-7.1 and
> > > > JDBC. I am having an indexed table with nearly
> > 3,000
> > > > records for a month. My machine is with 256 MB
> > RAM,
> > > > 600 MHz. I am having a report from the abovesaid
> > > > table, which displays 1 month's records at a
> > stretch
> > > > using Swing components. If I run the query from
> > the
> > > > hard-disk
> > > > it takes about 5 seconds to display all the
> > 3,000
> > > > records. If I connect 3 nodes to it and run the
> > query
> > > > from a node, it takes about 1.5 minutes. I don't
> > know
> > > > where I am wrong. Please do reply. thanks.
> >
> > Please tell us two more things:
> >
> > 1) If you enter the query by hand at the psql prompt
> > how
> >    long does the query take?
> >
> > 2) What is the SQL query?  Show us the SQL you
> > entered.
> >
> > I would expect much beter 10x to 100x speeds for
> > most "normal"
> > queries.  But do the above _after_ a "vacuum full
> > analyze"
> >
> > =====
> > Chris Albertson
> >   Home:   310-376-1029
> > chrisalbertson90278@yahoo.com
> >   Cell:   310-990-7550
> >   Office: 310-336-5189
> > Christopher.J.Albertson@aero.org
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Sign up for SBC Yahoo! Dial - First Month Free
> > http://sbc.yahoo.com
> >
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
>
>
> __________________________________________________
> Do You Yahoo!?
> Sign up for SBC Yahoo! Dial - First Month Free
> http://sbc.yahoo.com


=====
Chris Albertson
  Home:   310-376-1029  chrisalbertson90278@yahoo.com
  Cell:   310-990-7550
  Office: 310-336-5189  Christopher.J.Albertson@aero.org

__________________________________________________
Do You Yahoo!?
Sign up for SBC Yahoo! Dial - First Month Free
http://sbc.yahoo.com