From: | richard terry <rterry(at)pacific(dot)net(dot)au> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Help with date query |
Date: | 2010-11-22 20:59:06 |
Message-ID: | 201011230759.06963.rterry@pacific.net.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | admin | 2010-11-22 22:20:54 | pg_upgrade tablespace directory problem |
Previous Message | Matthew Walden | 2010-11-22 20:58:39 | Re: Help with date query |