Re: Performance question

On Mon, 10 Sep 2001, Tille, Andreas wrote:

> Hello,
>
> I have ported a database from MS SQL Server to PostgreSQL. The database has
> 40 tables and 117 indexes which I defined for the same fields as in MS SQL.
> I converted the data using some SQL output from MS SQL server and inserted
> it with psql.
>
> Now I started some performance comparisons and did the following statement:
>
> 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;
>
> (sorry for the German names used here).
>
> The MS-SQL server represents the result "immediately" - you just not notice
> any delay. If I do it on the PostgreSQL server it takes 30s on comparable
> hardware :-(((.
>
> I really have no idea what caused this big difference in speed which would
> forbid the use of PostgreSQL for our application. How can I checked
> whether the indexes I created are really used? What could be other
> reasons for such a big difference in speed?

Use explain. Explain tells you the query plan of the optimizer.

explain SELECT .....;

Best regards
Herbie
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Herbert Liechti http://www.thinx.ch
ThinX networked business services Adlergasse 5, CH-4500 Solothurn
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~