On Tue, Feb 23, 2010 at 6:48 AM, Scott Marlowe scott.marl...@gmail.com wrote:
I'm relieved that Postgresql itself does not, in fact, suck, but
slightly disappointed in the behavior of psql. I suppose it needs to
buffer everything in memory to properly format its tabular output,
among other
I am under the impression that MySQL does not have anything resembling
Postgres' support for regular expressions. Though some might think
that regular expressions are a sort of poor man's SQL, in any
application which manages large amounts of text they are crucial.
Postgres definitely
Greg Stark wrote:
You can do \set FETCH_COUNT to have psql use a cursor automatically.
It seems like a big win in this case. What would be the downside of
having a fetch_count set default in psql?
regards
Yeb Havinga
--
Sent via pgsql-general mailing list
On Tue, Feb 23, 2010 at 9:48 AM, Yeb Havinga yebhavi...@gmail.com wrote:
Greg Stark wrote:
You can do \set FETCH_COUNT to have psql use a cursor automatically.
It seems like a big win in this case. What would be the downside of having a
fetch_count set default in psql?
They were mentioned
On Tue, Feb 23, 2010 at 00:02, Yang Zhang yanghates...@gmail.com wrote:
Thing is, this is how I got here:
- ran complex query that does SELECT INTO.
- that never terminated, so killed it and tried a simpler SELECT (the
subject of this thread) from psql to see how long that would take.
You
Hi,
On Tue, Feb 23, 2010 at 12:51 AM, Yang Zhang yanghates...@gmail.com wrote:
When running the query in MySQL InnoDB:
$ vmstat 10
procs ---memory-- ---swap-- -io --system--
-cpu--
r b swpd free buff cache si so bi bo in cs us sy id wa
I have the exact same table of data in both MySQL and Postgresql. In Postgresql:
tpcc=# \d metarelcloud_transactionlog
Table
public.metarelcloud_transactionlog
Column| Type |
Modifiers
hello
the speed depends on setting of working_memory. Try to increase a working_memory
set working_memory to '10MB';
Regards
Pavel Stehule
2010/2/22 Yang Zhang yanghates...@gmail.com:
I have the exact same table of data in both MySQL and Postgresql. In
Postgresql:
tpcc=# \d
There is no index on the column transactionid in your PostgreSQL-
table, as there is in your MySQL-table. This explains the difference.
CREATE INDEX i_transactionid ON public.metarelcloud_transactionlog
(transactionid);
Op 22 feb 2010, om 19:10 heeft Yang Zhang het volgende geschreven:
I
On Mon, Feb 22, 2010 at 10:17 AM, Frank Heikens frankheik...@mac.com wrote:
There is no index on the column transactionid in your PostgreSQL-table, as
there is in your MySQL-table. This explains the difference.
CREATE INDEX i_transactionid ON public.metarelcloud_transactionlog
On Mon, Feb 22, 2010 at 1:13 PM, Pavel Stehule pavel.steh...@gmail.com wrote:
hello
the speed depends on setting of working_memory. Try to increase a
working_memory
set working_memory to '10MB';
It's already at
tpcc=# show work_mem;
work_mem
--
2kB
(1 row)
I also wouldn't
On Mon, Feb 22, 2010 at 1:30 PM, Richard Broersma
richard.broer...@gmail.com wrote:
On Mon, Feb 22, 2010 at 10:17 AM, Frank Heikens frankheik...@mac.com wrote:
There is no index on the column transactionid in your PostgreSQL-table, as
there is in your MySQL-table. This explains the difference.
Op 22 feb 2010, om 19:30 heeft Richard Broersma het volgende geschreven:
On Mon, Feb 22, 2010 at 10:17 AM, Frank Heikens
frankheik...@mac.com wrote:
There is no index on the column transactionid in your PostgreSQL-
table, as
there is in your MySQL-table. This explains the difference.
On 22 Feb 2010, at 19:35, Yang Zhang wrote:
I also wouldn't have imagined an external merge-sort as being very
Where's that external merge-sort coming from? Can you show an explain analyze?
If your work-mem is too low there's a good chance that Postgres has to use your
disks for sorting,
Yang Zhang escribió:
I'm running:
select * from metarelcloud_transactionlog order by transactionid;
It takes MySQL 6 minutes, but Postgresql is still running after 70
minutes. Is there something like a glaring misconfiguration that I'm
overlooking? Thanks in advance.
How large is the
On Mon, Feb 22, 2010 at 1:48 PM, Alban Hertroys
dal...@solfertje.student.utwente.nl wrote:
On 22 Feb 2010, at 19:35, Yang Zhang wrote:
I also wouldn't have imagined an external merge-sort as being very
Where's that external merge-sort coming from? Can you show an explain analyze?
I just
On Mon, Feb 22, 2010 at 2:03 PM, Alvaro Herrera
alvhe...@commandprompt.com wrote:
Yang Zhang escribió:
I'm running:
select * from metarelcloud_transactionlog order by transactionid;
It takes MySQL 6 minutes, but Postgresql is still running after 70
minutes. Is there something like a
Op 22 feb 2010, om 20:07 heeft Yang Zhang het volgende geschreven:
On Mon, Feb 22, 2010 at 1:48 PM, Alban Hertroys
dal...@solfertje.student.utwente.nl wrote:
On 22 Feb 2010, at 19:35, Yang Zhang wrote:
I also wouldn't have imagined an external merge-sort as being very
Where's that
On Mon, Feb 22, 2010 at 2:15 PM, Frank Heikens frankheik...@mac.com wrote:
Op 22 feb 2010, om 20:07 heeft Yang Zhang het volgende geschreven:
On Mon, Feb 22, 2010 at 1:48 PM, Alban Hertroys
dal...@solfertje.student.utwente.nl wrote:
On 22 Feb 2010, at 19:35, Yang Zhang wrote:
I also
Yang Zhang escribió:
I have the exact same table of data in both MySQL and Postgresql. In
Postgresql:
I just noticed two things:
[snip lots of stuff]
1.
) ENGINE=MyISAM AUTO_INCREMENT=50410166 DEFAULT CHARSET=latin1
You're doing a comparison to MyISAM.
2.
select * from
On Mon, Feb 22, 2010 at 2:27 PM, Alvaro Herrera
alvhe...@commandprompt.com wrote:
Yang Zhang escribió:
I have the exact same table of data in both MySQL and Postgresql. In
Postgresql:
I just noticed two things:
[snip lots of stuff]
1.
) ENGINE=MyISAM AUTO_INCREMENT=50410166 DEFAULT
On Mon, Feb 22, 2010 at 12:30 PM, Yang Zhang yanghates...@gmail.com wrote:
This isn't some microbenchmark. This is part of our actual analytical
application. We're running large-scale graph partitioning algorithms.
It's important to see how it runs if you can fit more / most of the
data set
Op 22 feb 2010, om 20:28 heeft Yang Zhang het volgende geschreven:
If your work-mem is too low there's a good chance that Postgres
has to
use your disks for sorting, which will obviously be quite slow.
Relative to the non-terminating 80-minute-so-far sort, Unix sort
runs
much
On Mon, Feb 22, 2010 at 2:39 PM, Scott Marlowe scott.marl...@gmail.com wrote:
On Mon, Feb 22, 2010 at 12:30 PM, Yang Zhang yanghates...@gmail.com wrote:
This isn't some microbenchmark. This is part of our actual analytical
application. We're running large-scale graph partitioning algorithms.
On Mon, Feb 22, 2010 at 11:10 AM, Yang Zhang yanghates...@gmail.com wrote:
I have the exact same table of data in both MySQL and Postgresql. In
Postgresql:
Just wondering, are these on the same exact machine?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make
On Mon, Feb 22, 2010 at 2:52 PM, Scott Marlowe scott.marl...@gmail.com wrote:
On Mon, Feb 22, 2010 at 11:10 AM, Yang Zhang yanghates...@gmail.com wrote:
I have the exact same table of data in both MySQL and Postgresql. In
Postgresql:
Just wondering, are these on the same exact machine?
On Mon, Feb 22, 2010 at 12:50 PM, Yang Zhang yanghates...@gmail.com wrote:
On Mon, Feb 22, 2010 at 2:39 PM, Scott Marlowe scott.marl...@gmail.com
wrote:
On Mon, Feb 22, 2010 at 12:30 PM, Yang Zhang yanghates...@gmail.com wrote:
This isn't some microbenchmark. This is part of our actual
On Mon, Feb 22, 2010 at 2:41 PM, Frank Heikens frankheik...@mac.com wrote:
Op 22 feb 2010, om 20:28 heeft Yang Zhang het volgende geschreven:
If your work-mem is too low there's a good chance that Postgres has to
use your disks for sorting, which will obviously be quite slow.
Relative to
Scott Marlowe wrote:
On Mon, Feb 22, 2010 at 11:10 AM, Yang Zhang yanghates...@gmail.com wrote:
I have the exact same table of data in both MySQL and Postgresql. In Postgresql:
Just wondering, are these on the same exact machine?
Just reading up on this interesting thread. WFIW, 2
Yang Zhang yanghates...@gmail.com writes:
On Mon, Feb 22, 2010 at 1:13 PM, Pavel Stehule pavel.steh...@gmail.com
wrote:
the speed depends on setting of working_memory. Try to increase a
working_memory
It's already at
2kB
According to your original posting, you're trying to sort
On Mon, Feb 22, 2010 at 12:53 PM, Yang Zhang yanghates...@gmail.com wrote:
On Mon, Feb 22, 2010 at 2:52 PM, Scott Marlowe scott.marl...@gmail.com
wrote:
On Mon, Feb 22, 2010 at 11:10 AM, Yang Zhang yanghates...@gmail.com wrote:
I have the exact same table of data in both MySQL and Postgresql.
On Mon, Feb 22, 2010 at 3:44 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Yang Zhang yanghates...@gmail.com writes:
On Mon, Feb 22, 2010 at 1:13 PM, Pavel Stehule pavel.steh...@gmail.com
wrote:
the speed depends on setting of working_memory. Try to increase a
working_memory
It's already at
Yeb Havinga yebhavi...@gmail.com writes:
Just reading up on this interesting thread. WFIW, 2 years ago I and a
collegue of mine did a hardware comparison of early Intel and AMD
desktop quadcore processors to run postgres database, with most other
parts comparable. The intel processor was 20
When in doubt - test.
Why not remove index in MySQL (or create index in PostgreSQL) and see
what happens.
Why trying compare apples and oranges?
Igor Neyman
-Original Message-
From: Yang Zhang [mailto:yanghates...@gmail.com]
Sent: Monday, February 22, 2010 1:37 PM
To: Richard
On Mon, Feb 22, 2010 at 5:31 PM, Igor Neyman iney...@perceptron.com wrote:
When in doubt - test.
Why not remove index in MySQL (or create index in PostgreSQL) and see
what happens.
Why trying compare apples and oranges?
Continue reading this thread -- I also tried using an index in
On Mon, Feb 22, 2010 at 3:33 PM, Yang Zhang yanghates...@gmail.com wrote:
On Mon, Feb 22, 2010 at 5:31 PM, Igor Neyman iney...@perceptron.com wrote:
When in doubt - test.
Why not remove index in MySQL (or create index in PostgreSQL) and see
what happens.
Why trying compare apples and oranges?
On Mon, Feb 22, 2010 at 3:33 PM, Yang Zhang yanghates...@gmail.com wrote:
On Mon, Feb 22, 2010 at 5:31 PM, Igor Neyman iney...@perceptron.com wrote:
When in doubt - test.
Why not remove index in MySQL (or create index in PostgreSQL) and see
what happens.
Why trying compare apples and oranges?
On Mon, Feb 22, 2010 at 11:10, Yang Zhang yanghates...@gmail.com wrote:
I have the exact same table of data in both MySQL and Postgresql. In ?
Postgresql:
FWIW on a stock (unchanged postgresql.conf) 8.3.9 I get (best of 3
runs) 79 seconds, 26 using an index and 27 seconds with it clustered.
nOn Mon, Feb 22, 2010 at 3:45 PM, Scott Marlowe
scott.marl...@gmail.com wrote:
On Mon, Feb 22, 2010 at 12:53 PM, Yang Zhang yanghates...@gmail.com wrote:
On Mon, Feb 22, 2010 at 2:52 PM, Scott Marlowe scott.marl...@gmail.com
wrote:
On Mon, Feb 22, 2010 at 11:10 AM, Yang Zhang
On Mon, Feb 22, 2010 at 9:30 PM, Alex Hunsaker bada...@gmail.com wrote:
On Mon, Feb 22, 2010 at 11:10, Yang Zhang yanghates...@gmail.com wrote:
I have the exact same table of data in both MySQL and Postgresql. In ?
Postgresql:
FWIW on a stock (unchanged postgresql.conf) 8.3.9 I get (best of
Yang Zhang yanghates...@gmail.com writes:
# select count(1) from (SELECT * from metarelcould_transactionlog
order by transactionid) as foo;
Does it strike anyone else that the query optimizer/rewriter should be
able to toss out the sort from such a query altogether?
It could, if it knew that
Yang Zhang yanghates...@gmail.com writes:
I'm relieved that Postgresql itself does not, in fact, suck, but
slightly disappointed in the behavior of psql. I suppose it needs to
buffer everything in memory to properly format its tabular output,
among other possible reasons I could imagine.
On Mon, Feb 22, 2010 at 10:51 PM, Yang Zhang yanghates...@gmail.com wrote:
nOn Mon, Feb 22, 2010 at 3:45 PM, Scott Marlowe
scott.marl...@gmail.com wrote:
What do things like vmstat 10 say while the query is running on each
db? First time, second time, things like that.
Awesome -- this
On Mon, Feb 22, 2010 at 22:51, Yang Zhang yanghates...@gmail.com wrote:
vmstat showed no swapping-out for a while, and then suddenly it
started spilling a lot. Checking psql's memory stats showed that it
was huge -- apparently, it's trying to store its full result set in
memory. As soon as I
On Tue, Feb 23, 2010 at 1:48 AM, Scott Marlowe scott.marl...@gmail.com wrote:
On Mon, Feb 22, 2010 at 10:51 PM, Yang Zhang yanghates...@gmail.com wrote:
nOn Mon, Feb 22, 2010 at 3:45 PM, Scott Marlowe
scott.marl...@gmail.com wrote:
What do things like vmstat 10 say while the query is
45 matches
Mail list logo
