Re: [PERFORM] : PostgreSQL Index behavior

2012-09-11 Thread Venkat Balaji
Thank you Jeff ! My comments are inline. > explain SELECT tv.short_code, tv.chn as pkg_subscription_chn, > >tv.vert as pkg_vert, ubs.campaign_id as campaign, > > 'none'::varchar as referer, > >CAST('CAMPAIGNWISE_SUBSCRIBER_BASE' AS VARCHAR) as vn, > > count(tv.msi

Re: [PERFORM] Planner selects different execution plans depending on limit

2012-09-11 Thread Tom Lane
Bill Martin writes: > I´ve created following table which contains one million records. > ... > "Limit (cost=10091.09..19305.68 rows=3927 width=621) (actual > time=0.255..0.255 rows=0 loops=1)" > " -> Bitmap Heap Scan on core_content content (cost=10091.09..57046.32 > rows=20011 width=621)

Re: [PERFORM] add column with default value is very slow

2012-09-11 Thread Andrew Dunstan
On 09/11/2012 09:55 AM, AI Rumman wrote: I added the excel file for locks data. I was surprised to see that while I was updating a single column value for all records in a tables, all indexes are locked by the server. Any ALTER TABLE command locks the whole table in ACCESS EXCLUSIVE mode, i

Re: [PERFORM] add column with default value is very slow

2012-09-11 Thread hubert depesz lubaczewski
On Tue, Sep 11, 2012 at 08:04:06PM +0600, AI Rumman wrote: > Table size is 1186 MB. if it takes long, it just means that your IO is slow. > I split the command in three steps as you said, but the result same during > the update operation. three? I was showing four steps, and one of them is usual

Re: [PERFORM] add column with default value is very slow

2012-09-11 Thread hubert depesz lubaczewski
On Tue, Sep 11, 2012 at 07:55:24PM +0600, AI Rumman wrote: > I added the excel file for locks data. well, it worked, but why didn't you just make it text file, in notepad or something like this? > I was surprised to see that while I was updating a single column value for > all records in a tables

Re: [PERFORM] add column with default value is very slow

2012-09-11 Thread AI Rumman
Table size is 1186 MB. I split the command in three steps as you said, but the result same during the update operation. One more thing, I have just restored the db from dump and analyzed it and I am using Postgresql 9.1 with 3 GB Ram with dual core machine. On Tue, Sep 11, 2012 at 7:59 PM, hubert

Re: [PERFORM] add column with default value is very slow

2012-09-11 Thread AI Rumman
I added the excel file for locks data. I was surprised to see that while I was updating a single column value for all records in a tables, all indexes are locked by the server. On Tue, Sep 11, 2012 at 7:44 PM, hubert depesz lubaczewski < dep...@depesz.com> wrote: > On Tue, Sep 11, 2012 at 07:20:2

Re: [PERFORM] add column with default value is very slow

2012-09-11 Thread hubert depesz lubaczewski
On Tue, Sep 11, 2012 at 07:20:28PM +0600, AI Rumman wrote: > I have a table as follows: > I execued the query: > ALTER TABLE entity ADD COLUMN owner_type char(1) NOT NULL default 'U'; > > The db is stuck. The enity table has 2064740 records; such alter table has to rewrite whole table. So it will

Re: [PERFORM] add column with default value is very slow

2012-09-11 Thread Albe Laurenz
AI Rumman wrote: > I execued the query: > ALTER TABLE entity ADD COLUMN owner_type char(1) NOT NULL default 'U'; > > The db is stuck. The enity table has 2064740 records; > > Watching locks: [all locks are granted] > Any idea for the db stuck? To add the column, PostgreSQL has to modify all row

[PERFORM] add column with default value is very slow

2012-09-11 Thread AI Rumman
I have a table as follows: \d entity Table "public.entity" Column|Type | Modifiers --+-+ crmid| integer | not null smcreatorid | integer

Re: [PERFORM] Planner selects different execution plans depending on limit

2012-09-11 Thread Bill Martin
On 10/09/12 16:24, bill_mar...@freenet.de wrote: Hi All I´ve ft_simple_core_content_content_idx ON core_content USING gin (to_tsvector('simple'::regconfig, content) ); If I´m seaching for a word which is NOT in the column content the query plan and the exec