Re: [SQL] usernames of a group from SQL
On Friday May 19 2006 12.04, Achilleus Mantzios wrote: > > SELECT u.usename from pg_user u,pg_group g where u.usesysid = any > (g.grolist) and g.groname='osztatlan_users'; > I'd like to make a function with this SQL: CREATE FUNCTION kodok.group_users(group_name "varchar") RETURNS SETOF "varchar" AS $BODY$ SELECT pg_user.usename FROM pg_user, pg_group WHERE usesysid = any (pg_group.grolist) AND pg_group.groname=$1; $BODY$ LANGUAGE 'sql' VOLATILE; But I get the next error message: ERROR: return type mismatch in function declared to return character varying DETAIL: Actual return type is name. CONTEXT: SQL function "group_users" At the moment, I don't understand what is the problem, and what I need to do now...? Thanks, kjt Nagyobb szabadsαgra vαgysz? Tφrj ki a nιgy fal kφzόl! Start ADSL elυfizetιsedhez az EuroWeb mostantσl havi 100 perc ingyenes WiFi hozzαfιrιst biztosνt szαmodra. Rιszletek: www.freestart.hu ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] usernames of a group from SQL
Hi, Kijato, kijato schrieb: > CREATE FUNCTION kodok.group_users(group_name "varchar") > RETURNS SETOF "varchar" AS > $BODY$ > SELECT pg_user.usename > FROM pg_user, pg_group > WHERE usesysid = any (pg_group.grolist) AND pg_group.groname=$1; > $BODY$ > LANGUAGE 'sql' VOLATILE; The pg_user.username column uses the PostgreSQL internal type "name" which is used for identifiers like table and user names. The PostgreSQL Documentation contains more about this. I think you can cast it to varchar, like: SELECT pg_user.username::varchar HTH, Markus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] timestamp query doesn't use an index ...
"Marc G. Fournier" <[EMAIL PROTECTED]> writes: > I'm trying to figure out some way to speed up the following query: >select ps2.page_id, ps2.template_component_id, max(ps2.start_time) > from page_schedule ps2 > where ps2.start_time at time zone 'MST7MDT' <= '2006-5-17 8:9:18' > group by ps2.page_id, ps2.template_component_id > Is there some other way I can either write above query *or* do an index, > such that it will use the index? One-sided inequalities frequently *shouldn't* use an index, because they're retrieving too much of the table. Are you sure this is fetching only a small fraction of the table? Are you using PG 8.1 (8.1 would be likely to try to use a bitmap indexscan for this)? You could experiment with enable_seqscan = off to see if the planner is actually wrong about its choice. If so, reducing random_page_cost might be the best permanent solution. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] timestamp query doesn't use an index ...
On Sun, 21 May 2006, Tom Lane wrote:
"Marc G. Fournier" <[EMAIL PROTECTED]> writes:
I'm trying to figure out some way to speed up the following query:
select ps2.page_id, ps2.template_component_id, max(ps2.start_time)
from page_schedule ps2
where ps2.start_time at time zone 'MST7MDT' <= '2006-5-17 8:9:18'
group by ps2.page_id, ps2.template_component_id
Is there some other way I can either write above query *or* do an index,
such that it will use the index?
One-sided inequalities frequently *shouldn't* use an index, because
they're retrieving too much of the table. Are you sure this is fetching
only a small fraction of the table? Are you using PG 8.1 (8.1 would be
likely to try to use a bitmap indexscan for this)?
You could experiment with enable_seqscan = off to see if the planner is
actually wrong about its choice. If so, reducing random_page_cost might
be the best permanent solution.
vrnprd=# select version();
version
PostgreSQL 8.1.3 on i386-portbld-freebsd6.1, compiled by GCC cc (GCC) 3.4.4
[FreeBSD] 20050518
(1 row)
vrnprd=# set enable_seqscan = off;
SET
vrnprd=# explain analyze
select ps2.page_id, ps2.template_component_id, max(ps2.start_time)
from page_schedule ps2
where ps2.start_time at time zone 'MST7MDT' <= '2006-5-17 8:9:18'
group by ps2.page_id, ps2.template_component_id
;
QUERY PLAN
-
HashAggregate (cost=3509.96..3513.50 rows=283 width=16) (actual
time=839.460..839.769 rows=128 loops=1)
-> Bitmap Heap Scan on page_schedule ps2 (cost=573.65..2822.86 rows=91614
width=16) (actual time=149.788..505.438 rows=94798 loops=1)
Recheck Cond: (timezone('MST7MDT'::text, start_time) <= '2006-05-17
08:09:18'::timestamp without time zone)
-> Bitmap Index Scan on start_time_page_schedule (cost=0.00..573.65
rows=91614 width=0) (actual time=127.761..127.761 rows=94798 loops=1)
Index Cond: (timezone('MST7MDT'::text, start_time) <=
'2006-05-17 08:09:18'::timestamp without time zone)
Total runtime: 846.604 ms
(6 rows)
vrnprd=#
And yup, it is definitely returning just 128 rows out of the 93k or so:
110 | 419 | 2005-10-26 13:15:00-03
130 | 215 | 2006-04-26 10:15:00-03
(128 rows)
And, not sure how far to reduce random_page_cost, but it was 4 ... tried 2
and 1, and both come up with the same results ... with seqscan enabled, it
does a seqscan :(
I suspected with the <= there wasn't going to be much I could do with
this, but figured I'd make sure there wasn't something that I was
overlooking :(
Thx ...
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED]
Yahoo . yscrappy Skype: hub.orgICQ . 7615664
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] timestamp query doesn't use an index ...
"Marc G. Fournier" <[EMAIL PROTECTED]> writes:
> -> Bitmap Heap Scan on page_schedule ps2 (cost=573.65..2822.86
> rows=91614 width=16) (actual time=149.788..505.438 rows=94798 loops=1)
> Recheck Cond: (timezone('MST7MDT'::text, start_time) <= '2006-05-17
> 08:09:18'::timestamp without time zone)
> -> Bitmap Index Scan on start_time_page_schedule
> (cost=0.00..573.65 rows=91614 width=0) (actual time=127.761..127.761
> rows=94798 loops=1)
> Index Cond: (timezone('MST7MDT'::text, start_time) <=
> '2006-05-17 08:09:18'::timestamp without time zone)
> And yup, it is definitely returning just 128 rows out of the 93k or so:
No, the scan is pulling 94798 rows from the table, according to the
EXPLAIN ANALYZE --- the number of resulting groups isn't much of a
factor here.
We don't currently have any index optimization for MIN/MAX in a GROUP BY
context, and even if we did, it wouldn't apply here: the planner
couldn't assume that the sort order of an index on "start_time at time
zone 'MST7MDT'" would have anything to do with the ordering of just
"start_time". Is there a reason you're writing
where ps2.start_time at time zone 'MST7MDT' <= '2006-5-17 8:9:18'
and not
where ps2.start_time <= '2006-5-17 8:9:18' at time zone 'MST7MDT'
The latter seems less likely to have strange behaviors near DST
transitions. I don't think it'll be any faster at the moment, but you
could at least save maintaining a specialized index.
regards, tom lane
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
