Обсуждение: Re: SELECT performance problem on a join

What do you get when you send an explain of the select?

Type this in and tell me that it says.
==============
explain select t_test.doc_id,  t_test.f_num_centre, t_test.f_num_candidat from t_test, doc where t_test.doc_id=doc.id
anddoc.docsubset_id=2000077392 
==============

What will happen is the database will tell you what it did and how long it took to perform each step. That way you can
seeany bottlenecks or areas that you can try to fix to speed up your database. 


>>> Patrice_Espié <patrice.espie@univ-lyon2.fr> 04/25/01 10:33AM >>>
Hello,

I have a great problem with performances of my database, on the SQL request:
---------------
select t_test.doc_id,  t_test.f_num_centre, t_test.f_num_candidat from
t_test, doc where t_test.doc_id=doc.id and doc.docsubset_id=2000077392
---------------
The doc table has 36.000 rows, and t_test the same. There is an index on doc
(docsubset_id) and an index on t_test (doc_id). There is others index too.

When I send this request, I must wait for about 45 sec. before the results
appears.
The server is a BI-PII 266, and there is about no activity on the database
neither on the server.

When I was using this request under MS-SQLServer (v6), the wait time was
about nothing.

What's wrong ?

Thank's a lot
Patrice Espie


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

>> >  2) Have you 'VACUUM ANALYZE t_test' and 'VACUUM ANALYZE doc' yet?
>> >Try #2, it should help you quite a bit.
>> I jsut did it : it said : VACUUM. Nothing else. This doesn't help me,
realy
>> ! ;)
>Have you tried your query since you VACUUM'ed?
>It helps a lot more than you would think.

Done. The problem has disapeared !!
Any explanation ?

Thank's a lot
Patrice