Re: Help with date query

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 <http://uk.linkedin.com/in/matthewdwalden>

On Mon, Nov 22, 2010 at 3:26 AM, richard terry <rterry(at)pacific(dot)net(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>