Don't know about Oracle, but select-distinct in MSSQL2K will indeed throw
away duplicates, which chops the CPU time. Very easy to see in the graphic
query plan, both in terms of CPU and the number of rows retrieved from a
single-node or nested-loop subtree. Definitely a worthwhile optimization.
"T
--- Tom Lane <[EMAIL PROTECTED]> wrote: >
=?iso-8859-1?q?Gary=20Cowell?=
> <[EMAIL PROTECTED]> writes:
> > So it seems the idea that oracle is dropping
> duplicate
> > rows prior to the sort when using distinct may
> indeed
> > be the case.
>
> Okay. We won't have any short-term solution for
> ma
=?iso-8859-1?q?Gary=20Cowell?= <[EMAIL PROTECTED]> writes:
> So it seems the idea that oracle is dropping duplicate
> rows prior to the sort when using distinct may indeed
> be the case.
Okay. We won't have any short-term solution for making DISTINCT do that,
but if you are on PG 7.4 you could ge
* Stephen Frost ([EMAIL PROTECTED]) wrote:
> systems does in 40 seconds. My only other concern is the Oracle system
> having to do the write I/O while the postgres one doesn't... I don't
> see an obvious way to get around that though, and I'm not sure if it'd
> really make *that* big of a differe
* Tom Lane ([EMAIL PROTECTED]) wrote:
> Stephen Frost <[EMAIL PROTECTED]> writes:
> > * Tom Lane ([EMAIL PROTECTED]) wrote:
> >> [... thinks for awhile ...] It seems possible that they may use sort
> >> code that knows it is performing a DISTINCT operation and discards
> >> duplicates on sight. G
Stephen Frost <[EMAIL PROTECTED]> writes:
> * Tom Lane ([EMAIL PROTECTED]) wrote:
>> [... thinks for awhile ...] It seems possible that they may use sort
>> code that knows it is performing a DISTINCT operation and discards
>> duplicates on sight. Given that there are only 534 distinct values,
>>
* Tom Lane ([EMAIL PROTECTED]) wrote:
> [... thinks for awhile ...] It seems possible that they may use sort
> code that knows it is performing a DISTINCT operation and discards
> duplicates on sight. Given that there are only 534 distinct values,
> the sort would easily stay in memory if that we
> Try increasing sort_mem temporarily, and see if that
> makes a difference:
>SET sort_mem = 64000;
>EXPLAIN ANALYSE ...
I did this (actualy 65536) and got the following:
pvcsdb=# explain analyze select distinct version from
vers where version is not null;
Hi,
Tom Lane wrote:
=?iso-8859-1?q?Gary=20Cowell?= <[EMAIL PROTECTED]> writes:
-> Sort (cost=117865.77..119220.13 rows=541741
width=132) (actual time=63623.417..66127.641
rows=541741 loops=1)
This is clearly where the time is going.
sort_mem = 16384
=?iso-8859-1?q?Gary=20Cowell?= <[EMAIL PROTECTED]> writes:
>-> Sort (cost=117865.77..119220.13 rows=541741
> width=132) (actual time=63623.417..66127.641
> rows=541741 loops=1)
This is clearly where the time is going.
> sort_mem = 16384
Probably not enough for this problem. The estim
Gary Cowell wrote:
--- [EMAIL PROTECTED] wrote: > You can roughly estimate time
spent for just scaning
the table using
something like this:
select sum(version) from ... where version is not
null
and just
select sum(version) from ...
The results would be interesting to compare.
Hi ,
I have similare problem and found that the problem is by pg sort.
It is extremly slow by me.
Also in my case I tryed to migrate one db from oracle to pg .
To solve this problem I dinamicaly set sort_mem to some big value.
In this case the sort is working into RAM and is relative fast.
Yo
--- [EMAIL PROTECTED] wrote: > You can roughly estimate time
spent for just scaning
> the table using
> something like this:
>
> select sum(version) from ... where version is not
> null
>
> and just
>
> select sum(version) from ...
>
> The results would be interesting to com
Gary Cowell wrote:
The explain output on postgres shows the same
execution with a scan on vers and a sort but the query
time is 78.6 seconds.
The explain output from PostgreSQL is:
QUERY PLAN
On Jun 18, 2004, at 7:31 AM, Gary Cowell wrote:
The explain output on postgres shows the same
execution with a scan on vers and a sort but the query
time is 78.6 seconds.
Does it run just as slow if you run it again?
It could be a case of the caches being empty
Oracle but I think I've configured co
On 18/06/2004 12:31 Gary Cowell wrote:
[snip]
I'm not as familiar with postgresql as I am with
Oracle but I think I've configured comparible
buffering and sort area sizes, certainly there isn't
much physical IO going on in either case.
What can I do to speed up this query? Other queries
are slightl
Gary Cowell wrote:
I'm not as familiar with postgresql as I am with
Oracle but I think I've configured comparible
buffering and sort area sizes, certainly there isn't
much physical IO going on in either case.
People are going to want to know:
1. version of PG
2. explain analyse output, rather than
17 matches
Mail list logo
