Re: [SQL] "SELECT IN" Still Broken in 7.4b
On Wed, 20 Aug 2003, Stephan Szabo wrote: > > On Wed, 20 Aug 2003, Rod Taylor wrote: > > > > Thanks, Stephan. I was really hoping that the IN(valuelist) was going to > > > be changed at the same time, because it really is unusable for anything > > > over a couple of thousand values. > > > > Changed to do what? > > One possibility might be to act as if the valuelist was a table and do the > IN as if it were that way, rather than treating it as a set of ORs. That > would be basically like doing the temporary table solution, but without > requiring the user to do it. > for integers we use contrib/intarray as a workaround. In principle, it's possible to extend intarray to general array. > > > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] "SELECT IN" Still Broken in 7.4b
Stephan Szabo wrote: On Wed, 20 Aug 2003, Rod Taylor wrote: ... Is the temp table version any faster? I realize it has a higher limit to the number of items you can have in the list. Within the scope of the new hashed IN stuff I believe so in at least some cases. I have a few million row table of integers where searching for values IN (~1 values) takes longer than creating the temp table, copying into it and doing the in subquery. That's not a particularly meaningful test case, but sending the psql output to /dev/null gives me: ... But where do your values come from in the first place? Couldn't you optimize your model so that you don't have to copy around such amounts of data? Regards, Dani ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] "SELECT IN" Still Broken in 7.4b
On Thu, 21 Aug 2003, Dani Oderbolz wrote: > Stephan Szabo wrote: > > >On Wed, 20 Aug 2003, Rod Taylor wrote: > >... > > > > > >>Is the temp table version any faster? I realize it has a higher limit > >>to the number of items you can have in the list. > >> > >> > > > >Within the scope of the new hashed IN stuff I believe so in at least some > >cases. I have a few million row table of integers where searching for > >values IN (~1 values) takes longer than creating the temp table, > >copying into it and doing the in subquery. That's not a particularly > >meaningful test case, but sending the psql output to /dev/null gives me: ... > > > But where do your values come from in the first place? > Couldn't you optimize your model so that you don't have to copy around > such amounts of data? I wasn't the OP, I was doing a simple test as a comparison between the two forms of the queries to see if making a temp table and populating it and then doing the subselect form could ever be faster than the current conversion for valuelists to a sequence of or conditions. In 7.3, it probably was not possible for a conversion to in subselect to be faster, but with the new hash subquery stuff it was worth trying again. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] logging messages from inside pgPLSQL routine?
I'mm trying to debug something inside my PLSQL routine. How do I print out error messages from inside my function? Thanks, Drew ---(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] logging messages from inside pgPLSQL routine?
On Thu, Aug 21, 2003 at 05:21:47PM -0700, Drew Wilson wrote: > I'mm trying to debug something inside my PLSQL routine. How do I print > out error messages from inside my function? RAISE NOTICE ''Foobar is %'', foobar; Where "foobar" is a variable. The documentation mentions this in several places and examples. -Roberto -- +|Roberto Mello -http://www.brasileiro.net/ |--+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + Compiling...Linking...Dialing Copyright Lawyer... ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] date calculation
Hi there, I have a problem calculating a date. A field carries the date as passed seconds since Jan 1st 1970. How can I get the date as dd.mm. out of this?? Thanks a lot Michael ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] "SELECT IN" Still Broken in 7.4b
On Wed, Aug 20, 2003 at 04:32:19PM -0400, Tom Lane wrote:
>
> against actual elapsed time (cf psql's \timing option) would tell.
What is measured by the \timing option? The figures reported
are slightly larger than those loged when the log_duration parameter
is true.
=> select count(*) from foo;
count
362921
(1 row)
Time: 5500.88 ms
while in syslog, the timing was "LOG: duration: 5.499783 sec". There
is a difference of 1.097 ms.
In both cases what is this duration? Real time or CPU time?
--
%!PS
297.6 420.9 translate 90 rotate 0 setgray gsave 0 1 1{pop 0 180 moveto 100
180 170 100 170 -10 curveto 180 -9 180 -9 190 -10 curveto 190 100 100 180
0 180 curveto fill 180 rotate}for grestore/Bookman-LightItalic findfont
240 scalefont setfont -151.536392 -63.7998886 moveto (bp)show showpage
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] date calculation
Hi there,
I have a problem calculating a date. A field carries the date as passed
seconds since Jan 1st 1970.
How can I get the date as dd.mm. out of this??
cast('1970-1-1' as timestamp)+cast(your_ticks || ' seconds' as interval)
Does anyone know better way to cast it?
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 8: explain analyze is your friend
