Need to make query more efficient

[peter_budo]

I have this long winded query

select i.id,i.name, i.item_type_id, 
(select it.name from items it, relations rel, relation_types relType
 where it.id=rel.to_id and rel.from_id=i.id and rel.relation_type_id=relType.id and relType.name='CityCtountry') 
 as cityCountry from items i where i.item_type_id=2;

Cut the long story short, we inherited database from customer that hasn't been happy with other company, but so far they refuse to let as redesign it so we have to deal with what we have.
At the moment there is some 8k entries and about half of them are cities that I need to get from DB including a name of country they belong to. Above query takes some 22sec to accomplish this.

Anyone up to challenge of making it more efficient? (DB redesign is out of question as customer refused :'( )

[tesuji]

Well, some 8k entries and excecuting that select statement would last about 22 sec ? sounds really kind of unreal.

So which database engine? Are there any primary keys and foreign keys, or indexes defined on tables: items it, relations rel, relation_types relType?

If NOT, access time is proportional to the cardinality of numbers of rows (NOR) of table items times NOR of table relationals times NOR of table relation_types, what could be a very large number. You may use count(*) to compute the cardinality of those inner joins.

Therefore, properly defined primary and foreign keys would really speed up access time.

If there aren't any properly defined primary and foreign keys, you can put indexes on the appropriate colums for speeding up access time afterwards, what does not directly affect the existing database schema, in other words, you don't do a redesign when adding indexes.

-- tesu

[pritaeas]

Not sure whether it would help your case, but since you're using AND's: the best way would be to put the most restrictive one in front. To clarify:

SELECT * FROM table WHERE true AND false
SELECT * FROM table WHERE false AND true

The second query would outperform the first.

A second option would be to create a temp-table for the city-country link. Now this query is executed for every record. A temp-table could easily be joined and you would gain a lot of processing time. (Assuming you have rights to do so.)

My $0.02

[peter_budo]

>>So which database engine?
Postgres 8.4

>>Are there any primary keys and foreign keys, or indexes defined on tables: items it, relations rel, relation_types relType?
item.id - primary
plus there is foreign key item.item_type_id (you see it at the end as passed value 2, which is given by previous selection)

relation_types.id primary key I look for it when I query about rel.name='CityCountry'

relataions.relation_type_id foreign from relation_type.id
relations.from_id foreign from items.id
relations.to_id foreign from items.id

[tesuji]

Hello,

I am glad to meeting here a postgresql fan too! So let's proceed unter mysql's hood.

What do you actually mean by

>> relataions.relation_type_id foreign from relation_type.id
>> relations.from_id foreign from items.id
>> relations.to_id foreign from items.id
?

Sure, because of the one-to-many relationships relataions.relation_type_id is a foreign key which points to master table relation_type. Both columns are used in the where clause to define the inner join.

But this usage DOES NOT mean that the table also has a foreign-key constraint what is usually table-defined in:

create table relataions (... id integer, ... FOREIGN KEY (id) REFERENCES relation_type, ...);

Also usage of relation_type.id does not mean that id is primary key of table relation_type, as usually defined in:

create table relation_type (... id integer, ... PRIMARY KEY (id), ...);

Well, if all these primary and foreign key constraints are well defined the way I just sketched out, there would be automatically put an index on all those primary and foreign key columns.

These indexes are very important to carry out all inner joins with high speed. If these indexes DO NOT exist because of omitting proper definition of primary AND foreign keys the inner joins would degenerate into cartesian products (cross products).

For example if all of your three tables have 1000 rows each, the number of comparisons necessary to compute the degenerated inner joins would be 1000 * 1000 * 1000 makes 10^9 comparisons and reads. Maybe this clarifies 22 sec processing time.

You may check the create-table statements or if not at hand you can also examine primary and foreign keys in system catalog of postgresql, especially in system table pg_constraint.

Well, I am not that sure whether postgresql acts this way if there aren't any indexes when to carry out joins. So it's quite usual that the query optimizer generates temporary indexes to speed up inner joins. I don't know how to visualize the execution plan of an select statement executed on postgresql, but such executing plans show all inner join and indexes.

-- tesu

[tesuji]

I found it how to show postgresql's execution plan (query plan):

EXPLAIN SELECT your_further_select_statement, and it's like Oracle's EXPLAIN PLAN (what has to be expected). Here is something about joins and cartesian products.

-- tesu

(The operator has decided that I can't update posting older than 29.999 minutes. So I did new posting)

[peter_budo]

We persuade customer let us use Hibernate and now its all OK.

Either way I want to say thank you for your time guys.

[tesuji]

I am glad that you solved it.

I am also about to learning Hibernate, yet i have been started with JPA 2.0 and Hibernate 3 on oracle database recently. It's really puzzling how Hibernate does all the mystic selects based only on a couple of annotations.

Afterwards, I think that the correlated select of your query might have also been responsible for that slow-down.

-- tesu