Re: [SQL] Which one is faster?
Jan Wieck wrote: > Bruce Momjian wrote: > > > > Wei Weng wrote: > > > Between Using Limit P, S or using a cursor to start from FETCH ABSOLUTE > > > S and FETCH NEXT for P times, which one is faster? > > > > LIMIT is faster because it doesn't need to generate all the result, in > > some cases. > > Neither does using a cursor need to generate all the results first. It's > a portal, which is an executor state on hold, and FETCH is implemented > as ExecutorRun() with the number of wanted result rows as limitation. > Since LIMIT generates internally one more result row than really wanted, > I don't think LIMIT would be much faster, but it's for sure less > portable. Oh, I thought the portal had to be materialized before returning a row. Thanks. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Tagging rows into collections?
On Wednesday 19 Jun 2002 10:19 pm, Tom Lane wrote: > Steve Wampler <[EMAIL PROTECTED]> writes: > > An event has: timestamp,event_name,list_of_attributes > >The list_of_attributes are simple (string) name,value pairs. > > > > However, although selection performance isn't a priority, the > > ability to reconstruct the events from the database is needed > > and the above simple table doesn't provide enough information > > to do so. (The resolution on the timestamp field isn't > > enough to distinquish separate events that have the same name.) > > What PG version are you using? In 7.2 the default timestamp resolution > is microseconds, rather than seconds. That might be enough to fix your > problem. Still doesn't *guarantee* uniqueness though, just makes it less likely. > If not, your two-table approach sounds reasonable. You could stick > with one table and use arrays for the name/value columns, but that > will make searches harder. How about using a sequence to generate unique numbers for you? Looks like a SERIAL type won't be much use, but a sequence can used without tying it to a field. One thing to be careful of - if you have multiple clients inserting then the numbers won't necessarily be in order. That is, client 1 might insert 10,11,12,13 and client 2 20,21,22,23 but in time-order they might be 10,11,20,12,22,23,13. This is because each client will get a batch of numbers to use (for efficiency reasons). Be aware that I'm not 100% certain on that last sentence. - Richard Huxton ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] how to sort a birthday list ?
Hi ! there's a nice query for retrieving the people who have their birthday in the next n days: (taken from Joe Celko's SQL for Smarties, 1st Ed., p. 76) SELECT * FROM Persons WHERE EXTRACT( YEAR FROM AGE(dateofbirth) ) < EXTRACT( YEAR FROM AGE( CURRENT_DATE+60, dateofbirth ) ) ... but how do I sort the list ? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] how to sort a birthday list ?
On Thu, 2002-06-20 at 13:55, Michael Agbaglo wrote: > Hi ! > > there's a nice query for retrieving the people who have their birthday > in the next n days: (taken from Joe Celko's SQL for Smarties, 1st Ed., > p. 76) > > SELECT * > FROM Persons > WHERE EXTRACT( YEAR FROM AGE(dateofbirth) ) < EXTRACT( YEAR FROM AGE( > CURRENT_DATE+60, dateofbirth ) ) > > ... but how do I sort the list ? How about: ORDER BY dateofbirth signature.asc Description: This is a digitally signed message part
Re: [SQL] SQL performance issue with PostgreSQL compared to
Achilleus, > > > The pgsql db is indexed on the > > > 'authuser' field, and I've run 'analyze logfile'. > > > > He also needs to index the bytes field and the traffic field. And run > ^^^ > > Index on an aggregate function??? > > I though we could only index plain normal column iscachable functions. No, you're correct. I meant just to index on the bytes field. -- -Josh Berkus ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] how to sort a birthday list ?
Michael, > SELECT * > FROM Persons > WHERE EXTRACT( YEAR FROM AGE(dateofbirth) ) < EXTRACT( YEAR FROM AGE( > CURRENT_DATE+60, dateofbirth ) ) > > ... but how do I sort the list ? Easy: SELECT person_name, person_department, EXTRACT( YEAR FROM AGE(dateofbirth) ) as their_age FROM Persons WHERE EXTRACT( YEAR FROM AGE(dateofbirth) ) < EXTRACT( YEAR FROM AGE( CURRENT_DATE+60, dateofbirth ) ) ORDER BY their_age, person_name As an example. -- -Josh Berkus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] SQL Challenge: Skip Weekends
Folks, Hey, I need to write a date calculation function that calculates the date after a number of *workdays* from a specific date. I pretty much have the "skip holidays" part nailed down, but I don't have a really good way to skip all weekends in the caluclation. Here's the ideas I've come up with: Idea #1: Use a reference table 1. Using a script, generate a table of all weekends from 2000 to 2050. 2. Increase the interval by the number of weekends that fall in the relevant period. Idea #2: Some sort of calculation using 5/7 of the interval, adjusted according to the day of the week of our starting date. My head hurts trying to figure this one out. -- -Josh Berkus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] SQL Challenge: Skip Weekends
Josh Berkus wrote:
> Folks,
>
> Hey, I need to write a date calculation function that calculates the
> date after a number of *workdays* from a specific date. I pretty
> much have the "skip holidays" part nailed down, but I don't have a
> really good way to skip all weekends in the caluclation. Here's the
> ideas I've come up with:
How about this (a bit ugly, but I think it does what you want -- minus
the holidays, which you said you already have figured out):
create or replace function
get_future_work_day(timestamp, int)
returns timestamp as '
select
case when extract(dow from future_work_date) = 6
then future_work_date + ''2 days''
when extract(dow from future_work_date) = 0
then future_work_date + ''1 day''
else
future_work_date
end
from
(
select $1
+ (($2 / 5)::text || '' weeks'')
+ (($2 % 5)::text || '' days'')
as future_work_date
) as t1
' language sql;
CREATE
testslv=# select get_future_work_day('2002-06-20',27);
get_future_work_day
2002-07-29 00:00:00-07
(1 row)
HTH,
Joe
---(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] skip weekends
Hello, Nice reply Josh. I wouldn't call your solution 'ugly' at all. It's an excellent example of a real world need for Postgresql functions. I've also been looking at other functions at http://www.brasileiro.net/postgres/cookbook/. I noticed your name amongst the author's -- nice one -- keep up the good work. My only problem is trying to decide on whether to use PL/pgSQL or PLPerl. Cheers Rudi ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
