[SQL] Why doesn't this work
I am running a query like so SELECT id FROM image WHERE image='demo-harvey wallbanger.jpg' It returns nothing My table looks like this "demo-820.jpg";1 "demo-lemon-mousse-1.jpg";2 "demo-pumpkinchaibars.jpg";3 "demo-Lolly-Shop.jpg";4 "demo-scan0001.jpg";5 "demo-cherry-chocolate-mousse-pie.jpg";6 "demo-harvey wallbanger.jpg ";7 Cheers Barbara
Re: [SQL] Why doesn't this work
Hi Barbara, from what I see in your msg, you have a /cr at the end of the filename. You should check for weird stuff and trim it away (before insert triggers do wonders at this). Cheers Bèrto On 15 January 2013 11:50, Barbara Woolums wrote: > I am running a query like so > > SELECT id FROM image WHERE image='demo-harvey wallbanger.jpg' > > It returns nothing > > My table looks like this > > "demo-820.jpg";1 > "demo-lemon-mousse-1.jpg";2 > "demo-pumpkinchaibars.jpg";3 > "demo-Lolly-Shop.jpg";4 > "demo-scan0001.jpg";5 > "demo-cherry-chocolate-mousse-pie.jpg";6 > "demo-harvey wallbanger.jpg > ";7 > > > Cheers > Barbara -- == If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Why doesn't this work
Tirsdag 15. januar 2013 12.50.00 skrev Barbara Woolums : > I am running a query like so > > SELECT id FROM image WHERE image='demo-harvey wallbanger.jpg' > > It returns nothing > > My table looks like this > > "demo-820.jpg";1 > "demo-lemon-mousse-1.jpg";2 > "demo-pumpkinchaibars.jpg";3 > "demo-Lolly-Shop.jpg";4 > "demo-scan0001.jpg";5 > "demo-cherry-chocolate-mousse-pie.jpg";6 > "demo-harvey wallbanger.jpg > ";7 It seems like you've got a newline at the end of the first field of the last row. Does it work if you change the query to SELECT id FROM image WHERE image LIKE '%demo-harvey wallbanger.jpg%' ? regards, Leif -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Why doesn't this work
On 01/15/2013 04:50 AM, Barbara Woolums wrote: I am running a query like so SELECT id FROM image WHERE image='demo-harvey wallbanger.jpg' It returns nothing My table looks like this "demo-820.jpg";1 "demo-lemon-mousse-1.jpg";2 "demo-pumpkinchaibars.jpg";3 "demo-Lolly-Shop.jpg";4 "demo-scan0001.jpg";5 "demo-cherry-chocolate-mousse-pie.jpg";6 "demo-harvey wallbanger.jpg ";7 Cheers Barbara Try select id from image where image.image ~'demo-harvey wallbanger'; If you example data is correct, I think you have a line-feed char after '.jpg' -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] returning values from dynamic SQL to a variable
I have a related problem and tried the PERFORM...EXECUTE pattern suggested but no matter where I put PERFORM I get 'function not found' errors. I want to loop through id values returned by a query and execute another with each i as a parameter. Each subquery will return 6-8 rows. This is a simplified example, in the real app the subquery is doing some aggregation work. Tried many many things including this pattern below and read everything I could find, but no go. Any help appreciated. create or replace function getRowsA() returns setof record as $$ declare r record; loopy record; i integer; sql text; begin for r in select * from cities loop i := r.id; sql := 'select city,topic,weight from v_doctopic where city = ' || i; EXECUTE sql; return next loopy; end loop; return; end; $$ language 'plpgsql'; select * from getRowsA() AS foo(city int, topic int, weight numeric) - karlg -- View this message in context: http://postgresql.1045698.n5.nabble.com/returning-values-from-dynamic-SQL-to-a-variable-tp5723322p5740324.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Curious problem of using BETWEEN with start and end being the same versus EQUALS '='
All,
On 9.1, I am running into a curious issue. I will explain the issue in high
level terms using psuedo SQL statements. Consider a SQL statement:
SELECT a, b, c FROM tab WHERE a = value1;
- This does an index scan followed by a merge join and takes about 37 secs to
execute
If I change the query to:
SELECT a, b, c FROM tab WHERE a BETWEEN value1 AND value1; -- The start and end
are the same
- This query does a index scan and nested loop join and takes forever to run
(as a matter of fact, it has never finished even after 8+ hours)
My actual query:
First Run with BETWEEN clause range of 4 days (Execution time = about 5 mins)
select a.date_id Date,
a.page_group page_group,
a.page page,
... --{snipped for brevity}
from bi2003.alps_agg a left outer join bi2003.event_agg b
on (a.date_id = b.date_id and upper(a.adc_visit) =
upper(bicommon.get_value('adc_visit', b.vcset)))
WHERE a.date_id between 20120228 and 20120302
and b.date_id between 20120228 and 20120302
and a.page = 'ddi_671'
group by 1,2,3,4,5,6,7,8,9;
Explain Analyze:
"GroupAggregate (cost=1214313.29..1923536.69 rows=274096 width=3040) (actual
time=275946.288..312348.732 rows=861 loops=1)"
" Output: a.date_id, a.page_group, a.page, a.int_alloc_type,
(bicommon.get_value('browserfamily'::text, b.vcset)),
(bicommon.get_value('trafficsource'::text, b.vcset)),
(bicommon.get_value('e671_hl_p1'::text, a.componentset)),
(bicommon.get_value('e671_img_p1'::text, a.componentset)),
(bicommon.get_value('e671_formLabels'::text, a.componentset)),
count(a.adc_visit), sum((bicommon.get_value('revenue'::text,
b.eventvalueset))::numeric(9,0)), sum((bicommon.get_value('Impression'::text,
b.eventcountset))::numeric(9,0)), sum((bicommon.get_value('page1submit'::text,
b.eventcountset))::numeric(9,0)), sum((bicommon.get_value('conversion'::text,
b.eventcountset))::numeric(9,0)), sum((bicommon.get_value('lead'::text,
b.eventcountset))::numeric(9,0))"
" -> Sort (cost=1214313.29..1214998.53 rows=274096 width=3040) (actual
time=275785.085..293169.601 rows=147480 loops=1)"
"Output: a.date_id, a.page_group, a.page, a.int_alloc_type, b.vcset,
a.componentset, a.adc_visit, b.eventvalueset, b.eventcountset,
(bicommon.get_value('browserfamily'::text, b.vcset)),
(bicommon.get_value('trafficsource'::text, b.vcset)),
(bicommon.get_value('e671_hl_p1'::text, a.componentset)),
(bicommon.get_value('e671_img_p1'::text, a.componentset)),
(bicommon.get_value('e671_formLabels'::text, a.componentset))"
"Sort Key: a.date_id, a.page_group, a.page, a.int_alloc_type,
(bicommon.get_value('browserfamily'::text, b.vcset)),
(bicommon.get_value('trafficsource'::text, b.vcset)),
(bicommon.get_value('e671_hl_p1'::text, a.componentset)),
(bicommon.get_value('e671_img_p1'::text, a.componentset)),
(bicommon.get_value('e671_formLabels'::text, a.componentset))"
"Sort Method: external merge Disk: 447168kB"
"-> Merge Join (cost=380263.82..830740.00 rows=274096 width=3040)
(actual time=81438.225..257963.708 rows=147480 loops=1)"
" Output: a.date_id, a.page_group, a.page, a.int_alloc_type,
b.vcset, a.componentset, a.adc_visit, b.eventvalueset, b.eventcountset,
bicommon.get_value('browserfamily'::text, b.vcset),
bicommon.get_value('trafficsource'::text, b.vcset),
bicommon.get_value('e671_hl_p1'::text, a.componentset),
bicommon.get_value('e671_img_p1'::text, a.componentset),
bicommon.get_value('e671_formLabels'::text, a.componentset)"
" Merge Cond: (((upper((a.adc_visit)::text)) =
(upper(bicommon.get_value('adc_visit'::text, b.vcset AND (a.date_id =
b.date_id))"
" -> Sort (cost=169041.20..169352.99 rows=124717 width=1350)
(actual time=15181.446..25806.356 rows=147480 loops=1)"
"Output: a.date_id, a.page_group, a.page, a.int_alloc_type,
a.componentset, a.adc_visit, (upper((a.adc_visit)::text))"
"Sort Key: (upper((a.adc_visit)::text)), a.date_id"
"Sort Method: external merge Disk: 205824kB"
"-> Index Scan using alps_agg_date_id on bi2003.alps_agg a
(cost=0.00..85163.47 rows=124717 width=1350) (actual time=28.843..11369.048
rows=147480 loops=1)"
" Output: a.date_id, a.page_group, a.page,
a.int_alloc_type, a.componentset, a.adc_visit, upper((a.adc_visit)::text)"
" Index Cond: ((a.date_id >= 20120228) AND (a.date_id
<= 20120302))"
" Filter: ((a.page)::text = 'ddi_671'::text)"
" -> Materialize (cost=211222.61..212076.23 rows=170723
width=1694) (actual time=66254.779..80862.998 rows=187870 loops=1)"
"Output: b.vcset, b.eventvalueset, b.eventcountset,
b.date_id, (upper(bicommon.get_value('adc_visit'::text, b.vcset)))"
"-> Sort (cost=211222.61..211649.42 rows=170723
width=1694) (actual time=66254.773..80680.870 rows=187870 loops=1)"
"
Re: [SQL] returning values from dynamic SQL to a variable
Hello you can use RETURN QUERY EXECUTE statement http://www.postgresql.org/docs/9.1/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING Regards Pavel Stehule 2013/1/15 kgeographer : > I have a related problem and tried the PERFORM...EXECUTE pattern suggested > but no matter where I put PERFORM I get 'function not found' errors. > > I want to loop through id values returned by a query and execute another > with each i as a parameter. Each subquery will return 6-8 rows. This is a > simplified example, in the real app the subquery is doing some aggregation > work. > > Tried many many things including this pattern below and read everything I > could find, but no go. Any help appreciated. > > > create or replace function getRowsA() returns setof record as $$ > declare > r record; > loopy record; > i integer; > sql text; > begin > for r in select * from cities loop > i := r.id; > sql := 'select city,topic,weight from v_doctopic where city = ' || i; > EXECUTE sql; > return next loopy; > end loop; > return; > end; > $$ language 'plpgsql'; > > select * from getRowsA() AS foo(city int, topic int, weight numeric) > > > > - > karlg > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/returning-values-from-dynamic-SQL-to-a-variable-tp5723322p5740324.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Curious problem of using BETWEEN with start and end being the same versus EQUALS '='
Venky Kandaswamy writes: >On 9.1, I am running into a curious issue. It's not very curious at all, or at least people on pgsql-performance (the right list for this sort of question) would have figured it out quickly. You're getting a crummy plan because of a crummy row estimate. When you do this: > WHERE a.date_id = 20120228 you get this: > "-> Index Scan using alps_agg_date_id on bi2003.alps_agg > a (cost=0.00..17870.00 rows=26292 width=1350) (actual time=0.047..142.383 > rows=36132 loops=1)" > " Output: a.date_id, a.page_group, a.page, > a.int_alloc_type, a.componentset, a.adc_visit, upper((a.adc_visit)::text)" > " Index Cond: (a.date_id = 20120228)" > " Filter: ((a.page)::text = 'ddi_671'::text)" 26K estimated rows versus 36K actual isn't the greatest estimate in the world, but it's plenty good enough. But when you do this: > WHERE a.date_id BETWEEN 20120228 AND 20120228 you get this: > "-> Index Scan using alps_agg_date_id on bi2003.alps_agg a > (cost=0.00..10.12 rows=1 width=1350)" > " Output: a.date_id, a.adc_visit, a.page_group, a.page, > a.int_alloc_type, a.componentset, a.variation_tagset, a.page_instance" > " Index Cond: ((a.date_id >= 20120228) AND (a.date_id <= > 20120228))" > " Filter: ((a.page)::text = 'ddi_671'::text)" so the bogus estimate of only one row causes the planner to pick an entirely different plan, which would probably be a great choice if there were indeed only one such row, but with 36000 of them it's horrid. The reason the row estimate is so crummy is that a zero-width interval is an edge case for range estimates. We've seen this before, although usually it's not quite this bad. There's been some talk of making the estimate for "x >= a AND x <= b" always be at least as much as the estimate for "x = a", but this would increase the cost of making the estimate by quite a bit, and make things actually worse in some cases (in particular, if a > b then a nil estimate is indeed the right thing). You might look into whether queries formed like "date_id >= 20120228 AND date_id < 20120229" give you more robust estimates at the edge cases. BTW, I notice in your EXPLAIN results that the same range restriction has been propagated to b.date_id: > "-> Index Scan using event_agg_date_id on bi2003.event_agg b > (cost=0.00..10.27 rows=1 width=1694)" > " Output: b.date_id, b.vcset, b.eventcountset, b.eventvalueset" > " Index Cond: ((b.date_id >= 20120228) AND (b.date_id <= > 20120228))" I'd expect that to happen automatically for a simple equality constraint, but not for a range constraint. Did you do that manually and not tell us about it? regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Curious problem of using BETWEEN with start and end being the same versus EQUALS '='
Thanks for the quick and detailed response, Tom. Yes, I did add a redundant where clause with a restriction on b.date_id on the range queries. This appears to speed things up since it does an index scan on the b table before the merge join. We will get more intelligent on query generation (our system generates queries on the fly) to work around this problem. Venky Kandaswamy Principal Engineer, Adchemy Inc. 925-200-7124 From: Tom Lane [t...@sss.pgh.pa.us] Sent: Tuesday, January 15, 2013 2:30 PM To: Venky Kandaswamy Cc: pgsql-gene...@postgresql.org; pgsql-sql@postgresql.org Subject: Re: [SQL] Curious problem of using BETWEEN with start and end being the same versus EQUALS '=' Venky Kandaswamy writes: >On 9.1, I am running into a curious issue. It's not very curious at all, or at least people on pgsql-performance (the right list for this sort of question) would have figured it out quickly. You're getting a crummy plan because of a crummy row estimate. When you do this: > WHERE a.date_id = 20120228 you get this: > "-> Index Scan using alps_agg_date_id on bi2003.alps_agg > a (cost=0.00..17870.00 rows=26292 width=1350) (actual time=0.047..142.383 > rows=36132 loops=1)" > " Output: a.date_id, a.page_group, a.page, > a.int_alloc_type, a.componentset, a.adc_visit, upper((a.adc_visit)::text)" > " Index Cond: (a.date_id = 20120228)" > " Filter: ((a.page)::text = 'ddi_671'::text)" 26K estimated rows versus 36K actual isn't the greatest estimate in the world, but it's plenty good enough. But when you do this: > WHERE a.date_id BETWEEN 20120228 AND 20120228 you get this: > "-> Index Scan using alps_agg_date_id on bi2003.alps_agg a > (cost=0.00..10.12 rows=1 width=1350)" > " Output: a.date_id, a.adc_visit, a.page_group, a.page, > a.int_alloc_type, a.componentset, a.variation_tagset, a.page_instance" > " Index Cond: ((a.date_id >= 20120228) AND (a.date_id <= > 20120228))" > " Filter: ((a.page)::text = 'ddi_671'::text)" so the bogus estimate of only one row causes the planner to pick an entirely different plan, which would probably be a great choice if there were indeed only one such row, but with 36000 of them it's horrid. The reason the row estimate is so crummy is that a zero-width interval is an edge case for range estimates. We've seen this before, although usually it's not quite this bad. There's been some talk of making the estimate for "x >= a AND x <= b" always be at least as much as the estimate for "x = a", but this would increase the cost of making the estimate by quite a bit, and make things actually worse in some cases (in particular, if a > b then a nil estimate is indeed the right thing). You might look into whether queries formed like "date_id >= 20120228 AND date_id < 20120229" give you more robust estimates at the edge cases. BTW, I notice in your EXPLAIN results that the same range restriction has been propagated to b.date_id: > "-> Index Scan using event_agg_date_id on bi2003.event_agg b > (cost=0.00..10.27 rows=1 width=1694)" > " Output: b.date_id, b.vcset, b.eventcountset, b.eventvalueset" > " Index Cond: ((b.date_id >= 20120228) AND (b.date_id <= > 20120228))" I'd expect that to happen automatically for a simple equality constraint, but not for a range constraint. Did you do that manually and not tell us about it? regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] returning values from dynamic SQL to a variable
Pavel - RETURN QUERY EXECUTE worked, many thanks for responding so quickly. The docs show no relevant examples, so for anyone else, something like this create or replace function getRowsE( OUT element character(1), OUT name character varying(100), OUT sum numeric ) returns setof record as $BODY$ declare r record; i integer; usesql text; begin for r in select * from mytable where id is not null order by id loop i := r.graphid; usesql := 'bunch of sql where ' || i || 'something or other, producing element, name, sum'; RETURN QUERY EXECUTE usesql; end loop; return; end; $BODY$ language 'plpgsql'; On 1/15/2013 10:23 AM, Pavel Stehule wrote: Hello you can use RETURN QUERY EXECUTE statement http://www.postgresql.org/docs/9.1/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING Regards Pavel Stehule 2013/1/15 kgeographer : I have a related problem and tried the PERFORM...EXECUTE pattern suggested but no matter where I put PERFORM I get 'function not found' errors. I want to loop through id values returned by a query and execute another with each i as a parameter. Each subquery will return 6-8 rows. This is a simplified example, in the real app the subquery is doing some aggregation work. Tried many many things including this pattern below and read everything I could find, but no go. Any help appreciated. create or replace function getRowsA() returns setof record as $$ declare r record; loopy record; i integer; sql text; begin for r in select * from cities loop i := r.id; sql := 'select city,topic,weight from v_doctopic where city = ' || i; EXECUTE sql; return next loopy; end loop; return; end; $$ language 'plpgsql'; select * from getRowsA() AS foo(city int, topic int, weight numeric) - karlg -- View this message in context: http://postgresql.1045698.n5.nabble.com/returning-values-from-dynamic-SQL-to-a-variable-tp5723322p5740324.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
