Using Seq scan even though the column has an Index

Question

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;

when trying to format your question you only need the triple backquotes one time before the code, and one time at the end of your code. (not on every line!) — Luuk, May 29 '23 at 11:41
`OR` is hard work for a database. Instead try using several queries with individual criteria and unioning the results. — Stu, May 29 '23 at 12:01
Yes you're right, OR is too hard. Also, we've tried using UNION but they're not accepting the results. Do we have any other way for simplifying this. If I fixed it then all queries will be resolved. — jptech84, May 29 '23 at 12:06
Complete definition (DDL) for the tables involved might be needed, because my guess is that some of the indexes might have been defined wrong. (The field in the wrong order) — Luuk, May 29 '23 at 12:11
Getting this error, while uploading DDL. It looks like your post is mostly code; please add some more details. — jptech84, May 29 '23 at 12:58

score 1 · Answer 1 · answered May 30 '23 at 13:31

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...)

Thanks a lot for your dedicated responses to my query. After using UNION, I can see the improvements. Now the query is running within 30 seconds for the first time and 12 seconds for the next occurrence. — jptech84, May 31 '23 at 13:26
this is my DB configuration. Is this enough, ALTER SYSTEM SET max_connections = '960'; ALTER SYSTEM SET shared_buffers = '8GB'; ALTER SYSTEM SET effective_cache_size = '4GB'; ALTER SYSTEM SET maintenance_work_mem = '1GB'; ALTER SYSTEM SET checkpoint_completion_target = '0.9'; ALTER SYSTEM SET wal_buffers = '16MB'; ALTER SYSTEM SET default_statistics_target = '100'; ALTER SYSTEM SET random_page_cost = '1.1'; ALTER SYSTEM SET work_mem = '1GB'; ALTER SYSTEM SET min_wal_size = '80MB'; ALTER SYSTEM SET max_wal_size = '1GB'; — jptech84, May 31 '23 at 13:28

Using Seq scan even though the column has an Index

1 Answers1