Skip site navigation (1) Skip section navigation (2)

Re: Help with date query

From: Matthew Walden <matthew(dot)walden(at)bcs(dot)org>
To: richard terry <rterry(at)pacific(dot)net(dot)au>
Cc: PostgreSQL - newbie <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Help with date query
Date: 2010-11-22 12:10:55
Message-ID: AANLkTim6Vh9rrWwKPC8Ux6ENN_Q+H7YD=UdmFYOLUZLr@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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
>

In response to

Responses

pgsql-novice by date

Next:From: Vaduvoiu TiberiuDate: 2010-11-22 12:59:34
Subject: Null values detected as 0 value
Previous:From: Leif Biberg KristensenDate: 2010-11-22 12:08:18
Subject: Re: function volatility

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group