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

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

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. 创建表格1:

Create Indexes for Table 1:

  1. 为表格1创建索引:

Create Table 2:

  1. 创建表格2:

Create Indexes for Table 2:

  1. 为表格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:

  1. CREATE TABLE IF NOT EXISTS schema1.table1
  2. (
  3. OID bigint NOT NULL,
  4. EID bigint NOT NULL,
  5. SOID bigint,
  6. OName character varying COLLATE pg_catalog."default" NOT NULL,
  7. OType integer NOT NULL,
  8. parentid bigint,
  9. pageorder integer,
  10. OSType integer NOT NULL DEFAULT 0,
  11. PStage integer NOT NULL DEFAULT 1,
  12. lastmodifieddate timestamp without time zone,
  13. lastmodifiedbyid bigint,
  14. VSType integer,
  15. OPath character varying COLLATE pg_catalog."default",
  16. isarchive boolean,
  17. Folder boolean,
  18. FCategory bigint NOT NULL DEFAULT 915,
  19. imotype boolean,
  20. iostype boolean,
  21. iemsg boolean NOT NULL DEFAULT false,
  22. isgraphictype boolean,
  23. extension character varying COLLATE pg_catalog."default",
  24. OIDhier character varying COLLATE pg_catalog."default",
  25. realpathoffset integer,
  26. carvers0 bigint,
  27. carvers1 bigint,
  28. iarchtype boolean,
  29. icontain boolean,
  30. fromemail boolean,
  31. iemailattach boolean,
  32. iemailtype boolean,
  33. headoffamilyid bigint,
  34. indexingstate integer NOT NULL DEFAULT 0,
  35. logicalsize bigint,
  36. addescription character varying COLLATE pg_catalog."default",
  37. objectuuid uuid DEFAULT uuid_generate_v1(),
  38. hasprocessingerror boolean,
  39. processdesc character varying COLLATE pg_catalog."default",
  40. Folder_smallint smallint,
  41. imotype_smallint smallint,
  42. iostype_smallint smallint,
  43. Folder_integer integer,
  44. CONSTRAINT pk_table1 PRIMARY KEY (OID),
  45. CONSTRAINT cmn_o_cmn_e FOREIGN KEY (EID)
  46. REFERENCES schema1.table3 (EID) MATCH SIMPLE
  47. ON UPDATE NO ACTION
  48. ON DELETE CASCADE
  49. )
  50. WITH (
  51. FILLFACTOR = 30,
  52. autovacuum_analyze_scale_factor = 0,
  53. autovacuum_analyze_threshold = 10000,
  54. autovacuum_vacuum_scale_factor = 0,
  55. autovacuum_vacuum_threshold = 10000
  56. )
  57. TABLESPACE schema1_ts;
  58. ALTER TABLE IF EXISTS schema1.table1
  59. OWNER to schema1;
  60. ALTER TABLE IF EXISTS schema1.table1
  61. ALTER COLUMN FCategory SET STATISTICS 10000;
  62. Create Indexes for Table 1:
  63. CREATE INDEX IF NOT EXISTS archive_bix
  64. ON schema1.table1 USING btree
  65. (isarchive ASC NULLS LAST, OID ASC NULLS LAST)
  66. TABLESPACE pg_default;
  67. CREATE INDEX IF NOT EXISTS cmn_o_indexingingstate_idx
  68. ON schema1.table1 USING btree
  69. (indexingstate ASC NULLS LAST, OID ASC NULLS LAST)
  70. TABLESPACE pg_default;
  71. CREATE INDEX IF NOT EXISTS table1_OSType_idx
  72. ON schema1.table1 USING btree
  73. (OSType ASC NULLS LAST)
  74. TABLESPACE schema1_ts;
  75. CREATE INDEX IF NOT EXISTS dot_extension_bix
  76. ON schema1.table1 USING btree
  77. (extension COLLATE pg_catalog."default" ASC NULLS LAST, OID ASC NULLS LAST)
  78. TABLESPACE pg_default;
  79. CREATE INDEX IF NOT EXISTS EID_bix
  80. ON schema1.table1 USING btree
  81. (EID ASC NULLS LAST, OID ASC NULLS LAST)
  82. TABLESPACE pg_default;
  83. CREATE INDEX IF NOT EXISTS FCategory_OType_idx
  84. ON schema1.table1 USING btree
  85. (FCategory ASC NULLS LAST, OType ASC NULLS LAST, OID ASC NULLS LAST)
  86. TABLESPACE pg_default;
  87. CREATE INDEX IF NOT EXISTS fromemail_bix
  88. ON schema1.table1 USING btree
  89. (fromemail ASC NULLS LAST, OID ASC NULLS LAST)
  90. TABLESPACE pg_default;
  91. CREATE INDEX IF NOT EXISTS head_of_fam_bix
  92. ON schema1.table1 USING btree
  93. (headoffamilyid ASC NULLS LAST, OID ASC NULLS LAST)
  94. TABLESPACE pg_default;
  95. CREATE INDEX IF NOT EXISTS is_container_bix
  96. ON schema1.table1 USING btree
  97. (icontain ASC NULLS LAST, OID ASC NULLS LAST)
  98. TABLESPACE pg_default;
  99. CREATE INDEX IF NOT EXISTS iemailattach_bix
  100. ON schema1.table1 USING btree
  101. (iemailattach ASC NULLS LAST, OID ASC NULLS LAST)
  102. TABLESPACE pg_default;
  103. CREATE INDEX IF NOT EXISTS iemsg_bix
  104. ON schema1.table1 USING btree
  105. (iemsg ASC NULLS LAST, OID ASC NULLS LAST)
  106. TABLESPACE pg_default;
  107. CREATE INDEX IF NOT EXISTS iemailtype_bix
  108. ON schema1.table1 USING btree
  109. (iemailtype ASC NULLS LAST, OID ASC NULLS LAST)
  110. TABLESPACE pg_default;
  111. CREATE INDEX IF NOT EXISTS Folder_bix
  112. ON schema1.table1 USING btree
  113. (Folder ASC NULLS LAST, OID ASC NULLS LAST)
  114. TABLESPACE pg_default;
  115. CREATE INDEX IF NOT EXISTS Folder_idx_karthik
  116. ON schema1.table1 USING btree
  117. (Folder ASC NULLS LAST)
  118. TABLESPACE pg_default
  119. WHERE Folder IS FALSE;
  120. CREATE INDEX IF NOT EXISTS Folder_integer_17052023_idx
  121. ON schema1.table1 USING btree
  122. (Folder_integer ASC NULLS LAST)
  123. TABLESPACE pg_default;
  124. CREATE INDEX IF NOT EXISTS Folder_partial_idx
  125. ON schema1.table1 USING btree
  126. (Folder ASC NULLS LAST)
  127. TABLESPACE pg_default
  128. WHERE Folder IS FALSE;
  129. CREATE INDEX IF NOT EXISTS Folder_smallint_bix
  130. ON schema1.table1 USING btree
  131. (Folder_smallint ASC NULLS LAST)
  132. TABLESPACE pg_default;
  133. CREATE INDEX IF NOT EXISTS isgraphictype_bix
  134. ON schema1.table1 USING btree
  135. (isgraphictype ASC NULLS LAST, OID ASC NULLS LAST)
  136. TABLESPACE pg_default;
  137. CREATE INDEX IF NOT EXISTS imotype_bix
  138. ON schema1.table1 USING btree
  139. (imotype ASC NULLS LAST, OID ASC NULLS LAST)
  140. TABLESPACE pg_default;
  141. CREATE INDEX IF NOT EXISTS iostype_bix
  142. ON schema1.table1 USING btree
  143. (iostype ASC NULLS LAST, OID ASC NULLS LAST)
  144. TABLESPACE pg_default;
  145. CREATE INDEX IF NOT EXISTS ix_karthik_userFCategory_bix
  146. ON schema1.table1 USING btree
  147. (OID ASC NULLS LAST, FCategory ASC NULLS LAST)
  148. TABLESPACE pg_default;
  149. CREATE INDEX IF NOT EXISTS logicalsize_idx
  150. ON schema1.table1 USING btree
  151. (logicalsize ASC NULLS LAST, OID ASC NULLS LAST)
  152. TABLESPACE pg_default;
  153. CREATE INDEX IF NOT EXISTS logicalsize_idx_pt
  154. ON schema1.table1 USING btree
  155. (logicalsize ASC NULLS LAST)
  156. TABLESPACE pg_default;
  157. CREATE INDEX IF NOT EXISTS lower_dot_ext_bix
  158. ON schema1.table1 USING btree
  159. (extension COLLATE pg_catalog."default" ASC NULLS LAST, OID ASC NULLS LAST)
  160. TABLESPACE pg_default;
  161. CREATE INDEX IF NOT EXISTS OName_idx
  162. ON schema1.table1 USING btree
  163. (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)
  164. TABLESPACE pg_default;
  165. CREATE INDEX IF NOT EXISTS OType_idx
  166. ON schema1.table1 USING btree
  167. (OType ASC NULLS LAST, OID ASC NULLS LAST)
  168. TABLESPACE pg_default;
  169. CREATE INDEX IF NOT EXISTS OType_idx_pt
  170. ON schema1.table1 USING btree
  171. (OType ASC NULLS LAST)
  172. TABLESPACE pg_default;
  173. CREATE INDEX IF NOT EXISTS OType_Folder_idx
  174. ON schema1.table1 USING btree
  175. (OType ASC NULLS LAST, Folder ASC NULLS LAST)
  176. TABLESPACE pg_default;
  177. CREATE INDEX IF NOT EXISTS objidpath_bix
  178. ON schema1.table1 USING btree
  179. (OIDhier COLLATE pg_catalog."default" ASC NULLS LAST, isgraphictype ASC NULLS LAST, OType ASC NULLS LAST, OID ASC NULLS LAST)
  180. TABLESPACE pg_default;
  181. CREATE INDEX IF NOT EXISTS parentid_idx
  182. ON schema1.table1 USING btree
  183. (parentid ASC NULLS LAST, OID ASC NULLS LAST)
  184. TABLESPACE schema1_ts;
  185. CREATE INDEX IF NOT EXISTS path_idx
  186. ON schema1.table1 USING btree
  187. (OPath COLLATE pg_catalog."default" ASC NULLS LAST, OID ASC NULLS LAST)
  188. TABLESPACE pg_default;
  189. CREATE INDEX IF NOT EXISTS userFCategory_bix
  190. ON schema1.table1 USING btree
  191. (FCategory ASC NULLS LAST, OID ASC NULLS LAST)
  192. TABLESPACE pg_default;
  193. CREATE INDEX IF NOT EXISTS userFCategory_bix_test
  194. ON schema1.table1 USING btree
  195. (FCategory ASC NULLS LAST)
  196. TABLESPACE pg_default;
  197. Create Table 2:
  198. CREATE TABLE IF NOT EXISTS schema1.table2
  199. (
  200. OID bigint NOT NULL,
  201. name_8_3 character varying COLLATE pg_catalog."default",
  202. physicalsize bigint,
  203. modidate bigint,
  204. creationdateft bigint,
  205. accessdateft bigint,
  206. fatdate character varying COLLATE pg_catalog."default",
  207. fsid bigint,
  208. fslocator bytea,
  209. fsfileclass integer,
  210. isdeleted boolean,
  211. isreadonly boolean,
  212. issystem boolean,
  213. ishidden boolean,
  214. startclu bigint,
  215. cmnt character varying COLLATE pg_catalog."default",
  216. ent double precision,
  217. chisqpercent double precision,
  218. iencryp boolean,
  219. idecryp boolean,
  220. iudecryp boolean,
  221. iscarved boolean,
  222. isbadext boolean,
  223. ifrecycle boolean,
  224. isactual boolean,
  225. isregistrytype boolean,
  226. isthumbsdb boolean,
  227. iwbartifact boolean,
  228. inotkffig boolean,
  229. inotkffigdups boolean,
  230. adscount integer,
  231. bnumber character varying COLLATE pg_catalog."default",
  232. ifrmfrespace boolean,
  233. decryptype integer,
  234. mulmediatype integer,
  235. multimtype character varying COLLATE pg_catalog."default",
  236. hilevelurl character varying COLLATE pg_catalog."default",
  237. chattype character varying COLLATE pg_catalog."default",
  238. httpheader character varying COLLATE pg_catalog."default",
  239. mimetype character varying COLLATE pg_catalog."default",
  240. sourceapp character varying COLLATE pg_catalog."default",
  241. width numeric(10,0),
  242. height numeric(10,0),
  243. allcustodians character varying COLLATE pg_catalog."default",
  244. CONSTRAINT pk_table2 PRIMARY KEY (OID)
  245. )
  246. WITH (
  247. FILLFACTOR = 50,
  248. autovacuum_analyze_scale_factor = 0,
  249. autovacuum_analyze_threshold = 10000,
  250. autovacuum_vacuum_scale_factor = 0,
  251. autovacuum_vacuum_threshold = 10000
  252. )
  253. TABLESPACE schema1_ts;
  254. ALTER TABLE IF EXISTS schema1.table2
  255. OWNER to schema1;
  256. Create Indexes for Table 2:
  257. CREATE INDEX IF NOT EXISTS actualfile_bix
  258. ON schema1.table2 USING btree
  259. (isactual ASC NULLS LAST, OID ASC NULLS LAST)
  260. TABLESPACE schema1_ts;
  261. CREATE INDEX IF NOT EXISTS adscount_idx
  262. ON schema1.table2 USING btree
  263. (adscount ASC NULLS LAST, OID ASC NULLS LAST)
  264. TABLESPACE schema1_ts;
  265. CREATE INDEX IF NOT EXISTS badextension_bix
  266. ON schema1.table2 USING btree
  267. (isbadext ASC NULLS LAST, OID ASC NULLS LAST)
  268. TABLESPACE pg_default;
  269. CREATE INDEX IF NOT EXISTS carved_bix
  270. ON schema1.table2 USING btree
  271. (iscarved ASC NULLS LAST, OID ASC NULLS LAST)
  272. TABLESPACE pg_default;
  273. CREATE INDEX IF NOT EXISTS createddate_idx
  274. ON schema1.table2 USING btree
  275. (creationdateft ASC NULLS LAST, OID ASC NULLS LAST)
  276. TABLESPACE pg_default;
  277. CREATE INDEX IF NOT EXISTS decrypted_bix
  278. ON schema1.table2 USING btree
  279. (idecryp ASC NULLS LAST, OID ASC NULLS LAST)
  280. TABLESPACE pg_default;
  281. CREATE INDEX IF NOT EXISTS decryptedbyuser_bix
  282. ON schema1.table2 USING btree
  283. (iudecryp ASC NULLS LAST, OID ASC NULLS LAST)
  284. TABLESPACE pg_default;
  285. CREATE INDEX IF NOT EXISTS deleted_bix
  286. ON schema1.table2 USING btree
  287. (isdeleted ASC NULLS LAST, OID ASC NULLS LAST)
  288. TABLESPACE schema1_ts;
  289. CREATE INDEX IF NOT EXISTS encrypted_bix
  290. ON schema1.table2 USING btree
  291. (iencryp ASC NULLS LAST, OID ASC NULLS LAST)
  292. TABLESPACE pg_default;
  293. CREATE INDEX IF NOT EXISTS fatlastadateasstr_idx
  294. ON schema1.table2 USING btree
  295. (fatdate COLLATE pg_catalog."default" ASC NULLS LAST, OID ASC NULLS LAST)
  296. TABLESPACE pg_default;
  297. CREATE INDEX IF NOT EXISTS fileclass_bix
  298. ON schema1.table2 USING btree
  299. (fsfileclass ASC NULLS LAST, OID ASC NULLS LAST)
  300. TABLESPACE pg_default;
  301. CREATE INDEX IF NOT EXISTS fromrecyclebin_bix
  302. ON schema1.table2 USING btree
  303. (ifrecycle ASC NULLS LAST, OID ASC NULLS LAST)
  304. TABLESPACE pg_default;
  305. CREATE INDEX IF NOT EXISTS fsid_idx
  306. ON schema1.table2 USING btree
  307. (fsid ASC NULLS LAST, OID ASC NULLS LAST)
  308. TABLESPACE schema1_ts;
  309. CREATE INDEX IF NOT EXISTS hidden_bix
  310. ON schema1.table2 USING btree
  311. (ishidden ASC NULLS LAST, OID ASC NULLS LAST)
  312. TABLESPACE schema1_ts;
  313. CREATE INDEX IF NOT EXISTS ifrmfrespace_bix
  314. ON schema1.table2 USING btree
  315. (ifrmfrespace ASC NULLS LAST, OID ASC NULLS LAST)
  316. TABLESPACE schema1_ts;
  317. CREATE INDEX IF NOT EXISTS isnokffignoreos_bix
  318. ON schema1.table2 USING btree
  319. (inotkffig ASC NULLS LAST, OID ASC NULLS LAST)
  320. TABLESPACE schema1_ts;
  321. CREATE INDEX IF NOT EXISTS isnokffignoreosdup_bix
  322. ON schema1.table2 USING btree
  323. (inotkffigdups ASC NULLS LAST, OID ASC NULLS LAST)
  324. TABLESPACE schema1_ts;
  325. CREATE INDEX IF NOT EXISTS isregistrytype_bix
  326. ON schema1.table2 USING btree
  327. (isregistrytype ASC NULLS LAST, OID ASC NULLS LAST)
  328. TABLESPACE schema1_ts;
  329. CREATE INDEX IF NOT EXISTS isthumbsdb_bix
  330. ON schema1.table2 USING btree
  331. (isthumbsdb ASC NULLS LAST, OID ASC NULLS LAST)
  332. TABLESPACE schema1_ts;
  333. CREATE INDEX IF NOT EXISTS iwbartifact_bix
  334. ON schema1.table2 USING btree
  335. (iwbartifact ASC NULLS LAST, OID ASC NULLS LAST)
  336. TABLESPACE schema1_ts;
  337. CREATE INDEX IF NOT EXISTS last_accesseddate_idx
  338. ON schema1.table2 USING btree
  339. (accessdateft ASC NULLS LAST, OID ASC NULLS LAST)
  340. TABLESPACE pg_default;
  341. CREATE INDEX IF NOT EXISTS modifieddate_idx
  342. ON schema1.table2 USING btree
  343. (modidate ASC NULLS LAST, OID ASC NULLS LAST)
  344. TABLESPACE pg_default;
  345. CREATE INDEX IF NOT EXISTS name_8_3_idx
  346. ON schema1.table2 USING btree
  347. (name_8_3 COLLATE pg_catalog."default" ASC NULLS LAST, OID ASC NULLS LAST)
  348. TABLESPACE pg_default;
  349. CREATE INDEX IF NOT EXISTS physicalsize_idx
  350. ON schema1.table2 USING btree
  351. (physicalsize ASC NULLS LAST, OID ASC NULLS LAST)
  352. TABLESPACE pg_default;
  353. CREATE INDEX IF NOT EXISTS read_only_bix
  354. ON schema1.table2 USING btree
  355. (isreadonly ASC NULLS LAST, OID ASC NULLS LAST)
  356. TABLESPACE schema1_ts;
  357. CREATE INDEX IF NOT EXISTS startclu_idx
  358. ON schema1.table2 USING btree
  359. (startclu ASC NULLS LAST, OID ASC NULLS LAST)
  360. TABLESPACE pg_default;
  361. CREATE INDEX IF NOT EXISTS system_bix
  362. ON schema1.table2 USING btree
  363. (issystem ASC NULLS LAST, OID ASC NULLS LAST)
  364. TABLESPACE schema1_ts;

