Thread: Query becoming slower on adding a primary key
Hi ,
After adding a primary key in one of the participant tables
the query never finishes. The live table has a primary key
so it cannot be removed. I made a copy of the live table
using create table t_a as select * from tab. the query works
fine . when i ad the pkey like i have in the live table it does
not work. Can anyone please help me with this problem?
below are the details.
thanks in advance.
Regds
Mallah.
explain analyze select email_id ,email ,contact from t_a a join
email_source f using(email_id) join email_subscriptions h using(email_id)
where 1=1 and f.source_id =1 and h.sub_id = 3 ; QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------Hash
Join (cost=134818.15..221449.12 rows=306921 width=40) (actual
time=9457.000..17700.227 rows=283763 loops=1) Hash Cond: ("outer".email_id = "inner".email_id) -> Seq Scan on
email_subscriptionsh (cost=0.00..70323.77 rows=746257
width=4) (actual time=0.054..3434.639 rows=746721 loops=1) Filter: (sub_id = 3) -> Hash
(cost=131485.92..131485.92rows=308491 width=44) (actual
time=9456.757..9456.757 rows=0 loops=1) -> Hash Join (cost=26878.00..131485.92 rows=308491 width=44)
(actual time=2293.378..8978.407 rows=299873 loops=1) Hash Cond: ("outer".email_id = "inner".email_id)
-> Seq Scan on email_source f (cost=0.00..26119.84
rows=308491 width=4) (actual time=0.123..1094.661
rows=317504 loops=1) Filter: (source_id = 1) -> Hash (cost=18626.80..18626.80
rows=800080width=40)
(actual time=2275.979..2275.979 rows=0 loops=1) -> Seq Scan on t_a a (cost=0.00..18626.80
rows=800080 width=40) (actual time=0.009..1297.728
rows=800080 loops=1)Total runtime: 17856.763 ms
(12 rows)
tradein_clients=# ALTER TABLE t_a add primary key (email_id);
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index
"t_a_pkey" for table "t_a"
ALTER TABLE
Time: 6322.116 ms
tradein_clients=# VACUUM analyze t_a;
VACUUM
Time: 809.840 ms
tradein_clients=# explain analyze select email_id ,email ,contact from
t_a a join email_source f using(email_id) join email_subscriptions h
using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3 ;
Runs for Ever.
ROW COUNTS:
t_a : 8,00,080
email_source: 15,45,056
email_subscriptions: 40,41,133
Structures:
tradein_clients=# \d email_source Table "public.email_source" Column | Type | Modifiers
-----------+---------+-----------email_id | integer | not nullsource_id | integer | not null
Indexes: "email_source_pkey" primary key, btree (email_id, source_id) "email_source_sid" btree (source_id)
Foreign-key constraints: "$1" FOREIGN KEY (source_id) REFERENCES sources(source_id) ON UPDATE
CASCADE ON DELETE CASCADE
\d t_a Table "public.t_a" Column | Type | Modifiers
-----------+--------------------------+-----------email_id | integer | not nulluserid | integer
|email | character varying(100) |country | character varying(100) |city | character
varying(50) |contact | character varying(100) |last_sent | timestamp with time zone |pref | character
varying(1) |website | character varying(255) |address | text |
\d email_subscriptions
Table "public.email_subscriptions" Column | Type | Modifiers
----------+---------+-----------email_id | integer | not nullsub_id | integer | not null
Indexes: "email_subscriptions_pkey" primary key, btree (email_id, sub_id)
mallah@trade-india.com writes:
> tradein_clients=# explain analyze select email_id ,email ,contact from
> t_a a join email_source f using(email_id) join email_subscriptions h
> using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3 ;
> Runs for Ever.
So what does plain explain say about it?
regards, tom lane
<br /> Tom Lane wrote: <blockquote cite="mid16609.1086125809@sss.pgh.pa.us" type="cite"><pre wrap=""><a
class="moz-txt-link-abbreviated"href="mailto:mallah@trade-india.com">mallah@trade-india.com</a> writes:
</pre><blockquotetype="cite"><pre wrap="">tradein_clients=# explain analyze select email_id ,email ,contact from
t_a a join email_source f using(email_id) join email_subscriptions h
using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3 ; </pre></blockquote><pre wrap=""> </pre><blockquote
type="cite"><prewrap="">Runs for Ever. </pre></blockquote><pre wrap="">
So what does plain explain say about it? </pre></blockquote> Oops sorry that was a valuable info i left. (sorry for
delaytoo)<br /><small><br /><tt>tradein_clients=# explain select email_id ,email ,contact from t_a a join
email_sourcef using(email_id) join email_subscriptions h <br /> using(email_id) where 1=1 and f.source_id =1 and
h.sub_id= 3 ;<br /> +-----------------------------------------------------------------------------------------+<br />
| QUERY PLAN |<br />
+-----------------------------------------------------------------------------------------+<br/> | Hash Join
(cost=133741.48..224746.39rows=328814 width=40) |<br /> | Hash Cond: ("outer".email_id =
"inner".email_id) |<br /> | -> Seq Scan on email_subscriptions h
(cost=0.00..70329.54rows=749735 width=4) |<br /> | Filter: (sub_id =
3) |<br /> | -> Hash (cost=130230.99..130230.99
rows=324994width=44) |<br /> | -> Hash Join (cost=26878.00..130230.99
rows=324994width=44) |<br /> | Hash Cond: ("outer".email_id =
"inner".email_id) |<br /> | -> Seq Scan on email_source f
(cost=0.00..26159.21rows=324994 width=4) |<br /> | Filter: (source_id =
1) |<br /> | -> Hash (cost=18626.80..18626.80 rows=800080
width=40) |<br /> | -> Seq Scan on t_a a (cost=0.00..18626.80 rows=800080
width=40) |<br /> +-----------------------------------------------------------------------------------------+<br />
(11rows)<br /><br /> Time: 452.417 ms<br /> tradein_clients=# ALTER TABLE t_a add primary key(email_id);<br /> NOTICE:
ALTERTABLE / ADD PRIMARY KEY will create implicit index "t_a_pkey" for table "t_a"<br /> ALTER TABLE<br /> Time:
7923.230ms<br /> tradein_clients=# explain select email_id ,email ,contact from t_a a join email_source f
using(email_id)join email_subscriptions <br /> h using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3 ;<br />
+-------------------------------------------------------------------------------------------------------------------+<br
/>| QUERY PLAN
|<br/>
+-------------------------------------------------------------------------------------------------------------------+<br
/>| Hash Join (cost=106819.76..197824.68 rows=328814 width=40)
|<br/> | Hash Cond: ("outer".email_id =
"inner".email_id) |<br /> | -> Seq Scan on
email_subscriptionsh (cost=0.00..70329.54 rows=749735 width=4) |<br /> |
Filter:(sub_id = 3) |<br /> |
-> Hash (cost=103309.28..103309.28 rows=324994 width=44) |<br
/>| -> Merge Join (cost=0.00..103309.28 rows=324994
width=44) |<br /> | Merge Cond: ("outer".email_id =
"inner".email_id) |<br /> | -> Index Scan using
t_a_pkeyon t_a a (cost=0.00..44689.59 rows=800080 width=40) |<br /> | -> Index Scan
usingemail_source_pkey on email_source f (cost=0.00..52602.59 rows=324994 width=4) |<br /> |
Filter:(source_id = 1) |<br />
+-------------------------------------------------------------------------------------------------------------------+<br
/>(10 rows)<br /><br /> Time: 2436.551 ms<br /> tradein_clients=#</tt></small><br /><br /><br /><br /> Regds<br />
Mallah.<br/><br /><br /><blockquote cite="mid16609.1086125809@sss.pgh.pa.us" type="cite"><pre wrap=""> regards,
tomlane
</pre></blockquote><br />
Even the first query used to run fine before but one fine day
it changed plans i think.
Regds
Mallah.
Rajesh Kumar Mallah wrote:
>
> Tom Lane wrote:
>
>>mallah@trade-india.com writes:
>>
>>
>>>tradein_clients=# explain analyze select email_id ,email ,contact from
>>>t_a a join email_source f using(email_id) join email_subscriptions h
>>>using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3 ;
>>>
>>>
>>
>>
>>
>>>Runs for Ever.
>>>
>>>
>>
>>So what does plain explain say about it?
>>
>>
> Oops sorry that was a valuable info i left. (sorry for delay too)
>
> tradein_clients=# explain select email_id ,email ,contact from t_a a
> join email_source f using(email_id) join email_subscriptions h
> using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3 ;
> +-----------------------------------------------------------------------------------------+
> | QUERY
> PLAN |
> +-----------------------------------------------------------------------------------------+
> | Hash Join (cost=133741.48..224746.39 rows=328814
> width=40) |
> | Hash Cond: ("outer".email_id =
> "inner".email_id) |
> | -> Seq Scan on email_subscriptions h (cost=0.00..70329.54
> rows=749735 width=4) |
> | Filter: (sub_id =
> 3) |
> | -> Hash (cost=130230.99..130230.99 rows=324994
> width=44) |
> | -> Hash Join (cost=26878.00..130230.99 rows=324994
> width=44) |
> | Hash Cond: ("outer".email_id =
> "inner".email_id) |
> | -> Seq Scan on email_source f (cost=0.00..26159.21
> rows=324994 width=4) |
> | Filter: (source_id =
> 1) |
> | -> Hash (cost=18626.80..18626.80 rows=800080
> width=40) |
> | -> Seq Scan on t_a a (cost=0.00..18626.80
> rows=800080 width=40) |
> +-----------------------------------------------------------------------------------------+
> (11 rows)
>
> Time: 452.417 ms
> tradein_clients=# ALTER TABLE t_a add primary key(email_id);
> NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index
> "t_a_pkey" for table "t_a"
> ALTER TABLE
> Time: 7923.230 ms
> tradein_clients=# explain select email_id ,email ,contact from t_a a
> join email_source f using(email_id) join email_subscriptions
> h using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3 ;
>
+-------------------------------------------------------------------------------------------------------------------+
> | QUERY
> PLAN |
>
+-------------------------------------------------------------------------------------------------------------------+
> | Hash Join (cost=106819.76..197824.68 rows=328814
> width=40) |
> | Hash Cond: ("outer".email_id =
> "inner".email_id)
> |
> | -> Seq Scan on email_subscriptions h (cost=0.00..70329.54
> rows=749735 width=4) |
> | Filter: (sub_id =
> 3)
> |
> | -> Hash (cost=103309.28..103309.28 rows=324994
> width=44) |
> | -> Merge Join (cost=0.00..103309.28 rows=324994
> width=44) |
> | Merge Cond: ("outer".email_id =
> "inner".email_id) |
> | -> Index Scan using t_a_pkey on t_a a
> (cost=0.00..44689.59 rows=800080 width=40) |
> | -> Index Scan using email_source_pkey on email_source
> f (cost=0.00..52602.59 rows=324994 width=4) |
> | Filter: (source_id =
> 1) |
>
+-------------------------------------------------------------------------------------------------------------------+
> (10 rows)
>
> Time: 2436.551 ms
> tradein_clients=#
>
>
>
> Regds
> Mallah.
>
>
>> regards, tom lane
>>
>>
>>
>
Hi,
Is there any solution to this issue ? I am facing it every week.
Warm Regds
Mallah.
Rajesh Kumar Mallah wrote:
>
> Tom Lane wrote:
>
>>mallah@trade-india.com writes:
>>
>>
>>>tradein_clients=# explain analyze select email_id ,email ,contact from
>>>t_a a join email_source f using(email_id) join email_subscriptions h
>>>using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3 ;
>>>
>>>
>>
>>
>>
>>>Runs for Ever.
>>>
>>>
>>
>>So what does plain explain say about it?
>>
>>
> Oops sorry that was a valuable info i left. (sorry for delay too)
>
> tradein_clients=# explain select email_id ,email ,contact from t_a a
> join email_source f using(email_id) join email_subscriptions h
> using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3 ;
> +-----------------------------------------------------------------------------------------+
> | QUERY
> PLAN |
> +-----------------------------------------------------------------------------------------+
> | Hash Join (cost=133741.48..224746.39 rows=328814
> width=40) |
> | Hash Cond: ("outer".email_id =
> "inner".email_id) |
> | -> Seq Scan on email_subscriptions h (cost=0.00..70329.54
> rows=749735 width=4) |
> | Filter: (sub_id =
> 3) |
> | -> Hash (cost=130230.99..130230.99 rows=324994
> width=44) |
> | -> Hash Join (cost=26878.00..130230.99 rows=324994
> width=44) |
> | Hash Cond: ("outer".email_id =
> "inner".email_id) |
> | -> Seq Scan on email_source f (cost=0.00..26159.21
> rows=324994 width=4) |
> | Filter: (source_id =
> 1) |
> | -> Hash (cost=18626.80..18626.80 rows=800080
> width=40) |
> | -> Seq Scan on t_a a (cost=0.00..18626.80
> rows=800080 width=40) |
> +-----------------------------------------------------------------------------------------+
> (11 rows)
>
> Time: 452.417 ms
> tradein_clients=# ALTER TABLE t_a add primary key(email_id);
> NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index
> "t_a_pkey" for table "t_a"
> ALTER TABLE
> Time: 7923.230 ms
> tradein_clients=# explain select email_id ,email ,contact from t_a a
> join email_source f using(email_id) join email_subscriptions
> h using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3 ;
>
+-------------------------------------------------------------------------------------------------------------------+
> | QUERY
> PLAN |
>
+-------------------------------------------------------------------------------------------------------------------+
> | Hash Join (cost=106819.76..197824.68 rows=328814
> width=40) |
> | Hash Cond: ("outer".email_id =
> "inner".email_id)
> |
> | -> Seq Scan on email_subscriptions h (cost=0.00..70329.54
> rows=749735 width=4) |
> | Filter: (sub_id =
> 3)
> |
> | -> Hash (cost=103309.28..103309.28 rows=324994
> width=44) |
> | -> Merge Join (cost=0.00..103309.28 rows=324994
> width=44) |
> | Merge Cond: ("outer".email_id =
> "inner".email_id) |
> | -> Index Scan using t_a_pkey on t_a a
> (cost=0.00..44689.59 rows=800080 width=40) |
> | -> Index Scan using email_source_pkey on email_source
> f (cost=0.00..52602.59 rows=324994 width=4) |
> | Filter: (source_id =
> 1) |
>
+-------------------------------------------------------------------------------------------------------------------+
> (10 rows)
>
> Time: 2436.551 ms
> tradein_clients=#
>
>
>
> Regds
> Mallah.
>
>
>> regards, tom lane
>>
>>
>>
>
<br /> HI,<br /><br /> The problem was solved by reducing the effective_cache_size from 102400 to 10240<br /> my total
RAMis 4GB.<br /><br /><br /> Regds<br /> mallah.<br /><br /> Tom Lane wrote: <blockquote
cite="mid16609.1086125809@sss.pgh.pa.us"type="cite"><pre wrap=""><a class="moz-txt-link-abbreviated"
href="mailto:mallah@trade-india.com">mallah@trade-india.com</a>writes: </pre><blockquote type="cite"><pre
wrap="">tradein_clients=#explain analyze select email_id ,email ,contact from
t_a a join email_source f using(email_id) join email_subscriptions h
using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3 ; </pre></blockquote><pre wrap=""> </pre><blockquote
type="cite"><prewrap="">Runs for Ever. </pre></blockquote><pre wrap="">
So what does plain explain say about it?
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
<a class="moz-txt-link-freetext"
href="http://archives.postgresql.org">http://archives.postgresql.org</a>
</pre></blockquote><br />