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
psql bookfind3 -c "drop table tt;"
psql bookfind3 -c "select id into tt from fti_author where string ~ '^arthur';"
psql bookfind3 -c "create index tti on tt(id);"
psql bookfind3 -c "select count(tt.id) from tt,fti_author a where tt.id=a.id and
a.string ~'^clarke';"
psql bookfind3 -c "select count(a1.id) from fti_author a1,fti_author a2
where a1.id=a2.id and a1.string ~ '^clarke' and a2.string ~ '^arthur';"
NOTICE: QUERY DUMP:
{ NESTLOOP :startup_cost 0.00 :total_cost 9.84 :rows 1 :width 8 :qptargetlist ({
TARGETENTRY :resdom { RESDOM :resno 1 :restype 26 :restypmod -1 :resname id :reskey 0
:reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 65000 :varattno 1
:vartype 26 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 2}}) :qpqual <>
:lefttree { INDEXSCAN :startup_cost 0.00 :total_cost 4.91 :rows 1 :width 4
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 26 :restypmod -1
:resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
:varno 2 :varattno 2 :vartype 26 :vartypmod -1 :varlevelsup 0 :varnoold 2 :varoattno
2}}) :qpqual ({ EXPR :typeOid 16 :opType op :oper { OPER :opno 1063 :opid 1254
:opresulttype 16 } :args ({ VAR :varno 2 :varattno 1 :vartype 1043 :vartypmod 54
:varlevelsup 0 :varnoold 2 :varoattno 1} { CONST :consttype 25 :constlen -1
:constisnull false :constvalue 11 [ 11 0 0 0 94 97 114 116 104 117 114 ] :constbyval
false })}) :lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0
:scanrelid 2 :indxid ( 29829975) :indxqual (({ EXPR :typeOid 16 :opType op :oper {
OPER :opno 1069 :opid 1074 :opresulttype 16 } :args ({ VAR :varno 2 :varattno 1
:vartype 1043 :vartypmod 54 :varlevelsup 0 :varnoold 2 :varoattno 1} { CONST
:consttype 1043 :constlen -1 :constisnull false :constvalue 10 [ 10 0 0 0 97 114 116
104 117 114 ] :constbyval false })} { EXPR :typeOid 16 :opType op :oper { OPER :opno
1066 :opid 1071 :opresulttype 16 } :args ({ VAR :varno 2 :varattno 1 :vartype 1043
:vartypmod 54 :varlevelsup 0 :varnoold 2 :varoattno 1} { CONST :consttype 1043
:constlen -1 :constisnull false :constvalue 10 [ 10 0 0 0 97 114 116 104 117 115 ]
:constbyval false })})) :indxqualorig (({ EXPR :typeOid 16 :opType op :oper { OPER
:opno 1069 :opid 1074 :opresulttype 16 } :args ({ VAR :varno 2 :varattno 1 :vartype
1043 :vartypmod 54 :varlevelsup 0 :varnoold 2 :varoattno 1} { CONST :consttype 1043
:constlen -1 :constisnull false :constvalue 10 [ 10 0 0 0 97 114 116 104 117 114 ]
:constbyval false })} { EXPR :typeOid 16 :opType op :oper { OPER :opno 1066 :opid
1071 :opresulttype 16 } :args ({ VAR :varno 2 :varattno 1 :vartype 1043 :vartypmod 54
:varlevelsup 0 :varnoold 2 :varoattno 1} { CONST :consttype 1043 :constlen -1
:constisnull false :constvalue 10 [ 10 0 0 0 97 114 116 104 117 115 ] :constbyval
false })})) :indxorderdir 1 } :righttree { INDEXSCAN :startup_cost 0.00 :total_cost
4.91 :rows 1 :width 4 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype
26 :restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false }
:expr { VAR :varno 1 :varattno 2 :vartype 26 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 2}}) :qpqual ({ EXPR :typeOid 16 :opType op :oper { OPER :opno 1063 :opid
1254 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype 1043 :vartypmod 54
:varlevelsup 0 :varnoold 1 :varoattno 1} { CONST :consttype 25 :constlen -1
:constisnull false :constvalue 11 [ 11 0 0 0 94 99 108 97 114 107 101 ] :constbyval
false })}) :lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0
:scanrelid 1 :indxid ( 29829975) :indxqual (({ EXPR :typeOid 16 :opType op :oper {
OPER :opno 1069 :opid 1074 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1
:vartype 1043 :vartypmod 54 :varlevelsup 0 :varnoold 1 :varoattno 1} { CONST
:consttype 1043 :constlen -1 :constisnull false :constvalue 10 [ 10 0 0 0 99 108 97
114 107 101 ] :constbyval false })} { EXPR :typeOid 16 :opType op :oper { OPER :opno
1066 :opid 1071 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype 1043
:vartypmod 54 :varlevelsup 0 :varnoold 1 :varoattno 1} { CONST :consttype 1043
:constlen -1 :constisnull false :constvalue 10 [ 10 0 0 0 99 108 97 114 107 102 ]
:constbyval false })})) :indxqualorig (({ EXPR :typeOid 16 :opType op :oper { OPER
:opno 1069 :opid 1074 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype
1043 :vartypmod 54 :varlevelsup 0 :varnoold 1 :varoattno 1} { CONST :consttype 1043
:constlen -1 :constisnull false :constvalue 10 [ 10 0 0 0 99 108 97 114 107 101 ]
:constbyval false })} { EXPR :typeOid 16 :opType op :oper { OPER :opno 1066 :opid
1071 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype 1043 :vartypmod 54
:varlevelsup 0 :varnoold 1 :varoattno 1} { CONST :consttype 1043 :constlen -1
:constisnull false :constvalue 10 [ 10 0 0 0 99 108 97 114 107 102 ] :constbyval
false })})) :indxorderdir 1 } :extprm () :locprm () :initplan <> :nprm 0 :jointype 0
:joinqual ({ EXPR :typeOid 16 :opType op :oper { OPER :opno 607 :opid 184
:opresulttype 16 } :args ({ VAR :varno 65000 :varattno 1 :vartype 26 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 2} { VAR :varno 65001 :varattno 1 :vartype 26
:vartypmod -1 :varlevelsup 0 :varnoold 2 :varoattno 2})})}
NOTICE: QUERY PLAN:
Nested Loop (cost=0.00..9.84 rows=1 width=8)
-> Index Scan using fti_author_s on fti_author a2 (cost=0.00..4.91 rows=1 width=4)
-> Index Scan using fti_author_s on fti_author a1 (cost=0.00..4.91 rows=1 width=4)
EXPLAIN
