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 profileOn Mon, Nov 22, 2010 at 3:26 AM, richard terry
<rterry@pacific.net.au> wrote:
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