[SQL] datediff script
This is a not-quite complete implementation of the SY/MS sql datediff. The months_between function can be extrapolated from it as well. I looked for it on forums, etc. and all I found were people complaining about the lack of an example. Please post fixes/changes or a link to a better one... if you know of it. CREATE OR REPLACE FUNCTION public.datediff(varchar, timestamp, timestamp) RETURNS int4 AS ' DECLARE arg_mode alias for $1; arg_d2 alias for $2; arg_d1 alias for $3; BEGIN if arg_mode = \'dd\' or arg_mode = \'d\' or arg_mode = \'y\' or arg_mode = \'dy\' or arg_mode = \'w\' then return cast(arg_d1 as date) - cast(arg_d2 as date); elsif arg_mode = \'ww\' then return ceil( ( cast(arg_d1 as date) - cast(arg_d2 as date) ) / 7.0); elsif arg_mode = \'mm\' OR arg_mode = \'m\' then return 12 * (date_part(\'year\',arg_d1) - date_part(\'year\',arg_d2)) + date_part(\'month\',arg_d1) - date_part(\'month\',arg_d2) + case when date_part(\'day\',arg_d1) > date_part(\'day\',arg_d2) then 0 when date_part(\'day\',arg_d1) = date_part(\'day\',arg_d2) and cast(arg_d1 as time) >= cast(arg_d2 as time) then 0 else -1 end; elsif arg_mode = \'yy\' OR arg_mode = \'y\' OR arg_mode = \'\' then return (cast(arg_d1 as date) - cast(arg_d2 as date)) / 365; end if; END; ' LANGUAGE 'plpgsql' VOLATILE; ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Putting an INDEX on a boolean field?
In my database, the "sites" table is large, and the "usersites" table
has only a few sites per userid - so it should be looked in first. I'm
surprised that I had to juggle my query around (below), rather than
trusting the optimizer to figure this out for me.
Should I start looking to figure out why the optimizer didn't figure out
that it should be doing this sort of thing? Or should I just keep
juggling with subselects, since it's not that hard to do.
This query results in a sequential scan:
select sites.*, blocks from sites left join quota on
sites.host_u = quota.host and quota.date = ('2005-06-16 23:55:05-04')
where sites.id in (select siteid from usersites where userid = 1);
versus this one which does not:
explain analyze select sites.*, blocks from (select * from sites
where id in (select siteid from usersites where userid = 1)) as sites
leftjoin quota on sites.host_u = quota.host and quota.date =
('2005-06-1623:55:05-04');
The tables have been vacuumed/analyzed, etc.
---slow---
explain analyze select sites.*, blocks from sites left join quota on
sites.host_u = quota.host and quota.date = ('2005-06-16 23:55:05-04')
where sites.id in (select siteid from usersites where userid = 1);
---
Hash IN Join (cost=3183.30..3295.50 rows=13 width=158) (actual
time=4865.895..4942.097 rows=10 loops=1)
Hash Cond: ("outer".id = "inner".siteid)
-> Merge Left Join (cost=3173.52..3263.12 rows=4493 width=158)
(actual time=4856.212..4939.329 rows=4443 loops=1)
Merge Cond: ("outer"."?column24?" = "inner".host)
-> Sort (cost=2786.62..2797.72 rows=4443 width=154) (actual
time=4811.499..4816.164 rows=4443 loops=1)
Sort Key: (sites.host_u)::text
-> Seq Scan on sites (cost=0.00..2517.43 rows=4443
width=154) (actual time=11.611..4598.849 rows=4443 loops=1)
-> Sort (cost=386.91..398.13 rows=4489 width=26) (actual
time=44.638..46.307 rows=4454 loops=1)
Sort Key: quota.host
-> Index Scan using quota_date_idx on quota
(cost=0.00..114.60 rows=4489 width=26) (actual time=0.069..10.780
rows=4453 loops=1)
Index Cond: (date = '2005-06-16
23:55:05-04'::timestamp with time zone)
-> Hash (cost=9.75..9.75 rows=12 width=4) (actual time=0.086..0.086
rows=0 loops=1)
-> Index Scan using usersites_userid_idx on usersites
(cost=0.00..9.75 rows=12 width=4) (actual time=0.047..0.076 rows=10
loops=1)
Index Cond: (userid = 1)
Total runtime: 4944.575 ms
(15 rows)
---fast---
explain analyze select sites.*, blocks from (select * from sites where
id in (select siteid from usersites where userid = 1)) as sites left
join quota on sites.host_u = quota.host and quota.date = ('2005-06-16
23:55:05-04');
---
Merge Left Join (cost=468.77..491.41 rows=13 width=158) (actual
time=46.547..53.669 rows=10 loops=1)
Merge Cond: ("outer"."?column24?" = "inner".host)
-> Sort (cost=81.86..81.89 rows=12 width=154) (actual
time=0.450..0.454 rows=10 loops=1)
Sort Key: (public.sites.host_u)::text
-> Nested Loop (cost=9.78..81.65 rows=12 width=154) (actual
time=0.129..0.392 rows=10 loops=1)
-> HashAggregate (cost=9.78..9.78 rows=12 width=4)
(actual time=0.084..0.095 rows=10 loops=1)
-> Index Scan using usersites_userid_idx on
usersites (cost=0.00..9.75 rows=12 width=4) (actual time=0.040..0.067
rows=10 loops=1)
Index Cond: (userid = 1)
-> Index Scan using sites_pkey on sites
(cost=0.00..5.98 rows=1 width=154) (actual time=0.017..0.018 rows=1
loops=10)
Index Cond: (sites.id = "outer".siteid)
-> Sort (cost=386.91..398.13 rows=4489 width=26) (actual
time=44.971..46.042 rows=3741 loops=1)
Sort Key: quota.host
-> Index Scan using quota_date_idx on quota
(cost=0.00..114.60 rows=4489 width=26) (actual time=0.025..10.643
rows=4453 loops=1)
Index Cond: (date = '2005-06-16 23:55:05-04'::timestamp
with time zone)
Total runtime: 54.988 ms
(15 rows)
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
