On Fri, 26 Jun 2009, bob_lun...@yahoo.com wrote:
The original unique index was in the order (timestamptz, varchar, text,
text) and most queries against it were slow. I changed the index order
to (varchar, text, timestamptz, text) and queries now fly, but loading
data (via copy from stdin) in
On Fri, 12 Jun 2009, Alan McKay wrote:
So, from the perspective of both Linux and PG, is there canonical list
of "here are the most important X things to track" ?
Not really, which is why you haven't gotten such a list from anyone here.
Exactly what's important to track does vary a bit based
2009/6/26 Janet Jacobsen :
> Hi. The user in question is using psycopg2, which he uses
> psycopg2:
>> import psycopg2
>> conn = psycopg2.connect("dbname=%s user=%s host=%s password=%s port=%s" ...)
>> pg_cursor = conn.cursor()
>> pg_cursor.execute()
>> rows = pg_cursor.fetchall()
> Note that
> (1
Hi. The user in question is using psycopg2, which he uses
psycopg2:
> import psycopg2
> conn = psycopg2.connect("dbname=%s user=%s host=%s password=%s port=%s" ...)
> pg_cursor = conn.cursor()
> pg_cursor.execute()
> rows = pg_cursor.fetchall()
Note that
(1) he said that he does not set an isolat
bob_lun...@yahoo.com writes:
> Why would changing the column order on a unique index cause data loading or
> index servicing to slow down? Page splits in the b-tree, maybe?
Yeah, perhaps. Tell us about the data distributions in the columns?
Is there any ordering to the keys that're being insert
On Fri, 26 Jun 2009, Scott Mead wrote:
Having those settings enabled basically does the following:
" Do not complete the I/O for a commit until you have either commit_siblings
commits also ready, or you have waited .55 seconds."
Basically, if you make 1 commit, you will sit there wait
"Dave North" writes:
> The outstanding question here is why does the explain analyze take
> (quite a bit) longer than just executing the query?
EXPLAIN ANALYZE has nontrivial measurement overhead, especially on
platforms with slow gettimeofday(). Old/cheap PC hardware, in particular,
tends to su
On Fri, Jun 26, 2009 at 4:36 PM, Brian Troutwine
wrote:
> > Turn commit delay and commit siblings off.
>
> Why?
Sorry about the short and sweet, was driving:
Having those settings enabled basically does the following:
" Do not complete the I/O for a commit until you have either
commit
> Turn commit delay and commit siblings off.
Why?
Brian
On Fri, Jun 26, 2009 at 1:06 PM, Scott Mead wrote:
> -- sorry for the top-post and short response.
>
> Turn commit delay and commit siblings off.
>
> --Scott
>
> On 6/26/09, Brian Troutwine wrote:
>> Hello, all.
>>
>> I'm finding that writ
> Indexes are good things. Try them. Particularly on the isbn field.
I'm not sure why amazon_items.isbn should be given an index.
item_details.isbn is used in a WHERE clause and is given an index
accordingly, but not amazon_items.isbn.
Brian
On Fri, Jun 26, 2009 at 12:40 PM, Alan Hodgson wrote
On Fri, Jun 26, 2009 at 3:30 PM, Brian
Troutwine wrote:
> Hello, all.
>
> I'm finding that write performance of a certain stored procedure is
> abysmal. I need to be able to sustain approximately 20 calls to this
> procedure per second, but am finding that, on the average, each call
> takes 2 secon
On Friday 26 June 2009, Brian Troutwine wrote:
> CREATE TABLE amazon_items (
> asin char(10) PRIMARY KEY,
> locale varchar(10) NOT NULL DEFAULT 'US',
> currency_code char(3) DEFAULT 'USD',
> isbn char(13),
> sales_rank integer,
>
Hello, all.
I'm finding that write performance of a certain stored procedure is
abysmal. I need to be able to sustain approximately 20 calls to this
procedure per second, but am finding that, on the average, each call
takes 2 seconds in itself, in addition to pegging a single processor
at 100% for
Greg/Tom/Josh,
Thanks for your comments about this problem...very much
appreciated. We have resolve the issue by re-doing the query partly
based on your advice and partly just spending more time in analysis.
There's one oddball thing we turned up which I'm including below in the
full serie
I have a partitioned table with a multi-column unique index. The table is
partitioned on a timestamp with time zone column. (I realize this has nothing
to do with the unique index.) The original unique index was in the order
(timestamptz, varchar, text, text) and most queries against it were
On Fri, Jun 26, 2009 at 10:33 AM, Greg Smith wrote:
> On Thu, 11 Jun 2009, Tom Lane wrote:
>
>> Matthew Wakeling writes:
>>>
>>> Oprofile scares me with the sheer number of options.
>>
>> You can ignore practically all of them; the defaults are pretty sane.
>> The recipe I usually follow is:
>
> A
On Thu, 11 Jun 2009, Tom Lane wrote:
Matthew Wakeling writes:
Oprofile scares me with the sheer number of options.
You can ignore practically all of them; the defaults are pretty sane.
The recipe I usually follow is:
An excellent brain dump from Tom and lots of other good stuff in this
th
On Fri, Jun 26, 2009 at 9:34 AM, Janet Jacobsen wrote:
> I assume that killing the user's process released the lock on the
> table. This user has only SELECT privileges. Under what
> conditions would a SELECT lock a table. The user connects
> to the database via a (Python?) script that runs on
Thank you for the answers. Very helpful.
Between the time that I sent my original post and saw your reply,
I tried to drop a couple of foreign key constraints. The alter
table statements also showed up as "waiting" when I ran ps aux.
I took your suggestion to run pg_locks and pg_stat_activit
19 matches
Mail list logo
