Few questions about my slow query
От | webmaster |
---|---|
Тема | Few questions about my slow query |
Дата | |
Msg-id | 99071410540300.00685@localhost.localdomain обсуждение исходный текст |
Ответы |
Re: [SQL] Few questions about my slow query
|
Список | pgsql-sql |
Hi, I'm returning to postgresql performance problem, while I'm trying a lot to understand way pg is performing queries I need your help... So I have two tables: refs (...some fields... , key is id_tit char(7) - indexed with default method); temp_kat (...some fields..., key is id_ref char(7) - indexed with default method); Refs contains 80000 recs, temp_kat contains 5500 recs and all records from temp_kat have a matching key value in refs... When I need "mark" records in table refs (set the field exist for records existing also in temp_kat) I do following query: update refs set exist='true' where temp_kat.id_ref=refs.id_tit; This query takes a lot of time - I don't know exactly how long because I had to leave my office after 1.5 hour and the query was still running. I think it's too long... Before executing I increased shared buffers to 1024 and both tables are vacuumized. And I'm running the RedHat5.1 box on Celeron 266 with 64MB ram. So the questions are: 1. Did I reach the maximum possible performance or should I rewrite query some other way? 2. Could I start postgres with some diff. options for ex. with the feature of delayed flushing? My pg is 6.4.2 and I saw here there is a possibility to run it at some diff. mode but I don't know how (no docs about it) and don't know if it's only pg 6.5 feature 3. Should I change the indexing method from default one to some other? Many thanks for helping me; believe me that I'm not lazy to study but there is not so much docs about the internal pg behaviour so I can only ask you... -- Michal Samek, Tony distribuce s.r.o. webmaster@tony.cz (++420659/321350) ICQ: 38607210
В списке pgsql-sql по дате отправления: