Hello,

I discussed a problem concerning the speed of PostgreSQL compared to
MS SQL server heavily on postgres-general list.  The thread starts with
message

    http://fts.postgresql.org/db/mw/msg.html?mid=1035557

Now I tried a snapshot of version 7.2 and got an increase of speed of
about factor 2.  But sorry this is really not enough.  The very simple
test I pointed to in my mail is even much to slow and the issue would
probably spoil down the whole project which should be a complete open
source solution and would perhaps and in any M$ stuff.  Iï¿½ve got under
heavy preasur from my employer who was talking about the nice world
of MS .net (while he is using MS-SQL exclusively).  To make the thing
clear the issue is the gal database of infectious diseases in Germany
runned by the Robert Koch-Institute.  So the beast could be of some
importance for increasing the acceptance of PostgreSQL and Open Source
in the field of medicine which is generally known for the money which
is involved in.  So I really hope that some skilled programmers would
be able to find a good way to solve the performance issue perhaps by
just profiling the simple query

   SELECT Hauptdaten_Fall.MeldeKategorie, Count(Hauptdaten_Fall.ID) AS Anz FROM Hauptdaten_Fall WHERE (((Hauptdaten_Fall.IstAktuell)=20))
GROUP BY Hauptdaten_Fall.MeldeKategorie ORDER BY Hauptdaten_Fall.MeldeKategorie;

to the data set I put on

   http://www.foodborne-net.de/~tillea/ifsg/ifsgtest.dump.bz2

If this should take less than half a second on a modern PC I could
continue to try mo realistic queries.

I really hope that I could readjust the image of PostgreSQL in the
eyes of my M$-centered colleagues.

Kind regards

       Andreas.

Mon, 29 Oct 2001 13:43:37 +0100 (CET)
"Tille, Andreas" <TilleA@rki.de>
Serious performance problem
Seems that problem is very simple :))
MSSql can do queries from indexes, without using actual table at all.
Postgresql doesn't.

So mssql avoids sequental scanning of big table, and simply does scan of
index which is already in needed order and has very much less size.

On Mon, Oct 29, 2001 at 01:43:37PM +0100, Tille, Andreas wrote:
> Hello,
> 
> I discussed a problem concerning the speed of PostgreSQL compared to
> MS SQL server heavily on postgres-general list.  The thread starts with
> message
> 
>     http://fts.postgresql.org/db/mw/msg.html?mid=1035557
> 
> Now I tried a snapshot of version 7.2 and got an increase of speed of
> about factor 2.  But sorry this is really not enough.  The very simple
> test I pointed to in my mail is even much to slow and the issue would
> probably spoil down the whole project which should be a complete open
> source solution and would perhaps and in any M$ stuff.  I've got under
> heavy preasur from my employer who was talking about the nice world
> of MS .net (while he is using MS-SQL exclusively).  To make the thing
> clear the issue is the gal database of infectious diseases in Germany
> runned by the Robert Koch-Institute.  So the beast could be of some
> importance for increasing the acceptance of PostgreSQL and Open Source
> in the field of medicine which is generally known for the money which
> is involved in.  So I really hope that some skilled programmers would
> be able to find a good way to solve the performance issue perhaps by
> just profiling the simple query
> 
>    SELECT Hauptdaten_Fall.MeldeKategorie, Count(Hauptdaten_Fall.ID) AS Anz FROM Hauptdaten_Fall WHERE (((Hauptdaten_Fall.IstAktuell)=20))
> GROUP BY Hauptdaten_Fall.MeldeKategorie ORDER BY Hauptdaten_Fall.MeldeKategorie;
> 
> to the data set I put on
> 
>    http://www.foodborne-net.de/~tillea/ifsg/ifsgtest.dump.bz2
> 
> If this should take less than half a second on a modern PC I could
> continue to try mo realistic queries.
> 
> I really hope that I could readjust the image of PostgreSQL in the
> eyes of my M$-centered colleagues.
> 
> Kind regards
> 
>        Andreas.
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html

