Re: Performance question (stripped down the problem)

On Sat, 22 Sep 2001, Mark kirkwood wrote:

> Interesting problem
:)  Hope we can find also an interesting solution ;-).

> takes 19 s on my old hardware ( P2 333Mhz + 384Mb + 2x20Gb ATA)
>
> I can get some improvement by making sort_mem=20480 :
>
> The query then takes 8s - still a bit slow  - Mysql does it in 2 s  :-(
> Increasing sort_mem>20480 did not give any further improvement.
  sort_mem = 65536
This gave no further improvement over sort_mem = 32768 and my box
takes about 5s for this task, which is more than one order of magnitude
higher than M$-SQL server.

> As noted by others it appears that data access is not the issue - as the
> query :
>
> SELECT
>   count(hauptdaten_fall.id) AS Anz
> FROM hauptdaten_fall
> WHERE (((hauptdaten_fall.istaktuell)=20))
>
> takes only 2 s - so I am guessing that the 8s result is about as good as can
> be gotten without delving into the PG code for GROUP BY access.
I did the same thing some days ago.  Even if the GROUP BY is detected as
the reason for the biggest part of slowing down - the M$-SQL server solves
the whole task faster than PostgreSQL is doing this simple select.

> A slightly complex workaround for better performance is to use a summary
> table  :
>
> CREATE TABLE hauptdaten_fall_sum(meldekategorie varchar(10),
>                                                      istaktuell integer,
>                                                      cnt integer)
>
> and maintain it via triggers on hauptdaten_fall
>
> The offending query then becomes :
>
> SELECT meldekategorie,cnt
> FROM hauptdaten_fall_sum
> WHERE istaktuell=20;
>
> which is unmeasurably fast ( i.e 0 s ) on my system.
Perhaps I have to think about such workarounds.  The problem is that this
was just the simplest example of several much more complex ones.  This would
mean I have to rework each query and have to do several speed tests.  This
makes the port very hard and perhaps the intended replication setup
impossible.

Kind regards

       Andreas.