Ummm ... not quite. In MSSQL/Sybase/Oracle, a clustered index maintains
its space saturation as part of each update operation. High activity
does indeed result in less-full pages (typically 60-80% full for tables
with heavy deletions or rowsize changes). To bring the percentage back
up, you run
I think you've probably fingered the kicker of why PG doesn't have this
kind of clustering already. Hence perhaps the need for other approaches
to the issue (the disk-IO efficiency of reading groups of rows related
by a common key) that other DB's (with in-place update) address with
synchronous c
This discussion is starting to sound like the split in HEAP memory
management evolution, into garbage-collecting (e.g. Java) and
non-garbage-collecting (e.g. C++).
Reclamation by GC's these days has become seriously sophisticated.
CLUSTER resembles the first generation of GC's, which were
singl
Sheer nitpick here...
A B-tree is where the records (data) live at all levels of the tree;
B+ tree is where the records are only at the leaf level.
That's what Knuth calls them, anyway.
Clustered indexes for all known dbs are true B+ trees.
Nonclustered indexes could be B-trees (probably aren't),
s
The
query:
select count(*) from billing where timestamp >
now()-60
should obviously use
the index
CREATE INDEX billing_timestamp_idx ON billing USING btree ("timestamp" timestamp_ops);
on a table with
140 rows.
But it uses a Seq
Scan. If I set enable_seqscan=no, it indicates a
J. Andrew Rogers wrote:
On Thu, 2004-08-26 at 12:30, Magnus Hagander wrote:
IIRC, SQL Server always creates clustered indexes by default for primary
keys.
That would surprise me actually.
Yaz, it should. It doesn't ALWAYS create clustered (unique) index for
primary keys, but clustered is the defau
Hi, I have just installed 8.0.0beta1 and I noticed
that some query are slower than 7.4.2 queries.
After a FULL VACUUM ANALYZE
***With 7.4.2***
explain analyze SELECT count(*) FROM "SNS_DATA"
WHERE "Data_Arrivo_Campione" BETWEEN '2004-01-01 00:00:00' AND '2004-01-31
23:59:59' AND "Cod_Pa
Strangely enough, I don't find that result surprising.
if the vast bulk of the data is in the past and now()-60 represents a very small slice
of the data
we might expect that using an index is optimal, but there could be many reasons why it
doesn't get
used.
AFAIK postgres doesn't peek at valu
On Fri, 2004-08-27 at 11:12, Jack Kerkhof wrote:
> The query:
>
> select count(*) from billing where timestamp > now()-60
>
> should obviously use the index
>
> CREATE INDEX billing_timestamp_idx ON billing USING btree
> ("timestamp" timestamp_ops);
>
> on a table with 140 rows.
>
On Sun, Aug 29, 2004 at 11:04:48AM -0700, Mr Pink wrote:
> Another is that if the condition data types don't match then an indes won't be used
> you could try:
>
> select count(*) from billing where timestamp > (now()-60)::timestamp
In fact, I've had success with code like
select count(*)
Mishca,
> Ummm ... not quite. In MSSQL/Sybase/Oracle, a clustered index maintains
> its space saturation as part of each update operation. High activity
> does indeed result in less-full pages (typically 60-80% full for tables
> with heavy deletions or rowsize changes). To bring the percentage bac
Mr Pink <[EMAIL PROTECTED]> writes:
> AFAIK postgres doesn't peek at values used in a query when optimizing
Of course it does.
However sometimes things don't work perfectly.
To get good answers rather than just guesses we'll need two things:
. What version of postgres are you using.
. The out
"Scott Marlowe" <[EMAIL PROTECTED]> writes:
> Also, count(*) is likely to always generate a seq scan due to the way
> aggregates are implemented currently in pgsql. you might want to try:
Huh? I'm curious to know what you're talking about here.
> select somefield from sometable where timestamp
On Sun, 2004-08-29 at 15:12, Greg Stark wrote:
> "Scott Marlowe" <[EMAIL PROTECTED]> writes:
>
> > Also, count(*) is likely to always generate a seq scan due to the way
> > aggregates are implemented currently in pgsql. you might want to try:
>
> Huh? I'm curious to know what you're talking abou
On Sun, 2004-08-29 at 15:38, Scott Marlowe wrote:
> On Sun, 2004-08-29 at 15:12, Greg Stark wrote:
> > "Scott Marlowe" <[EMAIL PROTECTED]> writes:
> >
> > > Also, count(*) is likely to always generate a seq scan due to the way
> > > aggregates are implemented currently in pgsql. you might want to
>> select somefield from sometable where timestampfield > now()-'60
>> seconds'::interval
This is a FAQ, but since the archives don't seem to be up at the moment,
here's the answer once again:
The expression "now() - something" is not a constant, so the planner
is faced with "timestampfield > unk
On Sun, Aug 29, 2004 at 03:38:00PM -0600, Scott Marlowe wrote:
>>> select somefield from sometable where timestampfield > now()-'60
>>> seconds'::interval
>>>
>>> and count the number of returned rows. If there's a lot, it won't be
>>> any faster, if there's a few, it should be a win.
>> Why woul
"Scott Marlowe" <[EMAIL PROTECTED]> writes:
> PostgreSQL has a "generic" aggregate method. Imagine instead doing a
> select count(id1+id2-id3) from table where ... In that instance, it's
> not a simple shortcut to just grab the number of rows anymore. Since
> PostgreSQL uses a generic aggregat
> People expect count(*) _without a where clause_ to be cached in a single
> global variable. Postgres can't do this, but the reason has everything to do
Someone should write an approx_count('table') function that reads
reltuples from pg_class and tell them to use it in combination with
autovac.
Hi,
I assume you have reposted because you have just signed up to the list.
If this is the case, can you please read the archives or replies to your original post
about this question.
It did make it onto the archives and myself and others did reply with a few ideas and
questions.
If you could
--- Greg Stark <[EMAIL PROTECTED]> wrote:
>
> Mr Pink <[EMAIL PROTECTED]> writes:
>
> > AFAIK postgres doesn't peek at values used in a query when optimizing
>
> Of course it does.
But not ones returned by a function such as now(), or when you use bind variables, as
Tom aptly
explained.
Th
Mr Pink <[EMAIL PROTECTED]> writes:
>>> AFAIK postgres doesn't peek at values used in a query when optimizing
>>
>> Of course it does.
> But not ones returned by a function such as now(), or when you use
> bind variables, as Tom aptly explained.
FWIW, 8.0 does have the ability to use the values
22 matches
Mail list logo