Mon, 29 Oct 2001 15:45:45 +0200
Vsevolod Lobko <seva@sevasoft.kiev.ua>
Re: Serious performance problem
Andreas - 
I took a look at your problem, since I'm sort of in the field,
and would liek to see free solutions spread, as well.

Here's what I see: Your example touches on what can be an achilles
heel for pgsql's current statistical analyzer: selection on data fields
that have a few common values. Often, the indices don't get used, since
a large fraction of the table needs to be scanned, in any case. In
your example, fully 68% of the table fits the where condition.

Here's some timing results on my machine:

Your dataset and query, as written:

real    0m25.272s
user    0m0.090s
sys     0m0.050s

Creating an index on  meldekategorie, and forcing it's use with
"set enable_seqscan = off"

real    0m14.743s
user    0m0.070s
sys     0m0.050s

Same, with index on istaktuell:

real    0m26.511s
user    0m0.050s
sys     0m0.060s

Now, with an index on both  meldekategorie and istaktuell:

real    0m7.179s
user    0m0.060s
sys     0m0.030s

I think we have a winner. No it's not sub-second, but I improved the time
by 3x just by trying some indices. Note that I _still_ had to force the
use of indices for this one. It's also the first time I've personally seen
a query/dataset that benefits this much from a two-key index.

As another poster replied to you, there is limitation with postgresql's
use of indices that arises from MVCC: even if the only data requested is
that stored in the index itself, the backend must visit the actual tuple
in the table to ensure that it is 'visible' to the current transaction.

How realistic a representation of your real workload is this query? Realize
that more selective, complex queries are where pgsql shines compared to
other RDBMS: the 'fast table scanner' type query that you proposed as your
test don't really let pgsql stretch it's legs. Do you have example timings
from MS-SQL or others?

Ross

On Mon, Oct 29, 2001 at 01:43:37PM +0100, Tille, Andreas wrote:
> Hello,
> 
> I discussed a problem concerning the speed of PostgreSQL compared to
> MS SQL server heavily on postgres-general list.  The thread starts with
> message
> 
>     http://fts.postgresql.org/db/mw/msg.html?mid=1035557
> 
> Now I tried a snapshot of version 7.2 and got an increase of speed of
> about factor 2.  But sorry this is really not enough.  The very simple
> test I pointed to in my mail is even much to slow and the issue would
> probably spoil down the whole project which should be a complete open
> source solution and would perhaps and in any M$ stuff.  I?ve got under
> heavy preasur from my employer who was talking about the nice world
> of MS .net (while he is using MS-SQL exclusively).  To make the thing
> clear the issue is the gal database of infectious diseases in Germany
> runned by the Robert Koch-Institute.  So the beast could be of some
> importance for increasing the acceptance of PostgreSQL and Open Source
> in the field of medicine which is generally known for the money which
> is involved in.  So I really hope that some skilled programmers would
> be able to find a good way to solve the performance issue perhaps by
> just profiling the simple query
> 
>    SELECT Hauptdaten_Fall.MeldeKategorie, Count(Hauptdaten_Fall.ID) AS Anz FROM Hauptdaten_Fall WHERE (((Hauptdaten_Fall.IstAktuell)=20))
> GROUP BY Hauptdaten_Fall.MeldeKategorie ORDER BY Hauptdaten_Fall.MeldeKategorie;
> 
> to the data set I put on
> 
>    http://www.foodborne-net.de/~tillea/ifsg/ifsgtest.dump.bz2
> 
> If this should take less than half a second on a modern PC I could
> continue to try mo realistic queries.
> 
> I really hope that I could readjust the image of PostgreSQL in the
> eyes of my M$-centered colleagues.
> 
> Kind regards
> 
>        Andreas.
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html

-- 
Ross Reedstrom, Ph.D.                                 reedstrm@rice.edu
Executive Director                                  phone: 713-348-6166
Gulf Coast Consortium for Bioinformatics              fax: 713-348-6182
Rice University MS-39
Houston, TX 77005

