Обсуждение: Help with date query

Hi list,

I have a huge table of documents with many many thousands of rows (currently
only 18000 , and will as it grows to many many times that over time.

I need some way to select out the documents from a fixed time period, the last
(n) months:

I've tried this in pgAdmin:


Select (the-fields-I-want)
from documents.vwDocuments where deleted = False
AND date_created  between '01/11/2010' AND  Now()
order by date_created DESC LIMIT 10;

Date_created is an un-indexed field  (can one index date fields?)

This unbeleivable slow, some 25 seconds over the 18000 records.

Displaying the records where document records where they belong to an
individual person is really quick within my program (gambas).

Any ways of re-phrasing this better, optimizing?

Any help appreciated.

richard

Richard,

You can create an index on a date column.  I don't think it should take too long to create on a table that size but just be aware that updates and inserts will not be allowed while it is being created.  You'll probably find your query flies once it has been created.

If you have access to psql I would personally want to create an explain plan of the SQL before and after the index creation to confirm it is worth the maintenance cost.

--
View my Linkedin profile

That sounds a bit more reasonable!  Glad it worked out OK.

--
View my Linkedin profile

On Monday 22 November 2010 23:10:55 Matthew Walden wrote:

Thanks, yes, that made a huge difference.
> Richard,
>
> You can create an index on a date column.  I don't think it should take too
> long to create on a table that size but just be aware that updates and
> inserts will not be allowed while it is being created.  You'll probably
>  find your query flies once it has been created.
>
> If you have access to psql I would personally want to create an explain
>  plan of the SQL before and after the index creation to confirm it is worth
>  the maintenance cost.
>
> > Hi list,
> >
> > I have a huge table of documents with many many thousands of rows
> > (currently
> > only 18000 , and will as it grows to many many times that over time.
> >
> > I need some way to select out the documents from a fixed time period, the
> > last
> > (n) months:
> >
> > I've tried this in pgAdmin:
> >
> >
> > Select (the-fields-I-want)
> > from documents.vwDocuments where deleted = False
> > AND date_created  between '01/11/2010' AND  Now()
> > order by date_created DESC LIMIT 10;
> >
> > Date_created is an un-indexed field  (can one index date fields?)
> >
> > This unbeleivable slow, some 25 seconds over the 18000 records.
> >
> > Displaying the records where document records where they belong to an
> > individual person is really quick within my program (gambas).
> >
> > Any ways of re-phrasing this better, optimizing?
> >
> > Any help appreciated.
> >
> > richard
> >
> >
> > --
> > Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-novice
>