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

Help with date query

From: richard terry <rterry(at)pacific(dot)net(dot)au>
To: "PostgreSQL - newbie" <pgsql-novice(at)postgresql(dot)org>
Subject: Help with date query
Date: 2010-11-22 03:26:59
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
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.



pgsql-novice by date

Next:From: bakkiyaDate: 2010-11-22 04:59:05
Subject: Re: Could not open relation with OID (table partitioning issue?)
Previous:From: Oliver CharlesDate: 2010-11-21 17:41:23
Subject: Re: Question - help needed

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