From: | Gena Gurchonok <gena(at)rt(dot)mipt(dot)ru> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | query execution time |
Date: | 2000-11-30 12:50:41 |
Message-ID: | 1771133161.20001130155041@rt.mipt.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
Attachment | Content-Type | Size |
---|---|---|
t1.txt | text/plain | 266 bytes |
t2.txt | text/plain | 145 bytes |
t2_plan.txt | text/plain | 5.0 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2000-11-30 15:56:17 | Re: Both cross-named & compound foreign key constaints fail |
Previous Message | Karla Peralta | 2000-11-30 12:21:38 | Support for SQLkit |