Performance Issue

[Taris]

Hello,

At the moment I am testing a little bit with PostgreSql 7.4.2.
Especially I want to find the "best" indexes for specific SQL-Statements.

Let me describe a very strange situation. I execute some SQL-Selects on a table.

First, the table is not index.
I enter a SELECT-statement that results in a query plan with a complexity of 98.43.

Now I create an index and vacuum-analyse the table and I repeat the same statment.
The complexity increases to 5140.96.

Ok, the index is a bad one and I drop it.
Again I vacuum-analyse the table and repeat the statement.
Suprisingly, the complexity remains at 5140.96.


I have no clue why PostgreSql behaves like this.
What can I do to reduce the complexity to 98.43 without dropping an re-creating the table?

Help would be very much appreciated, thank you!
Regards


P.S. Details

Step 1 - Table-Definition:

Column | Type | Modifiers
---------------+------------------+-----------
as_fondsid | numeric(20,0) |
as_portfolio | numeric(20,0) |
as_anteilart | numeric(20,0) |
as_anteilwert | double precision |



Step 2 - My SQL-Statement:

select as_fondsid from anlagestruktur_1 where as_portfolio = 4 and
as_anteilart in
(select aktienid from aktien where a_kurzname like 'B%');



Step 3 - Query-Plan

QUERY PLAN
------------------------------------------------------------------------------
Merge IN Join (cost=97.42..98.43 rows=1 width=18)
Merge Cond: ("outer".as_anteilart = "inner".aktienid)
-> Sort (cost=22.58..22.59 rows=6 width=36)
Sort Key: anlagestruktur_1.as_anteilart
-> Seq Scan on anlagestruktur_1 (cost=0.00..22.50 rows=6 width=36)
Filter: (as_portfolio = 4::numeric)
-> Sort (cost=74.85..75.33 rows=194 width=10)
Sort Key: aktien.aktienid
-> Seq Scan on aktien (cost=0.00..67.47 rows=194 width=10)
Filter: ((a_kurzname)::text ~~ 'B%'::text)
(10 rows)



Step 4 - New Index

create index anlagestruktur_1_i1 on anlagestruktur_1(as_portfolio);
vacuum analyse anlagestruktur_1;



Step 5 - Query-Plan

QUERY PLAN
------------------------------------------------------------------------------------
Merge IN Join (cost=4879.66..5057.25 rows=1769 width=10)
Merge Cond: ("outer".as_anteilart = "inner".aktienid)
-> Sort (cost=4804.81..4880.45 rows=30254 width=20)
Sort Key: anlagestruktur_1.as_anteilart
-> Seq Scan on anlagestruktur_1 (cost=0.00..2222.85 rows=30254 width=20)
Filter: (as_portfolio = 4::numeric)
-> Sort (cost=74.85..75.33 rows=194 width=10)
Sort Key: aktien.aktienid
-> Seq Scan on aktien (cost=0.00..67.47 rows=194 width=10)
Filter: ((a_kurzname)::text ~~ 'B%'::text)
(10 rows)



Step 6 - Dropping the index

drop index anlagestruktur_1_i1;
vacuum analyse anlagestruktur_1;



Step 6 - Query-Plan

QUERY PLAN
------------------------------------------------------------------------------------
Merge IN Join (cost=4874.11..5051.22 rows=1765 width=10)
Merge Cond: ("outer".as_anteilart = "inner".aktienid)
-> Sort (cost=4799.26..4874.72 rows=30186 width=20)
Sort Key: anlagestruktur_1.as_anteilart
-> Seq Scan on anlagestruktur_1 (cost=0.00..2222.85 rows=30186 width=20)
Filter: (as_portfolio = 4::numeric)
-> Sort (cost=74.85..75.33 rows=194 width=10)
Sort Key: aktien.aktienid
-> Seq Scan on aktien (cost=0.00..67.47 rows=194 width=10)
Filter: ((a_kurzname)::text ~~ 'B%'::text)
(10 rows)

 

[ceco]

Now I can't think of anything concrete on these numbers, but why don't you try join

instead of
select as_fondsid from anlagestruktur_1 where as_portfolio = 4 and
as_anteilart in
(select aktienid from aktien where a_kurzname like 'B%');

try

select t1.as_fondsid from anlagestruktur_1 AS t1, aktien AS t2 where t1.as_portfolio = 4 and
t1.as_anteilart = t2.aktienid AND t2.a_kurzname like 'B%';