Mon, 29 Oct 2001 11:31:54 -0600
"Ross J. Reedstrom" <reedstrm@rice.edu>
Re: Serious performance problem
On Mon, 29 Oct 2001, Ross J. Reedstrom wrote:

> Here's what I see: Your example touches on what can be an achilles
> heel for pgsql's current statistical analyzer: selection on data fields
> that have a few common values. Often, the indices don't get used, since
> a large fraction of the table needs to be scanned, in any case. In
> your example, fully 68% of the table fits the where condition.
>  ...
>
> I think we have a winner. No it's not sub-second, but I improved the time
> by 3x just by trying some indices. Note that I _still_ had to force the
> use of indices for this one. It's also the first time I've personally seen
> a query/dataset that benefits this much from a two-key index.
This is true for this example and I also played with indices as you.  I also
enforced the index scan and compared with forbidding the index scan.  The
result was on my more realistic examples that both versions performed quite
the same.  There was no *real* difference. For sure in this simple query there
is a difference but the real examples showed only 2% - 5% speed increase
(if not slower with enforcing index scans!).

> As another poster replied to you, there is limitation with postgresql's
> use of indices that arises from MVCC: even if the only data requested is
> that stored in the index itself, the backend must visit the actual tuple
> in the table to ensure that it is 'visible' to the current transaction.
Any possibility to switch of this temporarily for certain queries like this
if the programmer could make sure that it is not necessary?  Just a stupid
idea from a bloody uneducated man in database-engeniering.

> How realistic a representation of your real workload is this query? Realize
> that more selective, complex queries are where pgsql shines compared to
> other RDBMS: the 'fast table scanner' type query that you proposed as your
> test don't really let pgsql stretch it's legs. Do you have example timings
> from MS-SQL or others?
Unfortunately the four test we did here seemed all to suffer from the
same problem.  The situation is that there is a given database structure
which was developed over more than a year on MS-SQL and has a Access GUI.
Now parts of the UI should be made public via web (I want to use Zope)
and I just imported the data and did some example queries with the
terrible slow result.

Kind regards and thanks for your ideas

          Andreas.

Tue, 30 Oct 2001 10:55:50 +0100 (CET)
"Tille, Andreas" <TilleA@rki.de>
Re: Serious performance problem
On Mon, 29 Oct 2001, Vsevolod Lobko wrote:

> Seems that problem is very simple :))
> MSSql can do queries from indexes, without using actual table at all.
> Postgresql doesn't.
>
> So mssql avoids sequental scanning of big table, and simply does scan of
> index which is already in needed order and has very much less size.
Hmmm, could anyone imagine a simple or not *solution* of the Problem.
Iï¿½m thinking of some switch the database programmer could use if he
really knows what he is doing.

Kind regards

        Andreas.

Tue, 30 Oct 2001 10:59:10 +0100 (CET)
"Tille, Andreas" <TilleA@rki.de>
Re: Serious performance problem
"Tille, Andreas" wrote:
> 
> On Mon, 29 Oct 2001, Ross J. Reedstrom wrote:
> 
> > Here's what I see: Your example touches on what can be an achilles
> > heel for pgsql's current statistical analyzer: selection on data fields
> > that have a few common values. Often, the indices don't get used, since
> > a large fraction of the table needs to be scanned, in any case. In
> > your example, fully 68% of the table fits the where condition.
> >  ...
> >
> > I think we have a winner. No it's not sub-second, but I improved the time
> > by 3x just by trying some indices. Note that I _still_ had to force the
> > use of indices for this one. It's also the first time I've personally seen
> > a query/dataset that benefits this much from a two-key index.
> This is true for this example and I also played with indices as you.  I also
> enforced the index scan and compared with forbidding the index scan.  The
> result was on my more realistic examples that both versions performed quite
> the same.  There was no *real* difference. For sure in this simple query there
> is a difference but the real examples showed only 2% - 5% speed increase
> (if not slower with enforcing index scans!).

I studied his dataset and found that a simple count(*) on whole table 
took 1.3 sec on my Celeron 375 so I'm sure that the more complex query, 
which has to visit 2/3 of tuples will not be able to execute under 1 sec

My playing with indexes / subqueries and query rewriting got the example 
query (actually a functional equivalent) to run in ~5 sec with simple 
aggregate(group(indexscan))) plan and I suspect that this is how fast 
it will be on my hardware

It could probably be soon possible to make it run in ~ 1.5 by using an
aggregate 
function that does a sequential scan and returns a rowset.

> > As another poster replied to you, there is limitation with postgresql's
> > use of indices that arises from MVCC: even if the only data requested is
> > that stored in the index itself, the backend must visit the actual tuple
> > in the table to ensure that it is 'visible' to the current transaction.
> Any possibility to switch of this temporarily for certain queries like this
> if the programmer could make sure that it is not necessary?  Just a stupid
> idea from a bloody uneducated man in database-engeniering.

There have been plans to set aside a bit in index that would mark the
deleted 
tuple. Unfortunately this helps only in cases when there are many
deleted tuples
and all live tuples have to be checked anyway ;(

--------------
Hannu

Tue, 30 Oct 2001 12:20:57 +0200
Hannu Krosing <hannu@tm.ee>
Re: Serious performance problem
"Tille, Andreas" wrote:
> 
> Hello,
> 
> I discussed a problem concerning the speed of PostgreSQL compared to
> MS SQL server heavily on postgres-general list.  The thread starts with
> message
> 
>     http://fts.postgresql.org/db/mw/msg.html?mid=1035557
> 
> Now I tried a snapshot of version 7.2 and got an increase of speed of
> about factor 2.  But sorry this is really not enough.  The very simple
> test I pointed to in my mail is even much to slow and the issue would
> probably spoil down the whole project which should be a complete open
> source solution and would perhaps and in any M$ stuff.  Iï¿½ve got under
> heavy preasur from my employer who was talking about the nice world
> of MS .net (while he is using MS-SQL exclusively).  To make the thing
> clear the issue is the gal database of infectious diseases in Germany
> runned by the Robert Koch-Institute.  So the beast could be of some
> importance for increasing the acceptance of PostgreSQL and Open Source
> in the field of medicine which is generally known for the money which
> is involved in.  So I really hope that some skilled programmers would
> be able to find a good way to solve the performance issue perhaps by
> just profiling the simple query
> 
>    SELECT Hauptdaten_Fall.MeldeKategorie, Count(Hauptdaten_Fall.ID) AS Anz FROM Hauptdaten_Fall WHERE (((Hauptdaten_Fall.IstAktuell)=20))
> GROUP BY Hauptdaten_Fall.MeldeKategorie ORDER BY Hauptdaten_Fall.MeldeKategorie;
> 
> to the data set I put on
> 
>    http://www.foodborne-net.de/~tillea/ifsg/ifsgtest.dump.bz2
> 
> If this should take less than half a second on a modern PC I could
> continue to try mo realistic queries.


I tried some more on optimizing the query on my work computer 
(AMD ATHLON 850, 512MB, PostgreSQL 7.1.3 with default memory settings)


SELECT MeldeKategorie, 
       Count(ID) AS Anz
  FROM Hauptdaten_Fall 
 WHERE IstAktuell=20
GROUP BY MeldeKategorie 
ORDER BY MeldeKategorie;

real    0m9.675s

create index i1 on Hauptdaten_Fall(IstAktuell,MeldeKategorie);

----------------------------
set enable_seqscan = off;
SELECT MeldeKategorie, 
       Count(ID) AS Anz
  FROM Hauptdaten_Fall 
 WHERE IstAktuell=20
GROUP BY MeldeKategorie 
ORDER BY MeldeKategorie;

Aggregate  (cost=4497.30..4510.18 rows=258 width=16)
  ->  Group  (cost=4497.30..4503.74 rows=2575 width=16)
        ->  Sort  (cost=4497.30..4497.30 rows=2575 width=16)
              ->  Index Scan using i1 on hauptdaten_fall 
(cost=0.00..4351.40 rows=2575 width=16)

real    0m7.131s

---------------------------

set enable_seqscan = off;
SELECT MeldeKategorie, 
       Count(ID) AS Anz
  FROM Hauptdaten_Fall 
 WHERE IstAktuell=20
GROUP BY IstAktuell,MeldeKategorie
ORDER BY IstAktuell,MeldeKategorie;

Aggregate  (cost=4497.30..4510.18 rows=258 width=16)
  ->  Group  (cost=4497.30..4503.74 rows=2575 width=16)
        ->  Index Scan using i1 on hauptdaten_fall  (cost=0.00..4351.40
rows=2575 width=16)

real    0m3.223s

-- same after doing

cluster i1 on Hauptdaten_Fall;

real    1.590 -- 1.600



select count(*) from Hauptdaten_Fall;

real    0m0.630s

---------------------------

The following query is marginally (about 0.1 sec) faster, though the 
plan looks the same down to cost estimates.

SET ENABLE_SEQSCAN = OFF;
SELECT MeldeKategorie, 
       Count(*) AS Anz
  FROM (select IstAktuell,MeldeKategorie from Hauptdaten_Fall where
IstAktuell=20) sub
GROUP BY IstAktuell,MeldeKategorie
ORDER BY IstAktuell,MeldeKategorie;

Aggregate  (cost=0.00..4370.72 rows=258 width=16)
  ->  Group  (cost=0.00..4364.28 rows=2575 width=16)
        ->  Index Scan using i1 on hauptdaten_fall  (cost=0.00..4351.40
rows=2575 width=16)

real    0m1.438s - 1.506s

---------------------------

now I make the dataset bigger keeping the number of rows returned by
query the same

insert into hauptdaten_fall (istaktuell, meldekategorie)
select istaktuell + 20, meldekategorie 
from  hauptdaten_fall ;

INSERT 0 257530

insert into hauptdaten_fall (istaktuell, meldekategorie)
select istaktuell + 40, meldekategorie 
from  hauptdaten_fall ;

INSERT 0 515060
ifsgtest=# select count(*) from hauptdaten_fall;
  count  
---------
 1030120
(1 row)

cluster i1 on Hauptdaten_Fall;
vacuum analyze;


-- The query time is still the same 1.44 - 1.5 sec

SET ENABLE_SEQSCAN = OFF;
SELECT MeldeKategorie, 
       Count(*) AS Anz
  FROM (select IstAktuell,MeldeKategorie from Hauptdaten_Fall where
IstAktuell=20) sub
GROUP BY IstAktuell,MeldeKategorie
ORDER BY IstAktuell,MeldeKategorie;

Aggregate  (cost=0.00..4370.72 rows=258 width=16)
  ->  Group  (cost=0.00..4364.28 rows=2575 width=16)
        ->  Index Scan using i1 on hauptdaten_fall  (cost=0.00..4351.40
rows=2575 width=16)

real    0m1.438s - 1.506s

----------------------------

now back to original data distribution, just 4 times bigger

ifsgtest=# update hauptdaten_fall
ifsgtest-# set istaktuell = case when istaktuell % 20 = 0 then 20 else
10 end
ifsgtest-# ;
UPDATE 1030120
ifsgtest=# vacuum analyze;
VACUUM

SET ENABLE_SEQSCAN = OFF;
SELECT MeldeKategorie, 
       Count(*) AS Anz
  FROM (select IstAktuell,MeldeKategorie from Hauptdaten_Fall where
IstAktuell=20) sub
GROUP BY IstAktuell,MeldeKategorie
ORDER BY IstAktuell,MeldeKategorie;

real    0m6.077 -- 6.606s

and after clustering:
cluster i1 on Hauptdaten_Fall;

real    0m5.683 - 5.750s

so it's linear growth here

----------------------------

Hannu

Wed, 31 Oct 2001 12:25:00 +0200
Hannu Krosing <hannu@tm.ee>
Re: Serious performance problem
I have been thinking about this query, I downloaded all your info and I read
your reply to a previous post. 

At issue, you say MSSQL outperforms PGSQL, this may be true for a finite set of
query types, it may even be true for your entire application, but for how long?
What will be the nature of your data next year? 

Your query is a prime example of where application optimization needs to
happen. Regardless if MSSQL can currently execute that query quickly, at some
point there will be a volume of data which is too large to process quickly.
This table, you say, is created periodically with a cron job. How hard would it
be to append a couple SQL statements to create a summary table for the high
speed queries?

Personally, I think your approach needs to be modified a bit. The fact that
your query runs well on one SQL database and poorly on another indicates to me
that you will be tied to one database forever. If you use standard database
optimization techniques in your design, you can choose any database by the
whole of important criteria, such as reliability, speed, support,
administration, and price, rather than just speed.

Also, if you use MSSQL you will need to have some version of MS-Windows on
which to run it, that alone indicates to me you will have reliability problems.

"Tille, Andreas" wrote:
> 
> Hello,
> 
> I discussed a problem concerning the speed of PostgreSQL compared to
> MS SQL server heavily on postgres-general list.  The thread starts with
> message
> 
>     http://fts.postgresql.org/db/mw/msg.html?mid=1035557
> 
> Now I tried a snapshot of version 7.2 and got an increase of speed of
> about factor 2.  But sorry this is really not enough.  The very simple
> test I pointed to in my mail is even much to slow and the issue would
> probably spoil down the whole project which should be a complete open
> source solution and would perhaps and in any M$ stuff.  Iï¿½ve got under
> heavy preasur from my employer who was talking about the nice world
> of MS .net (while he is using MS-SQL exclusively).  To make the thing
> clear the issue is the gal database of infectious diseases in Germany
> runned by the Robert Koch-Institute.  So the beast could be of some
> importance for increasing the acceptance of PostgreSQL and Open Source
> in the field of medicine which is generally known for the money which
> is involved in.  So I really hope that some skilled programmers would
> be able to find a good way to solve the performance issue perhaps by
> just profiling the simple query
> 
>    SELECT Hauptdaten_Fall.MeldeKategorie, Count(Hauptdaten_Fall.ID) AS Anz FROM Hauptdaten_Fall WHERE (((Hauptdaten_Fall.IstAktuell)=20))
> GROUP BY Hauptdaten_Fall.MeldeKategorie ORDER BY Hauptdaten_Fall.MeldeKategorie;
> 
> to the data set I put on
> 
>    http://www.foodborne-net.de/~tillea/ifsg/ifsgtest.dump.bz2
> 
> If this should take less than half a second on a modern PC I could
> continue to try mo realistic queries.
> 
> I really hope that I could readjust the image of PostgreSQL in the
> eyes of my M$-centered colleagues.
> 
> Kind regards
> 
>        Andreas.
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html

-- 
5-4-3-2-1 Thunderbirds are GO!
------------------------
http://www.mohawksoft.com

Wed, 31 Oct 2001 08:14:02 -0500
mlw <markw@mohawksoft.com>
Re: Serious performance problem
On Wed, 31 Oct 2001, Hannu Krosing wrote:

> I tried some more on optimizing the query on my work computer
> (AMD ATHLON 850, 512MB, PostgreSQL 7.1.3 with default memory settings)
>
>
> SELECT MeldeKategorie,
>        Count(ID) AS Anz
>   FROM Hauptdaten_Fall
>  WHERE IstAktuell=20
> GROUP BY MeldeKategorie
> ORDER BY MeldeKategorie;
>
> real    0m9.675s
>
> create index i1 on Hauptdaten_Fall(IstAktuell,MeldeKategorie);
>
> ----------------------------
> set enable_seqscan = off;
> SELECT MeldeKategorie,
>        Count(ID) AS Anz
>   FROM Hauptdaten_Fall
>  WHERE IstAktuell=20
> GROUP BY MeldeKategorie
> ORDER BY MeldeKategorie;
>
> Aggregate  (cost=4497.30..4510.18 rows=258 width=16)
>   ->  Group  (cost=4497.30..4503.74 rows=2575 width=16)
>         ->  Sort  (cost=4497.30..4497.30 rows=2575 width=16)
>               ->  Index Scan using i1 on hauptdaten_fall
> (cost=0.00..4351.40 rows=2575 width=16)
>
> real    0m7.131s
>
> ---------------------------
>
> set enable_seqscan = off;
> SELECT MeldeKategorie,
>        Count(ID) AS Anz
>   FROM Hauptdaten_Fall
>  WHERE IstAktuell=20
> GROUP BY IstAktuell,MeldeKategorie
> ORDER BY IstAktuell,MeldeKategorie;
>
> Aggregate  (cost=4497.30..4510.18 rows=258 width=16)
>   ->  Group  (cost=4497.30..4503.74 rows=2575 width=16)
>         ->  Index Scan using i1 on hauptdaten_fall  (cost=0.00..4351.40
> rows=2575 width=16)
>
> real    0m3.223s
Hmmm, could you please explain the theory behind that for quite a
beginner like me (perhaps on -general if you feel it apropriate)

The change in the second select is that you included IstAktuell in the
GROUP BY/ORDER BY clause and this gives a speed increas by factor 2.
It seems that the  "Sort" can be left out in this case if I look at the
plan, but why that?  The WHERE clause should select just all IstAktuell=20
data sets and so the GROUP BY/ORDER BY clauses should every time have the
same work - as for my humble understanding.

>
> -- same after doing
>
> cluster i1 on Hauptdaten_Fall;
>
> real    1.590 -- 1.600
Thatï¿½s also interesting.  In reality the table Hauptdaten_Fall has many fields
with many indices.  If I understand things right it makes no sense to have
more than one clustered index, right? A further speed increase of factor two
would be welcome.  Could I expect this if I would find out the "sensitive"
index of my table for certain tasks?  Or is my understanging wrong and it
makes sense to cluster more than one index.  Unfortunately clustering the
index of a huge table takes some time.  Could I speed this up by some
tricks?

> select count(*) from Hauptdaten_Fall;
>
> real    0m0.630s
>
> ---------------------------
>
> The following query is marginally (about 0.1 sec) faster, though the
> plan looks the same down to cost estimates.
>
> SET ENABLE_SEQSCAN = OFF;
> SELECT MeldeKategorie,
>        Count(*) AS Anz
>   FROM (select IstAktuell,MeldeKategorie from Hauptdaten_Fall where
> IstAktuell=20) sub
> GROUP BY IstAktuell,MeldeKategorie
> ORDER BY IstAktuell,MeldeKategorie;
>
> Aggregate  (cost=0.00..4370.72 rows=258 width=16)
>   ->  Group  (cost=0.00..4364.28 rows=2575 width=16)
>         ->  Index Scan using i1 on hauptdaten_fall  (cost=0.00..4351.40
> rows=2575 width=16)
>
> real    0m1.438s - 1.506s
Hmm, perhaps this is nearly nothing or is there any theory that a
count(*) is faster than a count(<fieldname>)?

> ...
> real    0m6.077 -- 6.606s
>
> and after clustering:
> cluster i1 on Hauptdaten_Fall;
>
> real    0m5.683 - 5.750s
>
> so it's linear growth here
This is what my colleague was afraid of: We would have linear growth
compared to the log(n) growth which is to be expected on MS SQL server
(for this certain type of queries and for sure up to a far limit of
data where other constraints could get influence, but we are far from
this limit).  This would not convince him :-(.

Kind regards

         Andreas.

Thu, 1 Nov 2001 16:02:16 +0100 (CET)
"Tille, Andreas" <TilleA@rki.de>
Re: Serious performance problem