I partitioned a table, but didn't find any improvement in query timing.
The basic table was like as follows :-
\d table1
> Table "public.table1_old"
> Column | Type | Modifiers
> --------------+-----------------------------+--------------------
> crmid | integer | not null
> smcreatorid | integer | not null default 0
> smownerid | integer | not null default 0
> modifiedby | integer | not null default 0
> module | character varying(30) | not null
> description | text |
> createdtime | timestamp without time zone | not null
> modifiedtime | timestamp without time zone | not null
> viewedtime | timestamp without time zone |
> status | character varying(50) |
> version | integer | not null default 0
> presence | integer | default 1
> deleted | integer | not null default 0
> Indexes:
> "table1_pkey" PRIMARY KEY, btree (crmid)
> "table1_createdtime_idx" btree (createdtime)
> "table1_modifiedby_idx" btree (modifiedby)
> "table1_modifiedtime_idx" btree (modifiedtime)
> "table1_module_idx" btree (module) WHERE deleted = 0
> "table1_smcreatorid_idx" btree (smcreatorid)
> "table1_smownerid_idx" btree (smownerid)
> "ftx_en_table1_description" gin (to_tsvector('vcrm_en'::regconfig,
> for_fts(description)))
> "table1_deleted_idx" btree (deleted)
\d table2
> Table "public.table2"
> Column | Type |
> Modifiers
>
> -------------------------+------------------------+-------------------------------------------
> table2id | integer | not null default 0
> subject | character varying(250) | not null
> semodule | character varying(20) |
> table2type | character varying(200) | not null
> date_start | date | not null
> due_date | date |
> time_start | character varying(50) |
> time_end | character varying(50) |
> sendnotification | character varying(3) | not null default
> '0'::character varying
> duration_hours | character varying(2) |
> duration_minutes | character varying(200) |
> status | character varying(200) |
> eventstatus | character varying(200) |
> priority | character varying(200) |
> location | character varying(150) |
> notime | character varying(3) | not null default
> '0'::character varying
> visibility | character varying(50) | not null default
> 'all'::character varying
> recurringtype | character varying(200) |
> end_date | date |
> end_time | character varying(50) |
> duration_seconds | integer | not null default 0
> phone | character varying(100) |
> vip_name | character varying(200) |
> is_offline_call | smallint | default 0
> campaign_id | bigint |
> table2_classification | character varying(255) |
> Indexes:
> "table2_pkey" PRIMARY KEY, btree (table2id)
> "table2_table2type_idx" btree (table2type)
> "table2_date_start_idx" btree (date_start)
> "table2_due_date_idx" btree (due_date)
> "table2_eventstatus_idx" btree (eventstatus)
> "table2_status_idx" btree (status)
> "table2_subject_idx" btree (subject)
> "table2_time_start_idx" btree (time_start)
> "ftx_en_table2_subject" gin (to_tsvector('vcrm_en'::regconfig,
> for_fts(subject::text)))
As most of the queries were executed based on module.
select module,count(*) from table1 group by module;
> module | count
> -----------------------+--------
> Leads | 463237
> Calendar | 431041
> Accounts | 304225
> Contacts | 299211
> Emails | 199876
> HelpDesk | 135977
> Potentials | 30826
> Emails Attachment | 28249
> Notes | 1029
> Accounts Attachment | 1015
I paritioned the table based on module. And created index on each separate
tables.
After parition the table structure as follows :-
\d+ table1
> Table "public.table1"
> Column | Type | Modifiers | Storage
> | Description
>
> --------------+-----------------------------+--------------------+----------+-------------
> crmid | integer | not null | plain
> |
> smcreatorid | integer | not null default 0 | plain
> |
> smownerid | integer | not null default 0 | plain
> |
> modifiedby | integer | not null default 0 | plain
> |
> module | character varying(30) | not null |
> extended |
> description | text | |
> extended |
> createdtime | timestamp without time zone | not null | plain
> |
> modifiedtime | timestamp without time zone | not null | plain
> |
> viewedtime | timestamp without time zone | | plain
> |
> status | character varying(50) | |
> extended |
> version | integer | not null default 0 | plain
> |
> presence | integer | default 1 | plain
> |
> deleted | integer | not null default 0 | plain
> |
> Indexes:
> "table1_pkey1" PRIMARY KEY, btree (crmid)
> Child tables: table1_accounts,
> table1_calendar,
> table1_emails,
> table1_helpdesk,
> table1_leads,
> table1_others
> Has OIDs: no
*Without parition :-*
explain analyze
> select *
> from table1 as c
> inner join table2 as a on c.crmid = a.table2id and deleted = 0
> where module ='Leads'
> ;
>
> QUERY PLAN
>
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------------
> Hash Join (cost=93557.89..160291.06 rows=112087 width=506) (actual
> time=4013.152..4013.152 rows=0 loops=1)
> Hash Cond: (a.table2id = c.crmid)
> -> Seq Scan on table2 a (cost=0.00..18337.34 rows=681434 width=139)
> (actual time=0.028..834.189 rows=681434 loops=1)
> -> Hash (cost=73716.32..73716.32 rows=328765 width=367) (actual
> time=1620.810..1620.810 rows=287365 loops=1)
> Buckets: 1024 Batches: 128 Memory Usage: 226kB
> -> Bitmap Heap Scan on table1 c (cost=9489.85..73716.32
> rows=328765 width=367) (actual time=83.092..1144.159 rows=287365 loops=1)
> Recheck Cond: (((module)::text = 'Leads'::text) AND
> (deleted = 0))
> -> Bitmap Index Scan on table1_module_idx
> (cost=0.00..9407.66 rows=328765 width=0) (actual time=79.232..79.232
> rows=287365 loops=1)
> Index Cond: ((module)::text = 'Leads'::text)
> Total runtime: 4013.932 ms
> (10 rows)
*With Parition :- *
>
> explain analyze
>> select *
>> from table1 as c
>> inner join table2 as a on c.crmid = a.table2id and deleted = 0
>> where module ='Leads';
>>
>> QUERY PLAN
>>
>>
>> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>> Hash Join (cost=108101.50..175252.57 rows=313256 width=506) (actual
>> time=8430.588..8430.588 rows=0 loops=1)
>> Hash Cond: (a.table2id = c.crmid)
>> -> Seq Scan on table2 a (cost=0.00..18337.34 rows=681434 width=139)
>> (actual time=0.054..870.554 rows=681434 loops=1)
>> -> Hash (cost=89195.80..89195.80 rows=313256 width=367) (actual
>> time=2751.950..2751.950 rows=287365 loops=1)
>> Buckets: 1024 Batches: 128 Memory Usage: 226kB
>> -> Append (cost=0.00..89195.80 rows=313256 width=367) (actual
>> time=0.034..2304.191 rows=287365 loops=1)
>> -> Seq Scan on table1 c (cost=0.00..89187.53 rows=313255
>> width=367) (actual time=0.032..1783.075 rows=287365 loops=1)
>> Filter: ((deleted = 0) AND ((module)::text =
>> 'Leads'::text))
>> -> Index Scan using table1_leads_deleted_idx on
>> table1_leads c (cost=0.00..8.27 rows=1 width=280) (actual
>> time=0.010..0.010 rows=0 loops=1)
>> Index Cond: (deleted = 0)
>> Filter: ((module)::text = 'Leads'::text)
>> Total runtime: 8432.024 ms
>> (12 rows)
>
>
I set constraint_exclusion to partition.
Why do I need more time with parition?
Any experts please let me know.
