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
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
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
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),
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
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_Par"
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
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.
But it uses
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 back
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
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 about here.
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 try:
14 matches
Mail list logo