Re: [PERFORM] Compression in PG

potentially cause a performance hit on queries. My question is if PG can compress smaller text fields e.g 0.5-1KB, or must I do this outside PG? -- Shaul On Sun, Nov 1, 2009 at 5:57 PM, Scott Marlowe wrote: > On Sun, Nov 1, 2009 at 7:56 AM, Pavel Stehule > wrote: > > 2009/11

[PERFORM] Compression in PG

Hi, I have several long text fields in my DB that I would love to compress (descriptions, URLs etc). Can you tell me what options exists in PG (+pointers please), typical effect on space and run time? Thanks, -- Shaul

Re: [PERFORM] Finding rows in table T1 that DO NOT MATCH any row in table T2

the IN versions suggested will not work since AFAIK IN only works for a single value. -- Shaul On Tue, Oct 20, 2009 at 3:59 PM, Tom Lane wrote: > Shaul Dar writes: > > I assume this will work but will take a long time: > > > DELETE * FROM T1 where T1.PK NOT IN > > (S

Re: [PERFORM] Finding rows in table T1 that DO NOT MATCH any row in table T2

How about: DELETE * FROM T1 LEFT JOIN T2 ON T1.PK <http://t1.pk/> = T2.FK<http://t2.fk/> WHERE T2.FK IS NULL Shaul On Tue, Oct 20, 2009 at 2:37 PM, Shaul Dar wrote: > Hi, > > I have two large tables T1 and T2, such that T2 has a FK to T1 (i.e. T2.FK--> > T1.PK, po

[PERFORM] Finding rows in table T1 that DO NOT MATCH any row in table T2

Hi, I have two large tables T1 and T2, such that T2 has a FK to T1 (i.e. T2.FK--> T1.PK, possibly multiple T2 rows may reference the same T1 row). I have deleted about 2/3 of table T2. I now want to delete all rows in T1 that are not referenced by T2, i.e. all rows in T1 that cannot join with (any

Re: [PERFORM] Getting a random row

Jaśkiewicz wrote: > > On Tue, Oct 13, 2009 at 4:17 PM, Shaul Dar wrote: >> >> >> Also PG does not have a concept of an auto-increment pseudo-column like >>> Oracle's "rownum". Any suggestions? >>> >>> not true - it has sequences, and

Re: [PERFORM] Getting a random row

not to change table + the new column would also become nonconsecutive after further deletions. The nice thing about Oracle's "rownum" is that it' a pseudo-column", not a real one, and AFAIK is always valid. Suggestions? -- Shaul 2009/10/13 Grzegorz Jaśkiewicz > >

[PERFORM] Getting a random row

Hi, I am running performance simulation against a DB. I want to randomly pull different records from a large table. However the table has no columns that hold sequential integer values (1..MAX), i.e. the columns all have "holes" (due to earlier filtering). Also PG does not have a concept of an aut

[PERFORM] Dumping + restoring a subset of a table?

Hi everyone, I am looking for a way to dump+restore a subset of a database (on another server), using both selection and projection of the source tables (for simplicity assume a single table). I understand that pg_dump will not let me do this. One way I considered is creating a view with the subse

Re: [PERFORM] Postgres replication: dump/restore, PITR, Slony,...?

All right, so I misspelled Bucardo (also Mammoth...), and the company's name is Command Prompt (please get someone to work on that incomprehensible logo - I went back and looked at it and still have no clue what it means :-). Now how about some serious answers relating to my questions? Dimitri, t

[PERFORM] Postgres replication: dump/restore, PITR, Slony,...?

Hi, Our configuration is as follows: 1. A staging server, which receives new data and updates the DB 2. Two web servers that have copies of the DB (essentially read-only) and answer user queries (with load balancer) Currently we use dump (to SQL file, i.e. pg_dump with no args) + file copy to re

Re: [PERFORM] Best way to load test a postgresql server

On Thu, Jun 4, 2009 at 2:01 AM, Greg Smith wrote: > > If you want to load test your own specific DB then I am unaware of any >> such tools. >> > > pgbench will run against any schema and queries, the built-in set are just > the easiest to use. I just released a bunch of slides and a package I n

Re: [PERFORM] Best way to load test a postgresql server

I considered Tsung myself but haven't tried it. If you intend to, I suggest you read this excellent tutorial on using Tsung for test-loading Postgresql. While impressed I decided the procedure was too daunting and went with JMeter :-)

Re: [PERFORM] Best way to load test a postgresql server

Hi Peter, I was looking for the same recently, and my answer is as follows: 1. If you want to test the *H/W and configuration of your DBMS* then you can use the pgbench tool (which uses a specific built-in DB+schema, following the TPC benchmark). 2. If you want to *load test your own specific DB

[PERFORM] Using index for bitwise operations?

Hi, I have at column that is a bit array of 16, each bit specifying if a certain property, out of 16, is present or not. Our typical query select 300 "random" rows (could be located in different blocks) from the table based on another column+index, and then filters them down to ~50 based on this t

[PERFORM] Postgresql cache (memory) performance + how to warm up the cache

I have a DB table with 25M rows, ~3K each (i.e. ~75GB), that together with multiple indexes I use (an additional 15-20GB) will not fit entirely in memory (64GB on machine). A typical query locates 300 rows thru an index, optionally filters them down to ~50-300 rows using other indexes, finally fetc

[PERFORM] Putting tables or indexes in SSD or RAM: avoiding double caching?

y - right? Is there a way to avoid that, i.e. to tell postgress NOT to cache tablespaces, or some other granularity of the DB? Thanks, -- Shaul *Dr. Shaul Dar* Email: i...@shauldar.com Web: www.shauldar.com