Обсуждение: Why do I need more time with partition table?

Поиск
Список
Период
Сортировка

Why do I need more time with partition table?

От
AI Rumman
Дата:
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.

Re: Why do I need more time with partition table?

От
AI Rumman
Дата:
In addition to the previous mail, I am adding here that -
My Postgresql version is 9.1.2.

And one more thing, executing the following query I got two query plan where the second one looked strange to me.
If showed to take 20950.579 ms, but investigating both the plan I found that it took less time in every step of second plan.

explain analyze
select * 
from table1  as c
inner join table2 as a on c.crmid = a.activityid and deleted = 0
where module ='Leads';
                                                                                 QUERY PLAN                                                                                
 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=108101.50..175252.57 rows=313256 width=506) (actual time=5194.683..5194.683 rows=0 loops=1)
   Hash Cond: (a.activityid = c.crmid)
   ->  Seq Scan on table2 a  (cost=0.00..18337.34 rows=681434 width=139) (actual time=0.062..823.380 rows=681434 loops=1)
   ->  Hash  (cost=89195.80..89195.80 rows=313256 width=367) (actual time=2813.000..2813.000 rows=287365 loops=1)
         Buckets: 1024  Batches: 128  Memory Usage: 226kB
         ->  Append  (cost=0.00..89195.80 rows=313256 width=367) (actual time=0.062..2352.646 rows=287365 loops=1)
               ->  Seq Scan on table1 c  (cost=0.00..89187.53 rows=313255 width=367) (actual time=0.060..1820.331 rows=287365 loops=1)
                     Filter: ((deleted = 0) AND ((module)::text = 'Leads'::text))
               ->  Index Scan using crmentity_leads_deleted_idx on table1_leads c  (cost=0.00..8.27 rows=1 width=280) (actual time=11.076..11.076 rows=0 loops=1)
                     Index Cond: (deleted = 0)
                     Filter: ((module)::text = 'Leads'::text)
 Total runtime: 5195.117 ms
(12 rows)
 
Executing the query again -

\g
                                                                                QUERY PLAN                                                                                
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=108101.50..175252.57 rows=313256 width=506) (actual time=20950.161..20950.161 rows=0 loops=1)
   Hash Cond: (a.activityid = c.crmid)
   ->  Seq Scan on table2 a  (cost=0.00..18337.34 rows=681434 width=139) (actual time=0.092..835.241 rows=681434 loops=1)
   ->  Hash  (cost=89195.80..89195.80 rows=313256 width=367) (actual time=2774.250..2774.250 rows=287365 loops=1)
         Buckets: 1024  Batches: 128  Memory Usage: 226kB
         ->  Append  (cost=0.00..89195.80 rows=313256 width=367) (actual time=0.061..2318.759 rows=287365 loops=1)
               ->  Seq Scan on table1 c  (cost=0.00..89187.53 rows=313255 width=367) (actual time=0.059..1799.937 rows=287365 loops=1)
                     Filter: ((deleted = 0) AND ((module)::text = 'Leads'::text))
               ->  Index Scan using crmentity_leads_deleted_idx on table1_leads c  (cost=0.00..8.27 rows=1 width=280) (actual time=0.011..0.011 rows=0 loops=1)
                     Index Cond: (deleted = 0)
                     Filter: ((module)::text = 'Leads'::text)
 Total runtime: 20950.579 ms
(12 rows)

On Tue, Jul 24, 2012 at 4:42 PM, AI Rumman <rummandba@gmail.com> wrote:
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.

Re: Why do I need more time with partition table?

От
Jan Otto
Дата:
hi al,

> 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=287365loops=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)
(actualtime=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?

it looks like you don't moved your data from base-table to your partitions.

regards, jan

Re: Why do I need more time with partition table?

От
AI Rumman
Дата:
Thanks. I missed to add the trigger.
Now I added it, but still without partition taking less time compared to with partition query.

With partition :- 

explain analyze
select * 
from table1  as c
inner join table2 as a on c.crmid = a.activityid and deleted = 0
where module ='Leads'
;

                                                                      QUERY PLAN                                                                      
------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=25669.79..86440.88 rows=288058 width=367) (actual time=4411.734..4411.734 rows=0 loops=1)
   Hash Cond: (a.activityid = c.crmid)
   ->  Seq Scan on table2 a  (cost=0.00..18337.34 rows=681434 width=139) (actual time=0.264..1336.555 rows=681434 loops=1)
   ->  Hash  (cost=13207.07..13207.07 rows=288058 width=228) (actual time=1457.495..1457.495 rows=287365 loops=1)
         Buckets: 1024  Batches: 128  Memory Usage: 226kB
         ->  Append  (cost=0.00..13207.07 rows=288058 width=228) (actual time=0.014..1000.182 rows=287365 loops=1)
               ->  Seq Scan on table1 c  (cost=0.00..0.00 rows=1 width=367) (actual time=0.001..0.001 rows=0 loops=1)
                     Filter: ((deleted = 0) AND ((module)::text = 'Leads'::text))
               ->  Seq Scan on table1_leads c  (cost=0.00..13207.07 rows=288057 width=228) (actual time=0.010..490.169 rows=287365 loops=1)
                     Filter: ((deleted = 0) AND ((module)::text = 'Leads'::text))
 Total runtime: 4412.534 ms
