Re: [SQL] Query becoming slower on adding a primary key
Tom Lane wrote:
[EMAIL PROTECTED] 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
[SQL] UNIQUE columnt depdening on other column???
Hi, I have a problem. Let's say I have the following table: CREATE TABLE rekl_element(id serial PRIMARY KEY,active boolean NOT NULL DEFAULT 'y',num int4 NOT NULL,text varchar(10)); Now I want that "num" column is "unique" but only for those columns that have active='y'. For the columns that have active='f' I don't care if num is unique or not. I'm asking this because num will be doubled some times. How can I write a constraint, trigger function... etc to check this? Thanks in advance. Andy.
Re: [SQL] UNIQUE columnt depdening on other column???
Andrei Bintintan wrote: Hi, I have a problem. Let's say I have the following table: CREATE TABLE rekl_element( id serial PRIMARY KEY, active boolean NOT NULL DEFAULT 'y', num int4 NOT NULL, text varchar(10) ); Now I want that "num" column is "unique" but only for those columns that have active='y'. For the columns that have active='f' I don't care if num is unique or not. I'm asking this because num will be doubled some times. Non-standard but elegant: CREATE UNIQUE INDEX my_partially_unique_index ON rekl_element (num) WHERE active; -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] UNIQUE columnt depdening on other column???
On Wed, Jun 02, 2004 at 02:39:45PM +0300, Andrei Bintintan wrote:
> Hi,
>
> I have a problem.
>
> Let's say I have the following table:
> CREATE TABLE rekl_element(
> id serial PRIMARY KEY,
> active boolean NOT NULL DEFAULT 'y',
> num int4 NOT NULL,
> text varchar(10)
> );
>
> Now I want that "num" column is "unique" but only for those columns that
> have active='y'. For the columns that have active='f' I don't care if num is
> unique or not. I'm asking this because num will be doubled some times.
You can use a little trick for this.
Add a column 'uniqueness' that has a default nextval ('mysequence'::text).
Make an unique index on the columns (num, uniqueness).
Obviously, this will work because the column uniqueness has unique values.
Now write a trigger that sets the uniqueness column to 0 when the active
column equals 'y'. This will result in:
- unique num columns (or the index will fail) where active = 'y'
- arbitrary num colums (index will always be unique) where active = 'n'
--
__
"Nothing is as subjective as reality"
Reinoud van Leeuwen[EMAIL PROTECTED]
http://www.xs4all.nl/~reinoud
__
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] assistance on self join pls
Dear Darren, Your question is not very clear to me. On what columns do you want to aggregate? suppose u want to aggregate on outsite and inside ip you shud group by those columns and run a aggregate function like sum or avg etc , suppose u want the total traffic for every pair you can do this: select inside_ip,outside_ip , sum(outbound_bytes) as total_traffic from connection_data group by inside_ip,outside_ip ; Hope it helps. Regds Mallah. email lists wrote: Hi all, I have the following firewall connection data. datetime | protocol | port | inside_ip| outside_ip | outbound_count | outbound_bytes -+--+---++-- --++--- 2004-05-05 05:00:00 |6 |21 | 192.168.11.191 | 205.227.137.53 | 6 | 3881 2004-05-05 05:00:00 |6 | 22326 | 192.168.11.191 | 205.227.137.53 | 1 | 2592 2004-05-05 05:00:00 |6 | 38005 | 192.168.11.191 | 205.227.137.53 | 1 | 51286 2004-05-05 05:00:00 |6 | 51861 | 192.168.11.191 | 205.227.137.53 | 1 | 42460 2004-05-05 05:00:00 |6 | 52095 | 192.168.11.191 | 205.227.137.53 | 1 | 2558 2004-05-05 05:00:00 |6 | 59846 | 192.168.11.191 | 205.227.137.53 | 1 |118 2004-05-05 05:00:00 |6 | 60243 | 192.168.11.191 | 205.227.137.53 | 1 | 2092 2004-05-05 06:00:00 |6 |21 | 192.168.11.185 | 205.227.137.53 | 6 | 3814 2004-05-05 06:00:00 |6 | 29799 | 192.168.11.185 | 205.227.137.53 | 1 |118 2004-05-05 06:00:00 |6 | 30138 | 192.168.11.185 | 205.227.137.53 | 1 | 2092 2004-05-05 06:00:00 |6 | 30215 | 192.168.11.185 | 205.227.137.53 | 1 | 42460 2004-05-05 06:00:00 |6 | 51279 | 192.168.11.185 | 205.227.137.53 | 1 | 1332 2004-05-05 06:00:00 |6 | 52243 | 192.168.11.185 | 205.227.137.53 | 1 | 51286 2004-05-05 06:00:00 |6 | 60079 | 192.168.11.185 | 205.227.137.53 | 1 | 2558 I am wanting to aggregate / collapse each entry to something similar to: datetime | protocol | port | inside_ip| outside_ip | outbound_count | outbound_bytes -+--+---++-- --++--- 2004-05-05 05:00:00 |6 |21 | 192.168.11.191 | 205.227.137.53 | 12 | 104987 2004-05-05 06:00:00 |6 |21 | 192.168.11.185 | 205.227.137.53 | 12 | 103660 I have not had much success - any assistance greatly appreciated Darren ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] function with a composite type calling another function - Mission Impossible?
Hi. I'm trying to work a fairly complex set of functions that use a composite type for internal usage. This composite type should be passed to a specific function that takes care of some housekeeping actions... I haven't been able to make them work. as an example, I've generated a series of silly functions, to figure out where the problem lies: --- CREATE or replace FUNCTION public.real_to_complex(float8, float8) RETURNS complex_number AS 'declare my_real alias for $1; declare my_complex alias for $2; declare my_result complex_number; begin my_result.real := my_real; my_result.complex := my_complex; return my_result; end;' LANGUAGE 'plpgsql' VOLATILE; CREATE or replace FUNCTION public.complex_to_real(complex_number) RETURNS float8 AS 'declare my_complex alias for $1; declare my_result float; begin my_result := my_complex.real + my_complex.complex; return my_result; end;' LANGUAGE 'plpgsql' VOLATILE; CREATE or replace FUNCTION public.complex_to_complex(complex_number) RETURNS complex_number AS 'declare my_complex alias for $1; declare my_result complex_number; begin my_result.real := my_complex.real * 2; my_result.complex := my_complex.complex * 2; return my_result; end;' LANGUAGE 'plpgsql' VOLATILE; CREATE or replace FUNCTION public.really_complex(float, float) RETURNS complex_number AS 'declare my_real alias for $1; declare my_complex alias for $2; declare my_result complex_number; begin select * from real_to_complex(my_real, my_complex) into my_result; return my_result; end;' LANGUAGE 'plpgsql' VOLATILE; CREATE or replace FUNCTION public.really_really_complex(complex_number) RETURNS complex_number AS 'declare my_complex alias for $1; declare my_new_complex complex_number; declare my_result complex_number; begin select 1, 2 into my_new_complex.real, my_new_complex.complex; my_new_complex.real := my_complex.real; my_new_complex.complex := my_complex.complex; select * from complex_to_complex(my_new_complex) into my_result; return my_result; end;' LANGUAGE 'plpgsql' VOLATILE; -- the following selects work normally: --- select * from real_to_complex(1, 2); select * from complex_to_real(real_to_complex(1, 2)); select * from complex_to_complex(real_to_complex(1, 2)); select * from really_complex(1, 2); --- then, why this one doesn't work? --- select * from really_really_complex(real_to_complex(1, 2)); result: ERROR: column "my_new_complex" does not exist CONTEXT: PL/pgSQL function "really_really_complex" line 13 at select into variables -- I tried some other forms for the function really_really_complex... the answer is always the same: ---version 1 CREATE or replace FUNCTION public.really_really_complex(complex_number) RETURNS complex_number AS 'declare my_complex alias for $1; declare my_result complex_number; begin select * from complex_to_complex(my_complex) into my_result; return my_result; end;' LANGUAGE 'plpgsql' VOLATILE; --- version 2 CREATE or replace FUNCTION public.really_really_complex(complex_number) RETURNS complex_number AS 'declare my_complex alias for $1; declare my_result complex_number; begin my_result := complex_to_complex(my_complex); return my_result; end;' LANGUAGE 'plpgsql' VOLATILE; -- any suggestion/idea? what am I doing wrong? = Riccardo G. Facchini ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] function with a composite type calling another function - Mission Impossible?
"Riccardo G. Facchini" <[EMAIL PROTECTED]> writes: > declare my_new_complex complex_number; > select * from complex_to_complex(my_new_complex) into my_result; > ERROR: column "my_new_complex" does not exist plpgsql doesn't presently cope with passing whole-row variables into SQL expressions, which is essentially what you've got here. There's some chance it will work in time for 7.5. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] function with a composite type calling another function - Mission Impossible?
--- Tom Lane <__> wrote: > "Riccardo G. Facchini" <__> writes: > > declare my_new_complex complex_number; > > > select * from complex_to_complex(my_new_complex) into my_result; > > > ERROR: column "my_new_complex" does not exist > > plpgsql doesn't presently cope with passing whole-row variables into > SQL > expressions, which is essentially what you've got here. There's some > chance it will work in time for 7.5. > > regards, tom lane then, why --- select * from complex_to_real(real_to_complex(1, 2)); select * from complex_to_complex(real_to_complex(1, 2)); --- work? and why the function definition: CREATE or replace FUNCTION public.really_really_complex(complex_number) RETURNS complex_number AS 'declare my_complex alias for $1; declare my_result complex_number; begin my_result := complex_to_complex(my_complex); return my_result; end;' LANGUAGE 'plpgsql' VOLATILE; --- doesn't make a difference? thanks for your quick answer, = Riccardo G. Facchini ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Query becoming slower on adding a primary key
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:
[EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
