On 3 May 2013 21:06, Yang Zhang wrote:
>> Guessing the answer's no, but is there any way to construct indexes
>> such that I can safely put them on (faster) volatile storage? (Just to
>> be clear, I'm asking about indexes for *logged* tables.)
>
> Yes:
>
> CRE
Guessing the answer's no, but is there any way to construct indexes
such that I can safely put them on (faster) volatile storage? (Just to
be clear, I'm asking about indexes for *logged* tables.)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscr
I have an `account` table with 5.3M rows, with primary key `id` of
type `text` (and 600+ columns if that matters).
I'm trying to create a `newaccount` table with the same schema but
600k newly imported rows, then insert all the old rows for which `id`
isn't already in the set of newly updated rows
On Wed, May 1, 2013 at 4:56 PM, Jeff Janes wrote:
> That brings up another point to consider. If wal level is minimal, then
> tables which you bulk load in the same transaction as you created them or
> truncated them will not get any WAL records written. (That is the main
> reason the WAL verbos
On Tue, Apr 30, 2013 at 11:14 PM, Yang Zhang wrote:
> On Tue, Apr 30, 2013 at 8:13 PM, Tom Lane wrote:
>> Ian Lawrence Barwick writes:
>>> I think this is the post in question:
>>> http://thebuild.com/blog/2013/03/10/you-cannot-recover-from-the-loss-of-a-tablespace
On Tue, Apr 30, 2013 at 8:13 PM, Tom Lane wrote:
> Ian Lawrence Barwick writes:
>> 2013/5/1 Yang Zhang :
>>> That is unfortunate. Good thing I asked, I guess. Do you have a
>>> pointer to said blog post?
>
>> I think this is the post in question:
>&g
On Tue, Apr 30, 2013 at 8:13 PM, Tom Lane wrote:
> Ian Lawrence Barwick writes:
>> 2013/5/1 Yang Zhang :
>>> That is unfortunate. Good thing I asked, I guess. Do you have a
>>> pointer to said blog post?
>
>> I think this is the post in question:
>&g
On Tue, Apr 30, 2013 at 7:14 PM, Ian Lawrence Barwick wrote:
> 2013/5/1 Yang Zhang :
>
>> On Tue, Apr 30, 2013 at 5:31 PM, Darren Duncan
>> wrote:
>>> On 2013.04.30 4:55 PM, Yang Zhang wrote:
>>>>
>>>> I would intuit that it's fine, but I
That is unfortunate. Good thing I asked, I guess. Do you have a
pointer to said blog post?
On Tue, Apr 30, 2013 at 5:31 PM, Darren Duncan wrote:
> On 2013.04.30 4:55 PM, Yang Zhang wrote:
>>
>> I would intuit that it's fine, but I just want to make sure there are
>>
I would intuit that it's fine, but I just want to make sure there are
no gotchas from a recovery point of view:
If I were to lose my temp tablespace upon system crash, would this
prevent proper crash recovery?
Also, if I were to omit the temp tablespace from the base backup,
would that prevent pr
On Sat, Apr 27, 2013 at 11:55 AM, Jeff Janes wrote:
> On Sat, Apr 27, 2013 at 10:40 AM, Yang Zhang wrote:
>> My question really boils down to: if we're interested in using COW
>> snapshotting (a common feature of modern filesystems and hosting
>> environments), wo
ELECT * FROM bar
UNION
SELECT * FROM tmp WHERE id NOT IN (SELECT id FROM bar);
The question I have remaining is whether the bulk UPDATE will be able
to update many rows efficiently (smartly order them to do largely
sequential scans) - if so, I imagine it would be faster than the
above.
>
>
On Sat, Apr 27, 2013 at 2:54 AM, Yang Zhang wrote:
> On Sat, Apr 27, 2013 at 1:55 AM, Misa Simic wrote:
>> Hi,
>>
>> If dataset for update is large...
>>
>> Maybe best would be:
>>
>> From client machine, instead of sending update statements with
ECT * FROM bar
UNION
SELECT * FROM foo
WHERE id NOT IN (SELECT id FROM bar);
Wouldn't this alternative be faster?
>
> Kind regards,
>
> Misa
>
>
>
> On Saturday, April 27, 2013, Yang Zhang wrote:
>>
>> On Fri, Apr 26, 2013 at 9:41 PM, Tom Lane wrote:
>&g
On Sat, Apr 27, 2013 at 12:24 AM, Yang Zhang wrote:
> On Fri, Apr 26, 2013 at 9:41 PM, Tom Lane wrote:
>> Yang Zhang writes:
>>> It currently takes up to 24h for us to run a large set of UPDATE
>>> statements on a database, which are of the form:
>>
>>&g
On Fri, Apr 26, 2013 at 9:41 PM, Tom Lane wrote:
> Yang Zhang writes:
>> It currently takes up to 24h for us to run a large set of UPDATE
>> statements on a database, which are of the form:
>
>> UPDATE table SET field1 = constant1, field2 = constant2, ... W
;
> On 27/04/13 13:35, Yang Zhang wrote:
>>
>> We're using Postgresql 9.1.9 on Ubuntu 12.04 on EBS volumes on
>> m1.xlarge instances, which have:
>>
>> 15 GiB memory
>> 8 EC2 Compute Units (4 virtual cores with 2 EC2 Compute Units each)
>> 64-bi
We're running on EBS volumes on EC2. We're interested in leveraging
EBS snapshotting for backups. However, does this mean we'd need to
ensure our pg_xlog is on the same EBS volume as our data?
(I believe) the usual reasoning for separating pg_xlog onto a separate
volume is for performance. Howe
benchmarks suggest this won't get us enough of a
boost as much as possibly refactoring the way we're executing these
bulk updates in our application.)
On Fri, Apr 26, 2013 at 5:27 PM, Gavin Flower
wrote:
> On 27/04/13 12:14, Yang Zhang wrote:
>
> It currently takes up to 24h for us
It currently takes up to 24h for us to run a large set of UPDATE
statements on a database, which are of the form:
UPDATE table SET field1 = constant1, field2 = constant2, ... WHERE
id = constid
(We're just overwriting fields of objects identified by ID.)
The tables have handfuls of indi
Apologies for that Tom. I will paste the information in line once I'm
back at my computer. I do appreciate your help.
On Fri, Apr 12, 2013 at 10:24 AM, Tom Lane wrote:
> Yang Zhang writes:
>> I updated my SO question with some more info including explain analyze
>> (no dif
It's actually just `text`.
I updated my SO question with some more info including explain analyze
(no difference), \d,
and your last incantation.
Thanks!
On Fri, Apr 12, 2013 at 7:11 AM, Tom Lane wrote:
> Yang Zhang writes:
>> db=> explain select * from lead wh
QUERY PLAN
---
Seq Scan on lead (cost=100.00..1319666.99 rows=1 width=5208)
Filter: (email = 'f...@blah.com'::text)
(2 rows)
On Fri, Apr 12, 2013 at 1:13 AM, John R Pierce wrote:
> On 4/12/2013 1:03 AM, Yang Zhang wrote:
>
Any hints with this question I had posted to SO?
http://stackoverflow.com/questions/15965785/why-is-postgresql-9-1-not-using-index-for-simple-equality-select
Pasted here as well. Thanks.
My table `lead` has an index:
\d lead
...
Indexes:
"lead_pkey" PRIMARY KEY, btree (id)
On Thu, Sep 15, 2011 at 1:22 AM, Tomas Vondra wrote:
> On 15 Září 2011, 9:53, Yang Zhang wrote:
>> I have a simple query that's been running for a while, which is fine,
>> but it seems to be running very slowly, which is a problem:
>>
>> mydb=# explain select user_
Should also add that while the client is under no load (CPU<1%, load
~0.1, mem ~20%), the server looks pretty busy (CPU 90-100% of 1 core,
load ~1.5, mem ~70-80%), but PG is the only thing using resources.
On Thu, Sep 15, 2011 at 12:58 AM, Yang Zhang wrote:
> If it matters, the client is
in (select duid from
user_mappings)
and timestamp between '2009-04-01'::date and '2010-04-01'::date;
""", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)
st.setFetchSize(8192)
rs = st.executeQuery()
On Thu, Sep 15, 2011 at 12:53 AM, Yang
o | ssh nuthouse 'cat>/dev/null'` shows sustained bandwidth at
~30-40MB/s.)
I know that `den` is large but things seem to be running much slower
than I'd expect. Nothing in the logs. Is there any way to inspect
what's going on? Hesitant to kill the query in case it&
PG tends to be picked on by the Linux OOM killer, so lately we've been
forcing the OOM killer to kill other processes first with this script:
while true; do
for i in `pgrep postgres`; do
echo -17 > /proc/$i/oom_adj
done
sleep 60
done
Is there a Better Way? Thanks in advance.
--
Sent
_schema,
sum(bytes) as bytes,
lpad(pg_size_pretty(sum(bytes)::int), 9) as size,
sum(pct) as pct
from basic
group by _schema
order by bytes desc
)
$query;
"
}
--
Yang Zhang
http://yz.mit.edu/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Thu, Apr 14, 2011 at 6:46 PM, mark wrote:
>
>
>> -Original Message-
>> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
>> ow...@postgresql.org] On Behalf Of Yang Zhang
>> Sent: Thursday, April 14, 2011 6:51 PM
>> To: Adrian Klaver
On Thu, Apr 14, 2011 at 7:42 PM, Adrian Klaver wrote:
> On Thursday, April 14, 2011 5:51:21 pm Yang Zhang wrote:
>
>> >
>
>> > adrian.kla...@gmail.com
>
>>
>
>> Already know about TOAST. I could've been clearer, but that's not the
>
>&
On Thu, Apr 14, 2011 at 5:07 PM, Adrian Klaver wrote:
> On Thursday, April 14, 2011 4:50:44 pm Craig Ringer wrote:
>
>> On 15/04/2011 7:01 AM, Yang Zhang wrote:
>
>> > Is there any effort to add compression into PG, a la MySQL's
>
>> > row_format=co
Is there any effort to add compression into PG, a la MySQL's
row_format=compressed or HBase's LZO block compression?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Dah, left out the port.
On Fri, Apr 8, 2011 at 10:36 PM, Yang Zhang wrote:
> I'm using the postgresql 8.4.7 in Ubuntu 10.04, and I'm trying to use
> .pgpass documented here:
>
> http://www.postgresql.org/docs/8.4/interactive/libpq-pgpass.html
>
> I have a ~/.pgpa
I'm using the postgresql 8.4.7 in Ubuntu 10.04, and I'm trying to use
.pgpass documented here:
http://www.postgresql.org/docs/8.4/interactive/libpq-pgpass.html
I have a ~/.pgpass with 600 perms containing:
myhostname.com:yang:yang:mypassword
However, it doesn't seem to get picked up by psql -h
Also, in PG8.4+, is there any way to set the default tablespace on a
per-schema basis?
On Thu, Apr 7, 2011 at 12:27 PM, Yang Zhang wrote:
> Last I could find on this, it was slated for 8.1 inclusion:
>
> http://archives.postgresql.org/pgsql-patches/2004-08/msg00425.php
>
> But it
Last I could find on this, it was slated for 8.1 inclusion:
http://archives.postgresql.org/pgsql-patches/2004-08/msg00425.php
But it doesn't seem to be there in my PG8.4
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.po
Is it possible to attach or detach parts of a DB (e.g. tablespaces),
such that I can flexibly move the disks containing the DB tables
around to different hosts?
The last discussion I could find on this topic is from 2007, and the
answer was "No":
http://postgresql.1045698.n5.nabble.com/Possible-t
On Wed, Apr 6, 2011 at 6:18 PM, Adrian Klaver wrote:
> On Wednesday, April 06, 2011 5:21:23 pm Yang Zhang wrote:
>
>> On Wed, Apr 6, 2011 at 4:57 PM, Scott Marlowe
>> wrote:
>
>> > On Wed, Apr 6, 2011 at 5:24 PM, Yang Zhang
>> > wrote:
>
>>
On Wed, Apr 6, 2011 at 4:57 PM, Scott Marlowe wrote:
> On Wed, Apr 6, 2011 at 5:24 PM, Yang Zhang wrote:
>> On Wed, Apr 6, 2011 at 4:22 PM, Adrian Klaver
>> wrote:
>>> On Wednesday, April 06, 2011 4:06:40 pm Yang Zhang wrote:
>>>> How do I prevent acciden
On Wed, Apr 6, 2011 at 4:53 PM, Adrian Klaver wrote:
> On Wednesday, April 06, 2011 4:24:30 pm Yang Zhang wrote:
>
>> On Wed, Apr 6, 2011 at 4:22 PM, Adrian Klaver
>> wrote:
>
>> > On Wednesday, April 06, 2011 4:06:40 pm Yang Zhang wrote:
>
>> >> Ho
On Wed, Apr 6, 2011 at 4:22 PM, Adrian Klaver wrote:
> On Wednesday, April 06, 2011 4:06:40 pm Yang Zhang wrote:
>> How do I prevent accidental non-SSL connections (at least to specific
>> hosts) when connecting via psql? Is there any configuration for this?
>
How do I prevent accidental non-SSL connections (at least to specific
hosts) when connecting via psql? Is there any configuration for this?
Thanks.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-
On Wed, Apr 6, 2011 at 2:09 PM, Adrian Klaver wrote:
> On Wednesday, April 06, 2011 2:00:53 pm Yang Zhang wrote:
>> Anyone know how to set your password without having it visible in the
>> terminal, psql history, etc. (i.e. getpass(3))? Hoping there's a
>> program that
On Wed, Apr 6, 2011 at 2:15 PM, Yang Zhang wrote:
> On Wed, Apr 6, 2011 at 2:09 PM, Adrian Klaver wrote:
>> On Wednesday, April 06, 2011 2:00:53 pm Yang Zhang wrote:
>>> Anyone know how to set your password without having it visible in the
>>> terminal, psql hi
Anyone know how to set your password without having it visible in the
terminal, psql history, etc. (i.e. getpass(3))? Hoping there's a
program that already does this. Thanks.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.pos
Is there any tool for breaking down how much disk space is used by
(could be freed by removing) various tables, indexes, selected rows,
etc.? Thanks!
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgs
Sent: Thursday, March 24, 2011 4:56 PM
> To: Yang Zhang
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Default permissions for CREATE SCHEMA/TABLE?
>
> * Yang Zhang (yanghates...@gmail.com) wrote:
>> Any way I can have all newly created schemas/tables be owned by
Any way I can have all newly created schemas/tables be owned by, or
have all permissions granted to, a certain group, without having to
remember to GRANT ALL ON [SCHEMA|TABLE] TO that group? Thanks in
advance.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes
ffects of app-level large-field
compression in analytical workloads (though I'd be curious about
transactional workloads as well)? Thanks in advance.
--
Yang Zhang
http://yz.mit.edu/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscript
ow wait for the standby"?
--
Yang Zhang
http://yz.mit.edu/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Thanks in advance.
--
Yang Zhang
http://yz.mit.edu/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
cedure languages as well?
--
Yang Zhang
http://yz.mit.edu/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
b: 0
c: 0
d: 0
e {
a: "hello!"
b: 0
c: 0
}
Any hints on how I can get to the bottom of this? Thanks in advance.
--
Yang Zhang
http://yz.mit.edu/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Tue, Feb 23, 2010 at 1:48 AM, Scott Marlowe wrote:
> On Mon, Feb 22, 2010 at 10:51 PM, Yang Zhang wrote:
>> nOn Mon, Feb 22, 2010 at 3:45 PM, Scott Marlowe
>> wrote:
>>>
>>> What do things like vmstat 10 say while the query is running on each
>>>
On Mon, Feb 22, 2010 at 9:30 PM, Alex Hunsaker wrote:
> On Mon, Feb 22, 2010 at 11:10, Yang Zhang 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
>
nOn Mon, Feb 22, 2010 at 3:45 PM, Scott Marlowe
wrote:
> On Mon, Feb 22, 2010 at 12:53 PM, Yang Zhang wrote:
>> On Mon, Feb 22, 2010 at 2:52 PM, Scott Marlowe
>> wrote:
>>> On Mon, Feb 22, 2010 at 11:10 AM, Yang Zhang wrote:
>>>> I have the exact
On Mon, Feb 22, 2010 at 5:31 PM, Igor Neyman 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 Post
On Mon, Feb 22, 2010 at 3:44 PM, Tom Lane wrote:
> Yang Zhang writes:
>> On Mon, Feb 22, 2010 at 1:13 PM, Pavel Stehule
>> wrote:
>>> the speed depends on setting of working_memory. Try to increase a
>>> working_memory
>
>> It's already at
>
On Mon, Feb 22, 2010 at 2:41 PM, Frank Heikens 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
>>>
On Mon, Feb 22, 2010 at 2:52 PM, Scott Marlowe wrote:
> On Mon, Feb 22, 2010 at 11:10 AM, Yang Zhang 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?
>
Yes, on the s
On Mon, Feb 22, 2010 at 2:39 PM, Scott Marlowe wrote:
> On Mon, Feb 22, 2010 at 12:30 PM, Yang Zhang wrote:
>> This isn't some microbenchmark. This is part of our actual analytical
>> application. We're running large-scale graph partitioning algorithms.
>
> It&#x
On Mon, Feb 22, 2010 at 2:27 PM, Alvaro Herrera
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
On Mon, Feb 22, 2010 at 2:15 PM, Frank Heikens 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
>> wrote:
>>>
>>> On 22 Feb 2010, at 19:35, Yang Zhang wrote:
>>>
>
On Mon, Feb 22, 2010 at 2:03 PM, Alvaro Herrera
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
>
On Mon, Feb 22, 2010 at 1:48 PM, Alban Hertroys
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
REATE INDEX i_transactionid ON public.metarelcloud_transactionlog
>> (transactionid);
>
> Does an index help a sort operation in PostgreSQL?
I also share the same doubt. An external merge-sort needs to make
complete passes over the entire dataset, with no index-directed
accesses.
--
Yan
I also wouldn't have imagined an external merge-sort as being very
memory-intensive--wouldn't it only enough buffer space to read 2x and
write 1x in big-enough chunks for mostly-sequential access?
--
Yang Zhang
http://www.mit.edu/~y_z/
--
Sent via pgsql-general mailing list (pgsql-gene
KEY `nodeid` (`nodeid`)
) ENGINE=MyISAM AUTO_INCREMENT=50410166 DEFAULT CHARSET=latin1
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 misconfiguratio
;s got approximately nothing to do with this query.
Isn't that exactly what the leaf sorts are doing? By comparison,
"select * from metarelcloud_transactionlog order by transactionid"
takes much, much longer in PG (it's been running for 68 minutes now,
and still going, whereas M
34..8534662.95
rows=50410244 width=17)
Sort Key: b.transactionid
-> Seq Scan on
metarelcloud_transactionlog b (cost=0.00..925543.44 rows=50410244
width=17)
(14 rows)
--
Yang Zhang
http://www.mit.edu/~y_z/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
72 matches
Mail list logo
