Peter, I compared these using the data you supplied on my PostgreSQL
8.1.4 system:
On Aug 17, 2006, at 12:09 PM, Scott Lamb wrote:
On Aug 16, 2006, at 3:51 PM, Tom Lane wrote:
/* Select all sheep who's most recent transfer was into the
subject flock */
SELECT DISTINCT f1.regn_n
where latest.regn_no = even_later.regn_no
and latest.transfer_date < even_later.transfer_date)
and latest.flock_no = '1359'
There's no MAX() or DISTINCT here, so maybe this is easier to optimize?
--
Scott Lamb <http://www.slamb.org/>
--
th
the client-side solution we came up with.
--
Scott Lamb <http://www.slamb.org/>
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
n. So no real way
to do this.
Regards,
Scott
--
Scott Lamb <http://www.slamb.org/>
---(end of broadcast)---
TIP 6: explain analyze is your friend
about anything.Interesting. If you abandon the idea that collisions should be impossible (they're not indexes) or extremely rare (they're not hashes), it's pretty easy to come up with a decent hint to avoid a lot of dereferences. --Scott Lamb <http://www.slamb.org/>
you have to get them:
(1) sort the values into an array
(2) use each value's array index as its key
It reduces to the problem you're trying to use it to solve.
--
Scott Lamb <http://www.slamb.org/>
---(end of broadcast)---
TIP 9
On Nov 14, 2005, at 3:52 PM, Steve Wampler wrote:
Scott Lamb wrote:
On Nov 14, 2005, at 2:07 PM, Steve Wampler wrote:
# SELECT at.id FROM "tmp_table2" at, "tmp_tabl2e" a
# WHERE at.id=a.id and a.name='obsid' and a.value='oid080505';
Isn't this
7; and value = 'oid080505';
# DELETE FROM "tmp_table2" WHERE id IN
# (SELECT at.id FROM "tmp_table2" at, "tmp_table2" a
# WHERE at.id=a.id and a.name='obsid' and a.value='oid080505');
and this?
delete from tmp_table2 where name = '
each element of the union and then merging
that, compared to the cost of grabbing the union and then ordering
it. In this case, the former cost is about 0 - it already has
independently ordered them, and the merge algorithm is trivial.
<http://en.wikipedia.org/wiki/Merge_algorithm&g
to take
my first plunge into looking at a database query planner.
Regards,
Scott
--
Scott Lamb <http://www.slamb.org/>
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail
Merlin Moncure wrote:
hmm, try pushing the union into a subquery...this is better style
because it's kind of ambiguous if the ordering will apply before/after
the union.
Seems to be a little slower. There's a new "subquery scan" step.
explain analyze
selectq.when_happened from (
On 2 Nov 2005, at 21:13, Scott Lamb wrote:
I want to retrieve all the events during a timespan in one list;
typical timespans will involve up to a couple rows.
Err, I meant up to a couple million rows. With two rows, I wouldn't
be so concerned about performance. ;)
--
Scott Lamb
-> Index Scan using transaction_stopped on
"transaction" t (cost=0.00..79787.90 rows=1747458 width=8) (actual
time=0.149..2905.861 rows=1682003 loops=1)
Index Cond: (('2005-10-25 15:00:00'::timestamp
without time zone <
13 matches
Mail list logo
