Re: Help with date query

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(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-novice
>