答案1

得分: 1

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

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

  1. SELECT (tab1.OID) AS tab1_OID_Count
  2. FROM table1 tab1 
  3. left outer JOIN table2 tab2  ON (tab2.OID = tab1.OID)
  4. Where
  5.   (tab1.Folder = false 
  6.    OR (tab1.Folder IS NULL)
  7.    OR (tab2.FSFileClass = 18)
  8.     );

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

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

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

  1. SELECT (tab1.OID) AS tab1_OID_Count
  2. FROM table1 tab1 
  3. left outer JOIN table2 tab2  ON (tab2.OID = tab1.OID)
  4. Where
  5.   (tab1.Folder = false 
  6.    OR (tab1.Folder IS NULL)
  7.     )
  8. union 
  9. SELECT (tab1.OID) AS tab1_OID_Count
  10. FROM table1 tab1 
  11. left outer JOIN table2 tab2  ON (tab2.OID = tab1.OID)
  12. Where
  13.   (tab2.FSFileClass = 18)
  14. ;

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

  1. HashAggregate  (cost=21839.60..24477.54 rows=148095 width
  2.   Group Key: tab1.oid                                  
  3.   Planned Partitions: 4                                
  4.   ->  Append  (cost=0.42..13879.49 rows=148095 width=8)
  5.         ->  Index Only Scan using folder_bix on table1 
  6.               Filter: ((NOT folder) OR (folder IS NULL))
  7.         ->  Merge Join  (cost=0.84..5447.70 rows=6685 wid
  8.               Merge Cond: (tab1_1.oid = tab2.oid)       
  9.               ->  Index Only Scan using pk_table1 on tab
  10.               ->  Index Only Scan using fileclass_bix o
  11.                     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:

  1. SELECT (tab1.OID) AS tab1_OID_Count
  2. FROM table1 tab1 
  3. left outer JOIN table2 tab2  ON (tab2.OID = tab1.OID)
  4. Where
  5. (tab1.Folder = false 
  6. OR (tab1.Folder IS NULL)
  7. OR (tab2.FSFileClass = 18)
  8. );

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

  1. Hash Left Join  (cost=4612.54..11214.84 rows=141821 wid
  2. Hash Cond: (tab1.oid = tab2.oid)                     
  3. Filter: ((NOT tab1.folder) OR (tab1.folder IS NULL) O
  4. ->  Index Only Scan using folder_bix on table1 tab1  
  5. ->  Hash  (cost=2973.72..2973.72 rows=131072 width=12
  6. ->  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:

  1. SELECT (tab1.OID) AS tab1_OID_Count
  2. FROM table1 tab1 
  3. left outer JOIN table2 tab2  ON (tab2.OID = tab1.OID)
  4. Where
  5. (tab1.Folder = false 
  6. OR (tab1.Folder IS NULL)
  7. )
  8. union 
  9. SELECT (tab1.OID) AS tab1_OID_Count
  10. FROM table1 tab1 
  11. left outer JOIN table2 tab2  ON (tab2.OID = tab1.OID)
  12. Where
  13. (tab2.FSFileClass = 18)
  14. ;

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

  1. HashAggregate  (cost=21839.60..24477.54 rows=148095 wid
  2. Group Key: tab1.oid                                  
  3. Planned Partitions: 4                                
  4. ->  Append  (cost=0.42..13879.49 rows=148095 width=8)
  5. ->  Index Only Scan using folder_bix on table1 
  6. Filter: ((NOT folder) OR (folder IS NULL)
  7. ->  Merge Join  (cost=0.84..5447.70 rows=6685 w
  8. Merge Cond: (tab1_1.oid = tab2.oid)      
  9. ->  Index Only Scan using pk_table1 on ta
  10. ->  Index Only Scan using fileclass_bix o
  11. 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:

确定