On Thu, Mar 13, 2008 at 5:54 PM, chester c young <[EMAIL PROTECTED]>
wrote:
> is there any way to share a cursor between sessions?
>
> I have a costly query whose records need to be visited by in order by n
> number of concurrent sessions, and am unable to find the best way of
> doing this.
>
> I'
is there any way to share a cursor between sessions?
I have a costly query whose records need to be visited by in order by n
number of concurrent sessions, and am unable to find the best way of
doing this.
I'd almost like to write a daemon that hands out the next record, but
that's a royal pain t
Sorry, my bad, I meant 5 days x 4 persons = 20 person-days. From 10 to 15 it
is 5 'days' ? Arrival 10th and departure 15th makes 5 'nights' or 'days',
whatever one might call it, statistical accommodation units.
Likewise, res_id x, start_day some time before Feb 08, end_day 2008-02-16 for
a
This was superfast, thank you !
On Thursday 13 March 2008 20:58, Steve Crawford wrote:
> Aarni Ruuhimäki wrote:
> > res_id 2, start_day 2008-02-10, end_day 2008-02-15, number of persons 4
> >
>
> If you use the same inclusive counting of days for res_id 2, you have 4
> persons (don't know where 5
Aarni Ruuhimäki wrote:
Hi all,
A bit stuck here with something I know I can do with output / loops /
filtering in the (web)application but want to do in SQL or within PostgreSQL.
Simply said, count days of accommodation for a given time period.
E.g.
res_id 1, start_day 2008-01-25, end_day 2
Hi all,
A bit stuck here with something I know I can do with output / loops /
filtering in the (web)application but want to do in SQL or within PostgreSQL.
Simply said, count days of accommodation for a given time period.
E.g.
res_id 1, start_day 2008-01-25, end_day 2008-02-15, number of perso
So it all comes back to what I wrote in the beginning:
"Which solution performs best depends on the data, the database
version, the available indexes, ..."
Tips:
- be aware that statements can be written in different ways
- test them on realistic data
- use explain to tune your statements
>
>From my experience I tend to avoid SELECT DISTINCT queries because it's
usually very slow with many rows ...
For my specific case the result is the same:
muridae=> select count(distinct s.specimen_id) from specimens sp INNER
JOIN sequences s on s.specimen_id = sp.id;
count
---
1431
(1 ro
The chapter on indexes in the manual (
http://www.postgresql.org/docs/8.2/static/indexes.html )should give
you a pretty good idea on the why.
IN and EXISTS are not the only possibilities, you can also use inner or
outer joins.
Which solution performs best depends on the data, the database version,
how about
select count(distinct s.specimen_id) from specimens sp INNER JOIN
sequences s
on s.specimen_id = sp.id;
>>> Julien <[EMAIL PROTECTED]> 2008-03-13 17:27 >>>
mmh no because it's a one to many relation (a specimen can have more
than one sequence) :
muridae=> select count(sp.id) from spec
mmh no because it's a one to many relation (a specimen can have more
than one sequence) :
muridae=> select count(sp.id) from specimens sp INNER JOIN sequences s
on s.specimen_id = sp.id;
count
---
1536
(1 row)
Time: 81.242 ms
muridae=> select count(sp.id) from specimens sp where sp.id in
I think that just
select count(sp.id) from specimens sp INNER JOIN sequences s on
s.specimen_id = sp.id;
should be enough
>>> Julien <[EMAIL PROTECTED]> 2008-03-13 17:10 >>>
If I understood well the query plan, the planner optimize the
IN(SELECT ...) version with a JOIN (line 19-20 of the first pa
If I understood well the query plan, the planner optimize the
IN(SELECT ...) version with a JOIN (line 19-20 of the first paste) :
-> Hash IN Join (cost=240.95..4011.20 rows=1436 width=4) (actual
time=93.971..201.908 rows=1431 loops=1)
Hash Cond: ("outer".id = "inner".specimen_id)
so I
Hello,
Does anyone has an idea why sometimes:
- select ... where ... in (select ...)
is faster than :
- select ... where ... exists(select ...)
and sometimes it's the opposite ?
I had such a situation, I've pasted the queries on:
http://rafb.net/p/KXNZ6892.html and http://rafb.net/p/jvo5DO38.html
14 matches
Mail list logo
