From: Jason Earl <jdearl@yahoo.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: Reiner Dassing <dassing@wettzell.ifag.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: Triggers do not fire
Date: Wed, 17 Oct 2001 11:20:51 -0700 (PDT)
Message-ID: <20011017182051.63377.qmail@web10006.mail.yahoo.com> (raw)
In-Reply-To: <27135.1003332861@sss.pgh.pa.us>
I can vouch for that. I have several tables with 10
to 16 million entries in much the same sort of setup
as you are describing (primary key, timestamp, value).
PostgreSQL is will quite happily use the timestamp
indexes when accessing this table, and it doesn't
default to a sequential scan until a considerable
number of tuples would be searched. For example:
processdata=> explain select count(*) from
caseweights1 where dt > '2001-10-11';
NOTICE: QUERY PLAN:
Aggregate (cost=255053.37..255053.37 rows=1 width=0)
-> Index Scan using caseweights1_dt_idx on
caseweights1 (cost=0.00..254827.01 rows=90544
width=0)
EXPLAIN
processdata=> select count(*) from caseweights1 where
dt > '2001-10-11';
count
--------
146773
(1 row)
processdata=> select count(*) from caseweights1;
count
----------
14984087
(1 row)
As you can see, even though my table is fairly large
PostgreSQL will happily use indexes for queries even
when there is a significant number of tuples that are
to be accessed. The count command with the index took
perhaps a second on my 400MHz 128M ram normal IDE hard
drive test server. The count of all the records, on
the other hand, triggered a sequential scan that took
a long time to complete.
In other words, chances are good that PostgreSQL will
handle your data without special modification.
Jason
--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Reiner Dassing <dassing@wettzell.ifag.de> writes:
> > I have a table which has a lot of entries (some x
> millions) of the kind
> > (id, timestamp, value)
> > The access (selects) is concentrated to the timely
> last some thousands
> > entries.
> > To adapt this fact I want to setup a "virtual"
> table - test in my
> > example - which
> > is accessed by the clients but in reality the
> entries are separated to
> > different small
> > tables. These table are dynamically created to
> hold the values
> > distinguished by years.
>
> Why bother? Seems like you are just making life
> complicated for
> yourself. One big table with a suitable index ought
> to work fine.
>
> regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
__________________________________________________
Do You Yahoo!?
Make a great connection at Yahoo! Personals.
http://personals.yahoo.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jdearl@yahoo.com, tgl@sss.pgh.pa.us, dassing@wettzell.ifag.de
Subject: Re: Triggers do not fire
In-Reply-To: <20011017182051.63377.qmail@web10006.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox