pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery becoming slower on adding a primary key
6+ messages / 3 participants
[nested] [flat]
* Query becoming slower on adding a primary key
@ 2004-06-01 19:21 mallah@trade-india.com
2004-06-01 21:36 ` Re: Query becoming slower on adding a primary key Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: mallah@trade-india.com @ 2004-06-01 19:21 UTC (permalink / raw)
To: pgsql-sql
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_subscriptions h (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.92 rows=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=800080 width=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 null
source_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 null
userid | 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 null
sub_id | integer | not null
Indexes:
"email_subscriptions_pkey" primary key, btree (email_id, sub_id)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query becoming slower on adding a primary key
2004-06-01 19:21 Query becoming slower on adding a primary key mallah@trade-india.com
@ 2004-06-01 21:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-06-02 08:50 ` Re: Query becoming slower on adding a primary key Rajesh Kumar Mallah <mallah@trade-india.com>
2004-06-11 04:59 ` Re: Query becoming slower on adding a primary key [ SOLVED Rajesh Kumar Mallah <mallah@trade-india.com>
0 siblings, 2 replies; 6+ messages in thread
From: Tom Lane @ 2004-06-01 21:36 UTC (permalink / raw)
To: mallah@trade-india.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query becoming slower on adding a primary key
2004-06-01 19:21 Query becoming slower on adding a primary key mallah@trade-india.com
2004-06-01 21:36 ` Re: Query becoming slower on adding a primary key Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-06-02 08:50 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2004-06-03 05:03 ` Re: Query becoming slower on adding a primary key Rajesh Kumar Mallah <mallah@trade-india.com>
2004-06-08 18:41 ` Re: Query becoming slower on adding a primary key Rajesh Kumar Mallah <mallah@trade-india.com>
1 sibling, 2 replies; 6+ messages in thread
From: Rajesh Kumar Mallah @ 2004-06-02 08:50 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query becoming slower on adding a primary key
2004-06-01 19:21 Query becoming slower on adding a primary key mallah@trade-india.com
2004-06-01 21:36 ` Re: Query becoming slower on adding a primary key Tom Lane <tgl@sss.pgh.pa.us>
2004-06-02 08:50 ` Re: Query becoming slower on adding a primary key Rajesh Kumar Mallah <mallah@trade-india.com>
@ 2004-06-03 05:03 ` Rajesh Kumar Mallah <mallah@trade-india.com>
1 sibling, 0 replies; 6+ messages in thread
From: Rajesh Kumar Mallah @ 2004-06-03 05:03 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
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
>>
>>
>>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query becoming slower on adding a primary key
2004-06-01 19:21 Query becoming slower on adding a primary key mallah@trade-india.com
2004-06-01 21:36 ` Re: Query becoming slower on adding a primary key Tom Lane <tgl@sss.pgh.pa.us>
2004-06-02 08:50 ` Re: Query becoming slower on adding a primary key Rajesh Kumar Mallah <mallah@trade-india.com>
@ 2004-06-08 18:41 ` Rajesh Kumar Mallah <mallah@trade-india.com>
1 sibling, 0 replies; 6+ messages in thread
From: Rajesh Kumar Mallah @ 2004-06-08 18:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
>>
>>
>>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query becoming slower on adding a primary key [ SOLVED
2004-06-01 19:21 Query becoming slower on adding a primary key mallah@trade-india.com
2004-06-01 21:36 ` Re: Query becoming slower on adding a primary key Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-06-11 04:59 ` Rajesh Kumar Mallah <mallah@trade-india.com>
1 sibling, 0 replies; 6+ messages in thread
From: Rajesh Kumar Mallah @ 2004-06-11 04:59 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql; joseph.turner@oakleynetworks.com
HI,
The problem was solved by reducing the effective_cache_size from 102400
to 10240
my total RAM is 4GB.
Regds
mallah.
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?
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-06-11 04:59 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-01 19:21 Query becoming slower on adding a primary key mallah@trade-india.com
2004-06-01 21:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-06-02 08:50 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2004-06-03 05:03 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2004-06-08 18:41 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2004-06-11 04:59 ` Re: Query becoming slower on adding a primary key [ SOLVED Rajesh Kumar Mallah <mallah@trade-india.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox