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

Re: Help with date query

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 (view raw or flat)
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
> 

In response to

pgsql-novice by date

Next:From: adminDate: 2010-11-22 22:20:54
Subject: pg_upgrade tablespace directory problem
Previous:From: Matthew WaldenDate: 2010-11-22 20:58:39
Subject: Re: Help with date query

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