Thread: Performance problem

Performance problem

From
Andreas Rieke
Date:
Hi there,

maybe anyone can help me with the following problem.
Using PostgreSQL 7.03 on a Suse 6.3 Linux (kernel 2.2.13) with a P3/550
MHz,
I get a performance problem with a select command which takes up to 10
seconds.
The following tables are involved in this command:

CREATE TABLE "property_list" (
 "number" float8,
 "level_1_de" text,
 "level_1_en" text,
 "level_2_de" text,
 "level_2_en" text,

...

);

CREATE TABLE "properties" (
 "material_oid" oid NOT NULL,
 "property_list_oid" oid NOT NULL,

...

);

CREATE TABLE "materials" (
 "user_oid" oid NOT NULL,
 "material_class" oid,
 "is_new" bool DEFAULT 't'::bool,

...

);

and the command is

SELECT DISTINCT level_1_de from property_list pl, properties p,
materials m where m.oid=p.material_oid and p.property_list_oid=pl.oid
and m.is_new=FALSE;

The command returns about 10 items.
The tables mentioned contain the following number of entries:

materials: 2476
properties: 30 323
property_list: 349


I hope that using INDEXes should speed up the search, but although I
made
several attempts, the search is too slow. Since I am not an expert on
databases and PostgreSQL, maybe somebody who had similar problems is
able to help me.
Also, I do not know whether the DISTINCT in the SELECT statement forces
the database to
use algorithms which have not been optimized yet.

Thanks in advance for your help,

kind regards,

Andreas


Re: Performance problem

From
Stephan Szabo
Date:
Have you done a vacuum analyze on the database?  And what
does explain show for the query?

On Wed, 24 Jan 2001, Andreas Rieke wrote:

>
> SELECT DISTINCT level_1_de from property_list pl, properties p,
> materials m where m.oid=p.material_oid and p.property_list_oid=pl.oid
> and m.is_new=FALSE;
>
> The command returns about 10 items.
> The tables mentioned contain the following number of entries:
>
> materials: 2476
> properties: 30 323
> property_list: 349
>
>
> I hope that using INDEXes should speed up the search, but although I
> made
> several attempts, the search is too slow. Since I am not an expert on
> databases and PostgreSQL, maybe somebody who had similar problems is
> able to help me.
> Also, I do not know whether the DISTINCT in the SELECT statement forces
> the database to
> use algorithms which have not been optimized yet.
>
> Thanks in advance for your help,
>
> kind regards,
>
> Andreas
>