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(at)yahoo(dot)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(at)yahoo(dot)com>
> wrote:
> >
> > --- Hans-Juergen Schoenig <hs(at)cybertec(dot)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(at)yahoo(dot)com
> > Cell: 310-990-7550
> > Office: 310-336-5189
> > Christopher(dot)J(dot)Albertson(at)aero(dot)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(at)yahoo(dot)com
Cell: 310-990-7550
Office: 310-336-5189 Christopher(dot)J(dot)Albertson(at)aero(dot)org

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