Thread: Most efficient way of selecting by date?

Most efficient way of selecting by date?

From
Simon Willison
Date:
Hi all,

I have a table which includes a field for the date and time an item was
added to it. What's the most efficient way of selecting all of the items
added on a specific date (this is a really basic question but I'm new to
Postgres)? Also, would it be worth putting an index on the date column
as most of my selects will be using it?

Thanks,

Simon


Re: Most efficient way of selecting by date?

From
Bruno Wolff III
Date:
On Tue, Sep 09, 2003 at 21:30:31 +0100,
  Simon Willison <cs1spw@bath.ac.uk> wrote:
>
> I have a table which includes a field for the date and time an item was
> added to it. What's the most efficient way of selecting all of the items
> added on a specific date (this is a really basic question but I'm new to
> Postgres)? Also, would it be worth putting an index on the date column
> as most of my selects will be using it?

Probably by using a range. Something like:
select whatever from wherever
  where added < '2003-01-03' and added >= '2003-01-02';

Re: Most efficient way of selecting by date?

From
Ron Johnson
Date:
On Tue, 2003-09-09 at 15:30, Simon Willison wrote:
> Hi all,
>
> I have a table which includes a field for the date and time an item was
> added to it. What's the most efficient way of selecting all of the items
> added on a specific date (this is a really basic question but I'm new to
> Postgres)? Also, would it be worth putting an index on the date column
> as most of my selects will be using it?

If you need to record the date and time a record was inserted, but
will usually be querying by date, how about 2 fields?

CREATE TABLE foo (
<blah>
INSERT_DATE    DATE,
INSERT_TIME    TIME );

CREATE INDEX foo_insdt_idx ON foo (INSERT_DATE, INSERT_TIME);

Then, this will go pretty fast:
SELECT * FROM foo WHERE INSERT_DATE = '2003-09-11';

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA

4 degrees from Vladimir Putin


FAQ: missing answers

From
"M. Bastin"
Date:
Hi,

I'd like to know 2 things that should be added to the FAQ:

   How many tables can you create in a database?

   How many databases can you create on a single PostgreSQL server?

A potential customer asked me this and I didn't know the answers.

Thanks,

Marc

Re: FAQ: missing answers

From
Oliver Elphick
Date:
On Fri, 2003-09-12 at 11:05, M. Bastin wrote:
> Hi,
>
> I'd like to know 2 things that should be added to the FAQ:
>
>    How many tables can you create in a database?
>
>    How many databases can you create on a single PostgreSQL server?

I think that to both questions the answer is that there is no practical
limit within the database.  A table takes up some rows in system tables,
and it creates files on disk for its data and its indexes.  A database
creates a sub-directory containing its tables.  Since tables are able to
hold many millions of rows, I cannot imagine there could be any
practical limit there.

You will run out of inodes or disk space long before you run out of
database resources.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Let the wicked forsake his way, and the unrighteous
      man his thoughts; and let him return unto the LORD,
      and He will have mercy upon him; and to our God, for
      he will abundantly pardon."          Isaiah 55:7