query execution time
От | Gena Gurchonok |
---|---|
Тема | query execution time |
Дата | |
Msg-id | 1771133161.20001130155041@rt.mipt.ru обсуждение исходный текст |
Список | pgsql-bugs |
Hello I've found a strange behavior in query execution: I have really big table fti_author (>4 000 000 records) and i want to find ID intersection of ones contain '^clarke' and '^arthur' I've created 2 methods 1 select all with '^clarke' into temp table select intersection of ones with '^arthur' and temp table (example t1.txt in attachment) 2 one select with set of conditions (example t2.txt in attachment) THE PROBLEM is that 1st method requires 0.7sec and the 2nd - 3m20sec. Please tell me why. PS Please find attached to this letter verbose plan for second query. = selected dump ============================ -- -- TOC Entry ID 3 (OID 26649711) -- -- Name: fti_author Type: TABLE Owner: gena -- CREATE TABLE "fti_author" ( "string" character varying(50), "id" oid ); -- -- TOC Entry ID 7 (OID 26649711) -- -- Name: "fti_author_s" Type: INDEX Owner: gena -- CREATE INDEX "fti_author_s" on "fti_author" using btree ( "string" "varchar_ops" ); -- -- TOC Entry ID 8 (OID 26649711) -- -- Name: "fti_author_i" Type: INDEX Owner: gena -- CREATE INDEX "fti_author_i" on "fti_author" using btree ( "id" "oid_ops" ); ==================== I'm using compiled CVS tree dated 23 Nov 2000. complied with options --enable-syslog --prefix= --exec-prefix=/usr --with-locale --with-perl compiler gcc version pgcc-2.91.66 19990314 (egcs-1.1.2 release) System Mandrake Linux 6.0 (with some updates) Celeron 500/128M Gena
Вложения
В списке pgsql-bugs по дате отправления: