即使列有索引也在使用顺序扫描

huangapple
go评论65阅读模式
英文:

Using Seq scan even though the column has an Index

问题

We've a requiremant ie., Result should come within in 3 seconds. Here i've described about 2 large tables which are having around 20Million records. As commented earlier, when we combine two columns of a different tables(1 is integer data type & another one is Boolean data type) the optimizer referring Seq scan eventhough the columns have their own indexes. How to avoid using the Seq scan and how to improve the performance, kindly assist.

Create Table1:

创建表格1:

Create Indexes for Table 1:

为表格1创建索引:

Create Table 2:

创建表格2:

Create Indexes for Table 2:

为表格2创建索引:

(Note: The SQL code provided is already in English, so there's no need for translation.)

英文:

We've a requiremant ie., Result should come within in 3 seconds. Here i've described about 2 large tables which are having around 20Million records. As commented earlier, when we combine two columns of a different tables(1 is integer data type & another one is Boolean data type) the optimizer referring Seq scan eventhough the columns have their own indexes. How to avoid using the Seq scan and how to improve the performance, kindly assist.

Create Table1:

CREATE TABLE IF NOT EXISTS schema1.table1
(
OID bigint NOT NULL,
EID bigint NOT NULL,
SOID bigint,
OName character varying COLLATE pg_catalog."default" NOT NULL,
OType integer NOT NULL,
parentid bigint,
pageorder integer,
OSType integer NOT NULL DEFAULT 0,
PStage integer NOT NULL DEFAULT 1,
lastmodifieddate timestamp without time zone,
lastmodifiedbyid bigint,
VSType integer,
OPath character varying COLLATE pg_catalog."default",
isarchive boolean,
Folder boolean,
FCategory bigint NOT NULL DEFAULT 915,
imotype boolean,
iostype boolean,
iemsg boolean NOT NULL DEFAULT false,
isgraphictype boolean,
extension character varying COLLATE pg_catalog."default",
OIDhier character varying COLLATE pg_catalog."default",
realpathoffset integer,
carvers0 bigint,
carvers1 bigint,
iarchtype boolean,
icontain boolean,
fromemail boolean,
iemailattach boolean,
iemailtype boolean,
headoffamilyid bigint,
indexingstate integer NOT NULL DEFAULT 0,
logicalsize bigint,
addescription character varying COLLATE pg_catalog."default",
objectuuid uuid DEFAULT uuid_generate_v1(),
hasprocessingerror boolean,
processdesc character varying COLLATE pg_catalog."default",
Folder_smallint smallint,
imotype_smallint smallint,
iostype_smallint smallint,
Folder_integer integer,
CONSTRAINT pk_table1 PRIMARY KEY (OID),
CONSTRAINT cmn_o_cmn_e FOREIGN KEY (EID)
REFERENCES schema1.table3 (EID) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE CASCADE
)
WITH (
FILLFACTOR = 30,
autovacuum_analyze_scale_factor = 0,
autovacuum_analyze_threshold = 10000,
autovacuum_vacuum_scale_factor = 0,
autovacuum_vacuum_threshold = 10000
)
TABLESPACE schema1_ts;
ALTER TABLE IF EXISTS schema1.table1
OWNER to schema1;
ALTER TABLE IF EXISTS schema1.table1
ALTER COLUMN FCategory SET STATISTICS 10000;
Create Indexes for Table 1:
CREATE INDEX IF NOT EXISTS archive_bix
ON schema1.table1 USING btree
(isarchive ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS cmn_o_indexingingstate_idx
ON schema1.table1 USING btree
(indexingstate ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS table1_OSType_idx
ON schema1.table1 USING btree
(OSType ASC NULLS LAST)
TABLESPACE schema1_ts;
CREATE INDEX IF NOT EXISTS dot_extension_bix
ON schema1.table1 USING btree
(extension COLLATE pg_catalog."default" ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS EID_bix
ON schema1.table1 USING btree
(EID ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS FCategory_OType_idx
ON schema1.table1 USING btree
(FCategory ASC NULLS LAST, OType ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS fromemail_bix
ON schema1.table1 USING btree
(fromemail ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS head_of_fam_bix
ON schema1.table1 USING btree
(headoffamilyid ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS is_container_bix
ON schema1.table1 USING btree
(icontain ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS iemailattach_bix
ON schema1.table1 USING btree
(iemailattach ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS iemsg_bix
ON schema1.table1 USING btree
(iemsg ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS iemailtype_bix
ON schema1.table1 USING btree
(iemailtype ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS Folder_bix
ON schema1.table1 USING btree
(Folder ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS Folder_idx_karthik
ON schema1.table1 USING btree
(Folder ASC NULLS LAST)
TABLESPACE pg_default
WHERE Folder IS FALSE;
CREATE INDEX IF NOT EXISTS Folder_integer_17052023_idx
ON schema1.table1 USING btree
(Folder_integer ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS Folder_partial_idx
ON schema1.table1 USING btree
(Folder ASC NULLS LAST)
TABLESPACE pg_default
WHERE Folder IS FALSE;
CREATE INDEX IF NOT EXISTS Folder_smallint_bix
ON schema1.table1 USING btree
(Folder_smallint ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS isgraphictype_bix
ON schema1.table1 USING btree
(isgraphictype ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS imotype_bix
ON schema1.table1 USING btree
(imotype ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS iostype_bix
ON schema1.table1 USING btree
(iostype ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS ix_karthik_userFCategory_bix
ON schema1.table1 USING btree
(OID ASC NULLS LAST, FCategory ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS logicalsize_idx
ON schema1.table1 USING btree
(logicalsize ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS logicalsize_idx_pt
ON schema1.table1 USING btree
(logicalsize ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS lower_dot_ext_bix
ON schema1.table1 USING btree
(extension COLLATE pg_catalog."default" ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS OName_idx
ON schema1.table1 USING btree
(OName COLLATE pg_catalog."default" ASC NULLS LAST, OIDhier COLLATE pg_catalog."default" ASC NULLS LAST, isgraphictype ASC NULLS LAST, OType ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS OType_idx
ON schema1.table1 USING btree
(OType ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS OType_idx_pt
ON schema1.table1 USING btree
(OType ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS OType_Folder_idx
ON schema1.table1 USING btree
(OType ASC NULLS LAST, Folder ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS objidpath_bix
ON schema1.table1 USING btree
(OIDhier COLLATE pg_catalog."default" ASC NULLS LAST, isgraphictype ASC NULLS LAST, OType ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS parentid_idx
ON schema1.table1 USING btree
(parentid ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE schema1_ts;
CREATE INDEX IF NOT EXISTS path_idx
ON schema1.table1 USING btree
(OPath COLLATE pg_catalog."default" ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS userFCategory_bix
ON schema1.table1 USING btree
(FCategory ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS userFCategory_bix_test
ON schema1.table1 USING btree
(FCategory ASC NULLS LAST)
TABLESPACE pg_default;
Create Table 2:
CREATE TABLE IF NOT EXISTS schema1.table2
(
OID bigint NOT NULL,
name_8_3 character varying COLLATE pg_catalog."default",
physicalsize bigint,
modidate bigint,
creationdateft bigint,
accessdateft bigint,
fatdate character varying COLLATE pg_catalog."default",
fsid bigint,
fslocator bytea,
fsfileclass integer,
isdeleted boolean,
isreadonly boolean,
issystem boolean,
ishidden boolean,
startclu bigint,
cmnt character varying COLLATE pg_catalog."default",
ent double precision,
chisqpercent double precision,
iencryp boolean,
idecryp boolean,
iudecryp boolean,
iscarved boolean,
isbadext boolean,
ifrecycle boolean,
isactual boolean,
isregistrytype boolean,
isthumbsdb boolean,
iwbartifact boolean,
inotkffig boolean,
inotkffigdups boolean,
adscount integer,
bnumber character varying COLLATE pg_catalog."default",
ifrmfrespace boolean,
decryptype integer,
mulmediatype integer,
multimtype character varying COLLATE pg_catalog."default",
hilevelurl character varying COLLATE pg_catalog."default",
chattype character varying COLLATE pg_catalog."default",
httpheader character varying COLLATE pg_catalog."default",
mimetype character varying COLLATE pg_catalog."default",
sourceapp character varying COLLATE pg_catalog."default",
width numeric(10,0),
height numeric(10,0),
allcustodians character varying COLLATE pg_catalog."default",
CONSTRAINT pk_table2 PRIMARY KEY (OID)
)
WITH (
FILLFACTOR = 50,
autovacuum_analyze_scale_factor = 0,
autovacuum_analyze_threshold = 10000,
autovacuum_vacuum_scale_factor = 0,
autovacuum_vacuum_threshold = 10000
)
TABLESPACE schema1_ts;
ALTER TABLE IF EXISTS schema1.table2
OWNER to schema1;
Create Indexes for Table 2:
CREATE INDEX IF NOT EXISTS actualfile_bix
ON schema1.table2 USING btree
(isactual ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE schema1_ts;
CREATE INDEX IF NOT EXISTS adscount_idx
ON schema1.table2 USING btree
(adscount ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE schema1_ts;
CREATE INDEX IF NOT EXISTS badextension_bix
ON schema1.table2 USING btree
(isbadext ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS carved_bix
ON schema1.table2 USING btree
(iscarved ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS createddate_idx
ON schema1.table2 USING btree
(creationdateft ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS decrypted_bix
ON schema1.table2 USING btree
(idecryp ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS decryptedbyuser_bix
ON schema1.table2 USING btree
(iudecryp ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS deleted_bix
ON schema1.table2 USING btree
(isdeleted ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE schema1_ts;
CREATE INDEX IF NOT EXISTS encrypted_bix
ON schema1.table2 USING btree
(iencryp ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS fatlastadateasstr_idx
ON schema1.table2 USING btree
(fatdate COLLATE pg_catalog."default" ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS fileclass_bix
ON schema1.table2 USING btree
(fsfileclass ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS fromrecyclebin_bix
ON schema1.table2 USING btree
(ifrecycle ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS fsid_idx
ON schema1.table2 USING btree
(fsid ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE schema1_ts;
CREATE INDEX IF NOT EXISTS hidden_bix
ON schema1.table2 USING btree
(ishidden ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE schema1_ts;
CREATE INDEX IF NOT EXISTS ifrmfrespace_bix
ON schema1.table2 USING btree
(ifrmfrespace ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE schema1_ts;
CREATE INDEX IF NOT EXISTS isnokffignoreos_bix
ON schema1.table2 USING btree
(inotkffig ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE schema1_ts;
CREATE INDEX IF NOT EXISTS isnokffignoreosdup_bix
ON schema1.table2 USING btree
(inotkffigdups ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE schema1_ts;
CREATE INDEX IF NOT EXISTS isregistrytype_bix
ON schema1.table2 USING btree
(isregistrytype ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE schema1_ts;
CREATE INDEX IF NOT EXISTS isthumbsdb_bix
ON schema1.table2 USING btree
(isthumbsdb ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE schema1_ts;
CREATE INDEX IF NOT EXISTS iwbartifact_bix
ON schema1.table2 USING btree
(iwbartifact ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE schema1_ts;
CREATE INDEX IF NOT EXISTS last_accesseddate_idx
ON schema1.table2 USING btree
(accessdateft ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS modifieddate_idx
ON schema1.table2 USING btree
(modidate ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS name_8_3_idx
ON schema1.table2 USING btree
(name_8_3 COLLATE pg_catalog."default" ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS physicalsize_idx
ON schema1.table2 USING btree
(physicalsize ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS read_only_bix
ON schema1.table2 USING btree
(isreadonly ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE schema1_ts;
CREATE INDEX IF NOT EXISTS startclu_idx
ON schema1.table2 USING btree
(startclu ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS system_bix
ON schema1.table2 USING btree
(issystem ASC NULLS LAST, OID ASC NULLS LAST)
TABLESPACE schema1_ts;

答案1

得分: 1

使用有限的数据（您的表格内容），这将会很困难，但让我们试试 😊。

查询似乎在您的问题中丢失，但它是/曾是：

SELECT (tab1.OID) AS tab1_OID_Count
FROM table1 tab1 
left outer JOIN table2 tab2  ON (tab2.OID = tab1.OID)
Where
  (tab1.Folder = false 
   OR (tab1.Folder IS NULL)
   OR (tab2.FSFileClass = 18)
    );

查询计划显示（使用我的有限测试数据）：

Hash Left Join  (cost=4612.54..11214.84 rows=141821 width
  Hash Cond: (tab1.oid = tab2.oid)                     
  Filter: ((NOT tab1.folder) OR (tab1.folder IS NULL) OR
  ->  Index Only Scan using folder_bix on table1 tab1  
  ->  Hash  (cost=2973.72..2973.72 rows=131072 width=12
        ->  Seq Scan on table2 tab2  (cost=0.00..2973.7

如上所述，使用 OR 对数据库来说可能较困难，因此让我们尝试不使用 OR，将查询更改为：

SELECT (tab1.OID) AS tab1_OID_Count
FROM table1 tab1 
left outer JOIN table2 tab2  ON (tab2.OID = tab1.OID)
Where
  (tab1.Folder = false 
   OR (tab1.Folder IS NULL)
    )
union 
SELECT (tab1.OID) AS tab1_OID_Count
FROM table1 tab1 
left outer JOIN table2 tab2  ON (tab2.OID = tab1.OID)
Where
  (tab2.FSFileClass = 18)
;

然后显示的查询计划是（使用我的有限测试数据，可能不代表您的情况）：

HashAggregate  (cost=21839.60..24477.54 rows=148095 width
  Group Key: tab1.oid                                  
  Planned Partitions: 4                                
  ->  Append  (cost=0.42..13879.49 rows=148095 width=8)
        ->  Index Only Scan using folder_bix on table1 
              Filter: ((NOT folder) OR (folder IS NULL))
        ->  Merge Join  (cost=0.84..5447.70 rows=6685 wid
              Merge Cond: (tab1_1.oid = tab2.oid)       
              ->  Index Only Scan using pk_table1 on tab
              ->  Index Only Scan using fileclass_bix o
                    Index Cond: (fsfileclass = 18)

希望这可以改善您的查询性能，请分享时间上的改善情况（如果有的话...）。

英文:

With the limited data (the contents of your tables), it will be hard, but let's try 😉

the query got lost from your question, but it is/was:

SELECT (tab1.OID) AS tab1_OID_Count
FROM table1 tab1 
left outer JOIN table2 tab2  ON (tab2.OID = tab1.OID)
Where
(tab1.Folder = false 
OR (tab1.Folder IS NULL)
OR (tab2.FSFileClass = 18)
);

The query plan shows (with my, limited, test data):

Hash Left Join  (cost=4612.54..11214.84 rows=141821 wid
Hash Cond: (tab1.oid = tab2.oid)                     
Filter: ((NOT tab1.folder) OR (tab1.folder IS NULL) O
->  Index Only Scan using folder_bix on table1 tab1  
->  Hash  (cost=2973.72..2973.72 rows=131072 width=12
->  Seq Scan on table2 tab2  (cost=0.00..2973.7

As said, using OR can be hard for a database, so let's try to not use the OR, and change the query to:

SELECT (tab1.OID) AS tab1_OID_Count
FROM table1 tab1 
left outer JOIN table2 tab2  ON (tab2.OID = tab1.OID)
Where
(tab1.Folder = false 
OR (tab1.Folder IS NULL)
)
union 
SELECT (tab1.OID) AS tab1_OID_Count
FROM table1 tab1 
left outer JOIN table2 tab2  ON (tab2.OID = tab1.OID)
Where
(tab2.FSFileClass = 18)
;

The query plan shown (with my limited test data, that might not represent your situation) is then:

HashAggregate  (cost=21839.60..24477.54 rows=148095 wid
Group Key: tab1.oid                                  
Planned Partitions: 4                                
->  Append  (cost=0.42..13879.49 rows=148095 width=8)
->  Index Only Scan using folder_bix on table1 
Filter: ((NOT folder) OR (folder IS NULL)
->  Merge Join  (cost=0.84..5447.70 rows=6685 w
Merge Cond: (tab1_1.oid = tab2.oid)      
->  Index Only Scan using pk_table1 on ta
->  Index Only Scan using fileclass_bix o
Index Cond: (fsfileclass = 18)

I hope this will improve your query performance, and please share the improvement in time (if there is any...)

huangapple
  • 本文由 发表于 2023年5月29日 19:36:29
  • 转载请务必保留本文链接：https://go.coder-hub.com/76357000.html
  • postgresql-14
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定