(11 rows)

Without partition :- 

explain analyze
select * 
from table1_old  as c
inner join table2 as a on c.crmid = a.activityid and deleted = 0
where module ='Leads'
;
                                                                        QUERY PLAN                                                                        
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=92095.07..157111.03 rows=107445 width=502) (actual time=3795.273..3795.273 rows=0 loops=1)
   Hash Cond: (a.activityid = c.crmid)
   ->  Seq Scan on table2 a  (cost=0.00..18337.34 rows=681434 width=139) (actual time=0.030..812.925 rows=681434 loops=1)
   ->  Hash  (cost=73246.44..73246.44 rows=314850 width=363) (actual time=1377.624..1377.624 rows=287365 loops=1)
         Buckets: 1024  Batches: 128  Memory Usage: 226kB
         ->  Bitmap Heap Scan on table1_old c  (cost=9228.69..73246.44 rows=314850 width=363) (actual time=83.189..926.542 rows=287365 loops=1)
               Recheck Cond: (((module)::text = 'Leads'::text) AND (deleted = 0))
               ->  Bitmap Index Scan on crmentity_module_idx  (cost=0.00..9149.98 rows=314850 width=0) (actual time=79.357..79.357 rows=287365 loops=1)
                     Index Cond: ((module)::text = 'Leads'::text)
 Total runtime: 3795.721 ms
(10 rows)


On Tue, Jul 24, 2012 at 5:46 PM, Jan Otto <asche@me.com> wrote:
hi al,

> 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?

it looks like you don't moved your data from base-table to your partitions.

regards, jan


Re: Why do I need more time with partition table?

От
Jan Otto
Дата:
hi al,

On Jul 25, 2012, at 10:40 AM, AI Rumman <rummandba@gmail.com> wrote:

> Thanks. I missed to add the trigger.
> Now I added it, but still without partition taking less time compared to with partition query.
>
> With partition :-
>
> explain analyze
> select *
> from table1  as c
> inner join table2 as a on c.crmid = a.activityid and deleted = 0
> where module ='Leads'
> ;
>
>                                                                       QUERY PLAN
                                
>
------------------------------------------------------------------------------------------------------------------------------------------------------
>  Hash Join  (cost=25669.79..86440.88 rows=288058 width=367) (actual time=4411.734..4411.734 rows=0 loops=1)
>    Hash Cond: (a.activityid = c.crmid)
>    ->  Seq Scan on table2 a  (cost=0.00..18337.34 rows=681434 width=139) (actual time=0.264..1336.555 rows=681434
loops=1)
>    ->  Hash  (cost=13207.07..13207.07 rows=288058 width=228) (actual time=1457.495..1457.495 rows=287365 loops=1)
>          Buckets: 1024  Batches: 128  Memory Usage: 226kB
>          ->  Append  (cost=0.00..13207.07 rows=288058 width=228) (actual time=0.014..1000.182 rows=287365 loops=1)
>                ->  Seq Scan on table1 c  (cost=0.00..0.00 rows=1 width=367) (actual time=0.001..0.001 rows=0 loops=1)
>                      Filter: ((deleted = 0) AND ((module)::text = 'Leads'::text))
>                ->  Seq Scan on table1_leads c  (cost=0.00..13207.07 rows=288057 width=228) (actual
time=0.010..490.169rows=287365 loops=1) 
>                      Filter: ((deleted = 0) AND ((module)::text = 'Leads'::text))
>  Total runtime: 4412.534 ms
> (11 rows)

did you have analyze'd your tables? try if indexing column deleted on table1_leads gives you some more speed.

regards, jan

Re: Why do I need more time with partition table?

От
Jeff Janes
Дата:
On Wed, Jul 25, 2012 at 1:40 AM, AI Rumman <rummandba@gmail.com> wrote:
> Thanks. I missed to add the trigger.
> Now I added it, but still without partition taking less time compared to
> with partition query.

Based on the different times on "Seq Scan on table2", it looks like
one query has better caching than the other.

Did you try running the queries in alternating order, to average out
caching effects?

Could you run the "explain (analyze, buffers)" on those to get a
better picture of the buffer effects?

Cheers,

Jeff