Re: [PERFORM] Why do I need more time with partition table?
On Wed, Jul 25, 2012 at 1:40 AM, AI Rumman 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Why do I need more time with partition table?
hi al, On Jul 25, 2012, at 10:40 AM, AI Rumman 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.169 rows=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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Why do I need more time with partition table?
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 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: [PERFORM] Why do I need more time with partition table?
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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Why do I need more time with partition table?
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 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
>>
>> -+---
[PERFORM] Why do I need more time with partition table?
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
