Hello,
I am using sqlite 3.12.2 and I am getting a strange behaviour from a
very simple query. I am trying to understand the exact query plan and
the cause of this problem. I would be grateful if someone could help me.
The query is:
SELECT count(qview1."wlbWellboreName") FROM "discovery" qview2 CROSS
JOIN "wellbore_development_all" qview1 WHERE
(qview1."wlbNpdidWellbore" = qview2."wlbNpdidWellbore");
I have indexes on columns wlbNpdidWellbore in both tables, but I have
not declared any primary keys. On cold cache this query takes almost
three minutes, whereas on warm cache it takes around 100 msec. If I
replace qview1."wlbWellboreName" with * it runs in a couple of seconds
on cold cache. If I create the table "wellbore_development_all" from the
beginning setting wlbNpdidWellbore as primary key, the query runs in
under 3 seconds on cold cache and in a couple of tens of milliseconds on
warm cache.
I have executed analyze and vacuum on both tables before the query, both
with clustered and non clustered indexes. I would normally expect that
the difference would be very small: one more page fetch for each value
of wlbNpdidWellbore that it is matched in the index of
wellbore_development_all
I am pasting the output of .schema and stats for the two tables and also
the output of explain for both cases. From the explain, if I understand
correctly, I can see that we first search the whole
"wellbore_development_all" table (2nd OpenRead) and then the index (3rd
OpenRead). Is that correct? And if so, why is that?
Thanks,
Dimitris
discovery table:
sqlite> .schema
CREATE TABLE discovery( dscName TEXT,cmpLongName
TEXT,dscCurrentActivityStatus TEXT,dscHcType TEXT,wlbName TEXT,nmaName
TEXT,fldName TEXT,dscDateFromInclInField TEXT,dscDiscoveryYear
INTEGER,dscResInclInDiscoveryName TEXT,dscOwnerKind TEXT,dscOwnerName
TEXT,dscNpdidDiscovery INTEGER,fldNpdidField INTEGER,wlbNpdidWellbore
INTEGER,dscFactPageUrl TEXT,dscFactMapUrl TEXT,dscDateUpdated
TEXT,dscDateUpdatedMax TEXT,dateSyncNPD TEXT);
CREATE UNIQUE INDEX "discovery_dscNpdidDiscovery_pkey" ON "discovery"
("dscNpdidDiscovery")
;
CREATE INDEX "IX368_discovery" ON "discovery"
(
"wlbNpdidWellbore"
)
;
CREATE INDEX "IX367_discovery" ON "discovery"
(
"fldNpdidField"
)
;
sqlite> select * from sqlite_stat1;
discovery|IX367_discovery|42100 5
discovery|IX368_discovery|42100 2
discovery|discovery_dscNpdidDiscovery_pkey|42100 1
wellbore_development_all table without primary key declaration:
sqlite> .schema
CREATE TABLE wellbore_development_all( wlbWellboreName TEXT,wlbWell
TEXT,wlbDrillingOperator TEXT,wlbDrillingOperatorGroup
TEXT,wlbProductionLicence TEXT,wlbPurposePlanned TEXT,wlbContent
TEXT,wlbWellType TEXT,wlbEntryDate TEXT,wlbCompletionDate TEXT,wlbField
TEXT,wlbDrillPermit TEXT,wlbDiscovery TEXT,wlbDiscoveryWellbore
TEXT,wlbKellyBushElevation REAL,wlbFinalVerticalDepth REAL,wlbTotalDepth
REAL,wlbWaterDepth REAL,wlbMainArea TEXT,wlbDrillingFacility
TEXT,wlbFacilityTypeDrilling TEXT,wlbProductionFacility
TEXT,wlbLicensingActivity TEXT,wlbMultilateral TEXT,wlbContentPlanned
TEXT,wlbEntryYear INTEGER,wlbCompletionYear
INTEGER,wlbReclassFromWellbore TEXT,wlbPlotSymbol
INTEGER,wlbGeodeticDatum TEXT,wlbNsDeg INTEGER,wlbNsMin INTEGER,wlbNsSec
REAL,wlbNsCode TEXT,wlbEwDeg INTEGER,wlbEwMin INTEGER,wlbEwSec
REAL,wlbEwCode TEXT,wlbNsDecDeg REAL,wlbEwDesDeg REAL,wlbNsUtm
REAL,wlbEwUtm REAL,wlbUtmZone INTEGER,wlbNamePart1 INTEGER,wlbNamePart2
INTEGER,wlbNamePart3 TEXT,wlbNamePart4 INTEGER,wlbNamePart5
TEXT,wlbNamePart6 TEXT,wlbFactPageUrl TEXT,wlbFactMapUrl
TEXT,wlbDiskosWellboreType TEXT,wlbDiskosWellboreParent
TEXT,wlbNpdidWellbore INTEGER,dscNpdidDiscovery INTEGER,fldNpdidField
INTEGER,wlbWdssQcdate TEXT,prlNpdidProductionLicence
INTEGER,fclNpdidFacilityDrilling INTEGER,fclNpdidFacilityProducing
INTEGER,wlbNpdidWellboreReclass INTEGER,wlbDiskosWellOperator
TEXT,wlbDateUpdated TEXT,wlbDateUpdatedMax TEXT,dateSyncNPD TEXT);
CREATE UNIQUE INDEX "wellbore_development_all_wlbNpdidWellbore_pkey" ON
"wellbore_development_all" ("wlbNpdidWellbore")
;
CREATE INDEX "IX1608_wellbore_development_all" ON "wellbore_development_all"
(
"prlNpdidProductionLicence"
)
;
CREATE INDEX "IX1607_wellbore_development_all" ON "wellbore_development_all"
(
"fldNpdidField"
)
;
CREATE INDEX "IX1610_wellbore_development_all" ON "wellbore_development_all"
(
"wlbDiskosWellOperator"
)
;
CREATE INDEX "IX1605_wellbore_development_all" ON "wellbore_development_all"
(
"wlbDrillingOperator"
)
;
CREATE INDEX "IX1606_wellbore_development_all" ON "wellbore_development_all"
(
"dscNpdidDiscovery"
)
;
CREATE INDEX "IX1609_wellbore_development_all" ON "wellbore_development_all"
(
"wlbNpdidWellboreReclass"
)
;
sqlite> select * from sqlite_stat1;
wellbore_development_all|IX1609_wellbore_development_all|389400 78
wellbore_development_all|IX1606_wellbore_development_all|389400 32
wellbore_development_all|IX1605_wellbore_development_all|389400 126
wellbore_development_all|IX1610_wellbore_development_all|389400 126
wellbore_development_all|IX1607_wellbore_development_all|389400 43
wellbore_development_all|IX1608_wellbore_development_all|389400 44
wellbore_development_all|wellbore_development_all_wlbNpdidWellbore_pkey|389400
1
wellbore_development_all with primary key:
sqlite> .schema
CREATE TABLE "wellbore_development_all"( wlbWellboreName TEXT,wlbWell
TEXT,wlbDrillingOperator TEXT,wlbDrillingOperatorGroup
TEXT,wlbProductionLicence TEXT,wlbPurposePlanned TEXT,wlbContent
TEXT,wlbWellType TEXT,wlbEntryDate TEXT,wlbCompletionDate TEXT,wlbField
TEXT,wlbDrillPermit TEXT,wlbDiscovery TEXT,wlbDiscoveryWellbore
TEXT,wlbKellyBushElevation REAL,wlbFinalVerticalDepth REAL,wlbTotalDepth
REAL,wlbWaterDepth REAL,wlbMainArea TEXT,wlbDrillingFacility
TEXT,wlbFacilityTypeDrilling TEXT,wlbProductionFacility
TEXT,wlbLicensingActivity TEXT,wlbMultilateral TEXT,wlbContentPlanned
TEXT,wlbEntryYear INTEGER,wlbCompletionYear
INTEGER,wlbReclassFromWellbore TEXT,wlbPlotSymbol
INTEGER,wlbGeodeticDatum TEXT,wlbNsDeg INTEGER,wlbNsMin INTEGER,wlbNsSec
REAL,wlbNsCode TEXT,wlbEwDeg INTEGER,wlbEwMin INTEGER,wlbEwSec
REAL,wlbEwCode TEXT,wlbNsDecDeg REAL,wlbEwDesDeg REAL,wlbNsUtm
REAL,wlbEwUtm REAL,wlbUtmZone INTEGER,wlbNamePart1 INTEGER,wlbNamePart2
INTEGER,wlbNamePart3 TEXT,wlbNamePart4 INTEGER,wlbNamePart5
TEXT,wlbNamePart6 TEXT,wlbFactPageUrl TEXT,wlbFactMapUrl
TEXT,wlbDiskosWellboreType TEXT,wlbDiskosWellboreParent
TEXT,wlbNpdidWellbore INTEGER PRIMARY KEY,dscNpdidDiscovery
INTEGER,fldNpdidField INTEGER,wlbWdssQcdate
TEXT,prlNpdidProductionLicence INTEGER,fclNpdidFacilityDrilling
INTEGER,fclNpdidFacilityProducing INTEGER,wlbNpdidWellboreReclass
INTEGER,wlbDiskosWellOperator TEXT,wlbDateUpdated TEXT,wlbDateUpdatedMax
TEXT,dateSyncNPD TEXT);
CREATE INDEX "IX1608_wellbore_development_all" ON "wellbore_development_all"
(
"prlNpdidProductionLicence"
)
;
CREATE INDEX "IX1607_wellbore_development_all" ON "wellbore_development_all"
(
"fldNpdidField"
)
;
CREATE INDEX "IX1610_wellbore_development_all" ON "wellbore_development_all"
(
"wlbDiskosWellOperator"
)
;
CREATE INDEX "IX1605_wellbore_development_all" ON "wellbore_development_all"
(
"wlbDrillingOperator"
)
;
CREATE INDEX "IX1606_wellbore_development_all" ON "wellbore_development_all"
(
"dscNpdidDiscovery"
)
;
CREATE INDEX "IX1609_wellbore_development_all" ON "wellbore_development_all"
(
"wlbNpdidWellboreReclass"
)
;
CREATE UNIQUE INDEX "wellbore_development_all_wlbNpdidWellbore_pkey" ON
"wellbore_development_all" ("wlbNpdidWellbore")
;
sqlite> select * from sqlite_stat1;
wellbore_development_all|wellbore_development_all_wlbNpdidWellbore_pkey|389400
1
wellbore_development_all|IX1609_wellbore_development_all|389400 78
wellbore_development_all|IX1606_wellbore_development_all|389400 32
wellbore_development_all|IX1605_wellbore_development_all|389400 126
wellbore_development_all|IX1610_wellbore_development_all|389400 126
wellbore_development_all|IX1607_wellbore_development_all|389400 43
wellbore_development_all|IX1608_wellbore_development_all|389400 44
The output of the explain for the first case(nonclustered
index-wlbNpdidWellbore is not primary key in wellbore_development_all):
sqlite> explain query plan SELECT count(qview1."wlbWellboreName") FROM
"discovery" qview2 CROSS JOIN "wellbore_development_all" qview1 WHERE
(qview1."wlbNpdidWellbore" = qview2."wlbNpdidWellbore");
0|0|0|SCAN TABLE discovery AS qview2 USING COVERING INDEX IX368_discovery
0|1|1|SEARCH TABLE wellbore_development_all AS qview1 USING INDEX
wellbore_development_all_wlbNpdidWellbore_pkey (wlbNpdidWellbore=?)
sqlite> explain SELECT count(qview1."wlbWellboreName") FROM "discovery"
qview2 CROSS JOIN "wellbore_development_all" qview1 WHERE
(qview1."wlbNpdidWellbore" = qview2."wlbNpdidWellbore");
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 23 0 00 Start at 23
1 Null 0 1 2 00 r[1..2]=NULL
2 OpenRead 2 13788 2 k(2,,) 00 root=13788
iDb=2; IX368_discovery
3 OpenRead 1 2 3 65 00 root=2 iDb=3;
wellbore_development_all
4 OpenRead 3 393308 3 k(2,,) 02 root=393308
iDb=3; wellbore_development_all_wlbNpdidWellbore_pkey
5 Rewind 2 16 3 0 00
6 Column 2 0 3 00
r[3]=discovery.wlbNpdidWellbore
7 IsNull 3 15 0 00 if
r[3]==NULL goto 15
8 Affinity 3 1 0 D 00 affinity(r[3])
9 SeekGE 3 15 3 1 00 key=r[3]
10 IdxGT 3 15 3 1 00 key=r[3]
11 Seek 3 0 1 00 Move 1 to
3.rowid
12 Column 1 0 4 00
r[4]=wellbore_development_all.wlbWellboreName
13 AggStep0 0 4 1 count(1) 01 accum=r[1]
step(r[4])
14 Next 3 10 1 00
15 Next 2 6 0 01
16 Close 2 0 0 00
17 Close 1 0 0 00
18 Close 3 0 0 00
19 AggFinal 1 1 0 count(1) 00 accum=r[1] N=1
20 Copy 1 5 0 00 r[5]=r[1]
21 ResultRow 5 1 0 00 output=r[5]
22 Halt 0 0 0 00
23 Transaction 2 0 5 0 01 usesStmtJournal=0
24 Transaction 3 0 11 0 01 usesStmtJournal=0
25 TableLock 2 2 0 discovery 00 iDb=2 root=2
write=0
26 TableLock 3 2 0 wellbore_development_all 00
iDb=3 root=2 write=0
27 Goto 0 1 0 00
The output of explain for the second case (clustered
index-wlbNpdidWellbore is primary key in wellbore_development_all):
sqlite> explain query plan SELECT count(qview1."wlbWellboreName") FROM
"discovery" qview2 CROSS JOIN "wellbore_development_all" qview1 WHERE
(qview1."wlbNpdidWellbore" = qview2."wlbNpdidWellbore");
0|0|0|SCAN TABLE discovery AS qview2 USING COVERING INDEX IX368_discovery
0|1|1|SEARCH TABLE wellbore_development_all AS qview1 USING INTEGER
PRIMARY KEY (rowid=?)
sqlite> explain SELECT count(qview1."wlbWellboreName") FROM
"discovery" qview2 CROSS JOIN "wellbore_development_all" qview1 WHERE
(qview1."wlbNpdidWellbore" = qview2."wlbNpdidWellbore");
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 17 0 00 Start at 17
1 Null 0 1 2 00 r[1..2]=NULL
2 OpenRead 2 5 3 k(2,,) 00 root=5 iDb=3;
IX368_discovery
3 OpenRead 1 3 2 65 00 root=3 iDb=2;
wellbore_development_all
4 Rewind 2 11 3 0 00
5 Column 2 0 3 00
r[3]=discovery.wlbNpdidWellbore
6 MustBeInt 3 10 0 00
7 NotExists 1 10 3 00 intkey=r[3]; pk
8 Column 1 0 4 00
r[4]=wellbore_development_all.wlbWellboreName
9 AggStep0 0 4 1 count(1) 01 accum=r[1]
step(r[4])
10 Next 2 5 0 01
11 Close 2 0 0 00
12 Close 1 0 0 00
13 AggFinal 1 1 0 count(1) 00 accum=r[1] N=1
14 Copy 1 5 0 00 r[5]=r[1]
15 ResultRow 5 1 0 00 output=r[5]
16 Halt 0 0 0 00
17 Transaction 2 0 23 0 01 usesStmtJournal=0
18 Transaction 3 0 13 0 01 usesStmtJournal=0
19 TableLock 3 3 0 discovery 00 iDb=3 root=3
write=0
20 TableLock 2 3 0 wellbore_development_all 00
iDb=2 root=3 write=0
21 Goto 0 1 0 00
