On 17 Aug 2006 at 10:00, Mario Weilguni wrote:
not really sure if this is right without any testdata, but isn't that what
you
want?
CREATE index foo on sheep_flock (flock_no);
SELECT DISTINCT on (f1.transfer_date) f1.regn_no, f1.transfer_date as date_in
FROM SHEEP_FLOCK f1
WHERE
On 16 Aug 2006 at 18:51, Tom Lane wrote:
Peter Hardman [EMAIL PROTECTED] writes:
I'm in the process of migrating a Paradox 7/BDE 5.01 database from
single-user
snip
Arjen van der Meijden has proposed a very elegant query in another post.
What I find interesting though is that it sounds
Hi, Peter,
Peter Hardman wrote:
BTW, are you *sure* you are testing PG 8.1? The Subquery Scan f2 plan
node looks unnecessary to me, and I'd have expected 8.1 to drop it out.
8.0 and before would have left it in the plan though. This doesn't make
all that much difference performance-wise in
On 17 Aug 2006 at 12:11, Markus Schaber wrote:
Hi, Peter,
Peter Hardman wrote:
BTW, are you *sure* you are testing PG 8.1? The Subquery Scan f2 plan
node looks unnecessary to me, and I'd have expected 8.1 to drop it out.
8.0 and before would have left it in the plan though. This
Hi, Peter,
Peter Hardman wrote:
select version() returns
PostgreSQL 8.1.3 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
(mingw-special)
That looks correct.
I also presume that your environment is not as fragile wr/t connecting
do wrong databases, compared to debian with their
Peter Hardman [EMAIL PROTECTED] writes:
I wonder whether Paradox and MySQL are just not doing the sort (this
seems to be what eats up the time), since the output of the subquery
is in fact already in the proper order.
MSSQL (from the other thread). I feel fairly safe in assuming that
MySQL's
I wrote:
Anywy, your point about the sort being redundant is a good one, and
offhand I'd have expected PG to catch that; I'll have to look into
why it didn't. But that's not going to explain a 10x speed
difference, because the sort isn't 90% of the runtime.
I dug into this using some made-up
I have no idea if there's a standard name or what it may be, but for
what it's worth, this sounds similar to the optimizations I wanted
for a different query:
http://archives.postgresql.org/pgsql-performance/2005-11/msg00037.php
1. Recognize that a term constant across the whole sort is
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_no, f1.transfer_date as date_in
FROM SHEEP_FLOCK f1 JOIN
/* The last transfer date for each sheep */
(SELECT f.regn_no, MAX(f.transfer_date)
On 16 Aug 2006 at 17:48, Peter Hardman wrote:
I'm in the process of migrating a Paradox 7/BDE 5.01 database from
single-user
Paradox to a web based interface to either MySQL or PostgreSQL.
snip
I've uploaded my data to www.shetland-sheep.org.uk/pgdata/sheep-flock.zip
The flock SSBXXX is
On 17 Aug 2006 at 14:33, Tom Lane wrote:
I wrote:
Anywy, your point about the sort being redundant is a good one, and
offhand I'd have expected PG to catch that; I'll have to look into
why it didn't. But that's not going to explain a 10x speed
difference, because the sort isn't 90% of
On 17 Aug 2006 at 20:58, Peter Hardman wrote:
On 16 Aug 2006 at 17:48, Peter Hardman wrote:
I'm in the process of migrating a Paradox 7/BDE 5.01 database from single-user
Paradox to a web based interface to either MySQL or PostgreSQL.
snip
I've uploaded my data to
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_no,
Peter Hardman [EMAIL PROTECTED] writes:
On 17 Aug 2006 at 14:33, Tom Lane wrote:
I found a couple of minor planner problems, which I've repaired in CVS
HEAD. You might consider using TEXT columns instead of VARCHAR(n),
As someone else suggested, these fields ought really to be CHAR no
I'm in the process of migrating a Paradox 7/BDE 5.01 database from single-user
Paradox to a web based interface to either MySQL or PostgreSQL.
The database is a pedigree sheep breed society database recording sheep and
flocks (amongst other things).
My current problem is with one table and an
On 16-8-2006 18:48, Peter Hardman wrote:
Using identically structured tables and the same primary key, if I run this on
Paradox/BDE it takes about 120ms, on MySQL (5.0.24, local server) about 3ms,
and on PostgresSQL (8.1.3, local server) about 1290ms). All on the same
Windows XP Pro machine
On 8/16/06, Peter Hardman [EMAIL PROTECTED] wrote:
I'm in the process of migrating a Paradox 7/BDE 5.01 database from single-user
Paradox to a web based interface to either MySQL or PostgreSQL.
The database is a pedigree sheep breed society database recording sheep and
flocks (amongst other
On 16 Aug 2006 at 20:02, Arjen van der Meijden wrote:
On 16-8-2006 18:48, Peter Hardman wrote:
Using identically structured tables and the same primary key, if I run this
on
Paradox/BDE it takes about 120ms, on MySQL (5.0.24, local server) about
3ms,
and on PostgresSQL (8.1.3,
Peter Hardman [EMAIL PROTECTED] writes:
I'm in the process of migrating a Paradox 7/BDE 5.01 database from
single-user
Paradox to a web based interface to either MySQL or PostgreSQL.
The query I run is:
/* Select all sheep who's most recent transfer was into the subject flock */
SELECT
19 matches
Mail list logo
