from:"vincent"

On 6/14/06, Scott Marlowe <[EMAIL PROTECTED]> wrote:
On Wed, 2006-06-14 at 09:47, John E. Vincent wrote:> -- this is the third time I've tried sending this and I never saw it get> through to the list. Sorry if multiple copies show up.>> Hi all,
BUNCHES SNIPPED> work_mem = 1048576 ( I know this is high but you should see some of our> sorts and aggregates)Ummm. That's REALLY high. You might want to consider lowering theglobal value here, and then crank it up on a case by case basis, like
during nighttime report generation. Just one or two queries couldtheoretically run your machine out of memory right now. Just put a "setwork_mem=100" in your script before the big query runs.
I know it is but that's what we need for some of our queries. Our ETL tool (informatica) and BI tool (actuate) won't let us set those things as part of our jobs. We need it for those purposes. We have some really nasty queries that will be fixed in our new server.
E.G. we have a table called loan_account_agg_fact that has 200+ million rows and it contains every possible combination of late status for a customer account (i.e. 1 day late, 2 day late, 3 day late) so it gets inserted for new customers but updated for existing records as part of our warehouse load. Part of the new layout is combining late ranges so instead of number of days we have a range of days (
i.e. 1-15,16-30). Even with work_mem that large, the load of that loan_account_agg_fact table creates over 3GB of temp tables!
That's exactly what we do. We just do a normal backup, and have ascript that gzips anything in the backup directory that doesn't end in.gz... If you've got space to burn, as you say, then use it at least a
few days to see how it affects backup speeds.Seeing as how you're CPU bound, most likely the problem is just thecompressed backup.I'm starting to think the same thing. I'll see how this COPY I'm doing of the single largest table does right now and make some judgement based on that.
-- John E. Vincent

Re: [PERFORM] Performance of pg_dump on PGSQL 8.0

Out of curiosity, does anyone have any idea what the ratio of actual datasize to backup size is if I use the custom format with -Z 0 compression or the tar format? Thanks.On 6/14/06, 
Scott Marlowe <[EMAIL PROTECTED]> wrote:
On Wed, 2006-06-14 at 09:47, John E. Vincent wrote:> -- this is the third time I've tried sending this and I never saw it get> through to the list. Sorry if multiple copies show up.>> Hi all,
BUNCHES SNIPPED> work_mem = 1048576 ( I know this is high but you should see some of our> sorts and aggregates)Ummm.  That's REALLY high.  You might want to consider lowering theglobal value here, and then crank it up on a case by case basis, like
during nighttime report generation.  Just one or two queries couldtheoretically run your machine out of memory right now.  Just put a "setwork_mem=100" in your script before the big query runs.
> We're inserting around 3mil rows a night if you count staging, info, dim> and fact tables. The vacuum issue is a whole other problem but right now> I'm concerned about just the backup on the current hardware.
>> I've got some space to burn so I could go to an uncompressed backup and> compress it later during the day.That's exactly what we do.  We just do a normal backup, and have ascript that gzips anything in the backup directory that doesn't end in
.gz...  If you've got space to burn, as you say, then use it at least afew days to see how it affects backup speeds.Seeing as how you're CPU bound, most likely the problem is just thecompressed backup.
-- John E. Vincent[EMAIL PROTECTED]

Re: [PERFORM] Performance of pg_dump on PGSQL 8.0

On 6/14/06, Scott Marlowe <[EMAIL PROTECTED]> wrote:
Description of "Queries gone wild" redacted.  hehe.Yeah, I've seen those kinds of queries before too.  you might be able tolimit your exposure by using alter user:alter user userwhoneedslotsofworkmem set work_mem=100;
Is this applicable on  8.0? We were actually LOOKING for a governor of some sort for these queries.  And something that is not explicitly stated, is that allocated up front or is that just a ceiling?
and then only that user will have that big of a default.  You could evenmake it so that only queries that need that much log in as that user,
and all other queries log in as other folks.  Just a thought.  I justget REAL nervous seeing a production machine with a work_mem set thathigh.Which is actually how it's configured. We have a dedicated user connecting from  Actuate. The reports developers use thier own logins when developing new reports. Only when they get published do they convert to the Actuate user.
-- John E. Vincent[EMAIL PROTECTED]

Re: [PERFORM] Performance of pg_dump on PGSQL 8.0

time gzip -6 claDW_PGSQL.test.bakreal    3m4.360suser    1m22.090ssys 0m6.050sWhich is still less time than it would take to do a compressed pg_dump. On 6/14/06, 
Scott Marlowe <[EMAIL PROTECTED]> wrote:
How long does gzip take to compress this backup?On Wed, 2006-06-14 at 15:59, John Vincent wrote:> Okay I did another test dumping using the uncompressed backup on the> system unloaded and the time dropped down to 8m for the backup.
> There's still the size issue to contend with but as I said, I've got a> fair bit of space left on the SAN to work with.

Re: [PERFORM] Performance of pg_dump on PGSQL 8.0

On 6/14/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
On Wed, Jun 14, 2006 at 02:11:19PM -0400, John Vincent wrote:> Out of curiosity, does anyone have any idea what the ratio of actual> datasize to backup size is if I use the custom format with -Z 0 compression
> or the tar format?-Z 0 should mean no compression.But the custom format is still a binary backup, no?
Something you can try is piping the output of pg_dump to gzip/bzip2. Onsome OSes, that will let you utilize 1 CPU for just the compression. Ifyou wanted to get even fancier, there is a parallelized version of bzip2
out there, which should let you use all your CPUs.Or if you don't care about disk IO bandwidth, just compress after thefact (though, that could just put you in a situation where pg_dumpbecomes bandwidth constrained).
Unfortunately if we working with our current source box, the 1 CPU is already the bottleneck in regards to compression. If I run the pg_dump from the remote server though, I might be okay.
--Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

[PERFORM] Optimizer internals

I'm not a programmer so understanding the optimizer code is WAY beyond my limits.My question, that I haven't seen answered elsewhere, is WHAT things can affect the choice of an index scan over a sequence scan. I understand that sometimes a sequence scan is faster and that you still have to get the data from the disk but my question relates to an issue we had pop up today.
We have 2 tables, which we'll refer to as laaf and laaf_new. The first table has 220M rows and the second table has 4M rows. What were basically doing is aggregating the records from the first table into the second one at which point we're going to drop the first one. This is the same table I mentioned previously in my post about pg_dump.
laaf_new has one less column than laaf and both were freshly vacuum analyzed after having an index added on a single column (other than the primary key). The query we were doing was as follows:select main_account_status_dim_id, count(*)
from cla_dw.loan_account_agg_fact_newgroup by main_account_status_dim_idorder by main_account_status_dim_id; One of our problems is that we don't have any PGSQL dbas here. All of our guys are DB2 (we're still looking though).
Now I've been told by our DBA that we should have been able to wholy satisfy that query via the indexes.We did regular EXPLAINS on the query with seqscan enabled and disabled and even in our own tests actually running the queries, the results WERE faster with a seq scan than an index scan but the question we were discussing is WHY did it choose the index scan and why is the index scan slower than the sequence scan? He's telling me that DB2 would have been able to do the whole thing with indexes.
EXPLAINS:(the reason for the random_page_cost was that we had the default of 4 in the .conf file and were planning on changing it to 2 anyway to match our other server)set random_page_cost=2;set enable_seqscan=on;
explain select main_account_status_dim_id, count(*)from cla_dw.loan_account_agg_factgroup by main_account_status_dim_idorder by main_account_status_dim_id; "Sort  (cost=8774054.54..8774054.66 rows=48 width=4)"
"  Sort Key: main_account_status_dim_id""  ->  HashAggregate  (cost=8774052.60..8774053.20 rows=48 width=4)""    ->  Seq Scan on loan_account_agg_fact  (cost=0.00..7609745.40
 rows=232861440 width=4)"set random_page_cost=2;set enable_seqscan=off;explain select main_account_status_dim_id, count(*)from cla_dw.loan_account_agg_factgroup by main_account_status_dim_id
order by main_account_status_dim_id; "Sort  (cost=108774054.54..108774054.66 rows=48 width=4)""  Sort Key: main_account_status_dim_id""  ->  HashAggregate  (cost=108774052.60..108774053.20
 rows=48 width=4)""    ->  Seq Scan on loan_account_agg_fact  (cost=1.00..107609745.40 rows=232861440 width=4)"Here's the DDL for the table laaf:When the system is not busy again, I'll run a verbose version. The query was run against each of the tables to compare the results of aggregation change with the new table.
CREATE TABLE cla_dw.loan_account_agg_fact(  loan_account_agg_fact_id int8 NOT NULL DEFAULT nextval('loan_account_agg_fact_loan_account_agg_fact_id_seq'::regclass),  dw_load_date_id int4 NOT NULL DEFAULT 0,
  servicer_branch_dim_id int4 NOT NULL DEFAULT 0,  main_account_status_dim_id int4 NOT NULL DEFAULT 0,  product_dim_id int4 NOT NULL DEFAULT 0,  next_due_date_id int4 NOT NULL DEFAULT 0,  account_balance numeric(15,6) NOT NULL DEFAULT 0,
  loan_count int4 NOT NULL DEFAULT 0,  principal numeric(15,6) NOT NULL DEFAULT 0,  interest numeric(15,6) NOT NULL DEFAULT 0,  fees numeric(15,6) NOT NULL DEFAULT 0,  gl_principal numeric(15,6) NOT NULL DEFAULT 0,
  gl_interest numeric(15,6) NOT NULL DEFAULT 0,  accruable_principal numeric(15,6) NOT NULL DEFAULT 0,  unaccruable_principal numeric(15,6) NOT NULL DEFAULT 0,  calculated_principal numeric(15,6) DEFAULT 0,
  current_interest numeric(15,6) NOT NULL DEFAULT 0,  past_due_interest numeric(16,5) NOT NULL DEFAULT 0,  cash_available numeric(15,6) DEFAULT 0,  cash_collected numeric(15,6) DEFAULT 0,  cash_collected_date_id int4 DEFAULT 0,
  dw_agg_load_dt timestamp(0) DEFAULT ('now'::text)::timestamp(6) with time zone,  cash_available_principal numeric(15,6) DEFAULT 0,  cash_available_current numeric(15,6) DEFAULT 0,  cash_available_last numeric(15,6) DEFAULT 0,
  cash_available_interest numeric(15,6) DEFAULT 0,  cash_available_fees numeric(15,6) DEFAULT 0,  cash_not_collected numeric(15,6) DEFAULT 0,  number_contacts_total int4 DEFAULT 0,  number_broken_commitments int4 DEFAULT 0,
  loc_current_due_total numeric(15,6) DEFAULT 0,  loc_current_due_principal numeric(15,6) DEFAULT 0,  loc_current_due_interest numeric(15,6) DEFAULT 0,  loc_current_due_fees numeric(15,6) DEFAULT 0,  loc_past_due_last numeric(15,6) DEFAULT 0,
  loc_past_due_total numeric(15,6) DEFAULT 0,  number_made_commitments int4 DEFAULT 0,  CONSTRAINT loan_account_agg_fact_pkey PRIMARY KEY (loan_account_agg_fact_id)) WITH OIDS;CREATE INDEX loan_account_agg_fact_main_a

Re: [PERFORM] Optimizer internals

On 6/15/06, Mark Lewis <[EMAIL PROTECTED]> wrote:
DB2 can satisfy the query using only indexes because DB2 doesn't doMVCC.Although MVCC is generally a win in terms of making the database easierto use and applications less brittle, it also means that the database
must inspect the visibility information for each row before it cananswer a query.  For most types of queries this isn't a big deal, butfor count(*) type queries, it slows things down.
Mark,Thanks for the answer. My DBAs just got this look on thier face when I showed. It's not like the couldn't have investigated this information themselves but I think the light finally came on.One question that we came up with is how does this affect other aggregate functions like MAX,MIN,SUM and whatnot? Being that this is our data warehouse, we use these all the time. As I've said previously, I didn't know a human could generate some of the queries we've passed through this system.
Since adding the visibility information to indexes would make themsignificantly more expensive to use and maintain, it isn't done.
Therefore, each row has to be fetched from the main table anyway.Since in this particular query you are counting all rows of thedatabase, PG must fetch each row from the main table regardless, so thesequential scan is much faster because it avoids traversing the index
and performing random read operations.-- Mark Lewis

Re: [PERFORM] Optimizer internals

On 6/15/06, Mark Lewis <[EMAIL PROTECTED]> wrote:
Unfortunately SUM is in the same boat as COUNT; in order for it toreturn a meaningful result it must inspect visibility information forall of the rows.-- MarkWe'll this is interesting news to say the least. We went with PostgreSQL for our warehouse because we needed the advanced features that MySQL didn't have at the time (views/sprocs).
It sounds like we almost need another fact table for the places that we do SUM (which is not a problem just an additional map. If I'm interpreting this all correctly, we can't force PG to bypass a sequence scan even if we know our data is stable because of the MVCC aspect. In our case, as with most warehouses (except those that do rolling loads during the day), we only write data to it for about 5 hours at night in batch. 
Any suggestions? FYI the original question wasn't meant as a poke at comparing PG to MySQL to DB2. I'm not making an yvalue judgements either way. I'm just trying to understand how we can use it the best way possible.
If anyone from the bizgres team is watching, have they done any work in this area? Thanks.John

Re: [PERFORM] Optimizer internals

Any suggestions? FYI the original question wasn't meant as a poke at comparing PG to MySQL to DB2. I'm not making an yvalue judgements either way. I'm just trying to understand how we can use it the best way possible.
If anyone from the bizgres team is watching, have they done any work in this area? Thanks.John

Actually we just thought about something. With PG, we can create
an index that is a SUM of the column where indexing, no? We're going to
test this in a few hours. Would that be able to be satisfied by an
index scan?
Also, we're looking at the link provided for the materialized views in PG.Thanks.

Re: [PERFORM] SAN performance mystery

On 6/15/06, Tim Allen <[EMAIL PROTECTED]> wrote:
Is that expected performance, anyone? It doesn't sound right to me. Doesanyone have any clues about what might be going on? Buggy kerneldrivers? Buggy kernel, come to think of it? Does a SAN just not provide
adequate performance for a large database?I'd be grateful for any clues anyone can offer,TimTim,Here are the areas I would look at first if we're considering hardware to be the problem:
HBA and driver: Since this is a Intel/Linux system, the HBA is PROBABLY a qlogic. I would need to know the SAN model to see what the backend of the SAN is itself. EMC has some FC-attach models that actually have SATA disks underneath. You also might want to look at the cache size of the controllers on the SAN.
- Something also to note is that EMC provides a add-on called PowerPath for load balancing multiple HBAs. If they don't have this, it might be worth investigating. - As with anything, disk layout is important. With the lower end IBM SAN (DS4000) you actually have to operate on physical spindle level. On our 4300, when I create a LUN, I select the exact disks I want and which of the two controllers are the preferred path. On our DS6800, I just ask for storage. I THINK all the EMC models are the "ask for storage" type of scenario. However with the 6800, you select your storage across extent pools.
Have they done any benchmarking of the SAN outside of postgres? Before we settle on a new LUN configuration, we always do the dd,umount,mount,dd routine. It's not a perfect test for databases but it will help you catch GROSS performance issues.
SAN itself: - Could the SAN be oversubscribed? How many hosts and LUNs total do they have and what are the queue_depths for those hosts? With the qlogic card, you can set the queue depth in the BIOS of the adapter when the system is booting up. CTRL-Q I think. If the system has enough local DASD to relocate the database internally, it might be a valid test to do so and see if you can isolate the problem to the SAN itself.
PG itself: If you think it's a pgsql configuration, I'm guessing you already
configured postgresql.conf to match thiers (or at least a fraction of
thiers since the memory isn't the same?). What about loading a "from-scratch" config file and restarting the tuning process?
Just a dump of my thought process from someone who's been spending too much time tuning his SAN and postgres lately.

Re: [PERFORM] Optimizer internals

decibel=# create index test on i ( sum(i) );ERROR:  cannot use aggregate function in index _expression_
decibel=#BTW, there have been a number of proposals to negate the effect of nothaving visibility info in indexes. Unfortunately, none of them have cometo fruition yet, mostly because it's a very difficult problem to solve.
But it is something that the community would like to see happen.--Jim C. Nasby, Sr. Engineering Consultant  
[EMAIL PROTECTED]Pervasive Software  
http://pervasive.comwork: 512-231-6117vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461
Yeah we got the same thing when we tried it.
I thought about the whole thing on the way home and the downside is that we might have to ditch pgsql.As far as implementing it, it might make sense to translate READ UNCOMMITTED to that new functionality. If the default isolation level stays the current level, the people who need it can use it via WITH UR or somesuch.
I know it's not that easy but it's an idea. I'm also thinking that the table inheritance we're going to be taking advantage of in 8.1 on the new server might make the sequence scan less of an issue. The only reason the sequence scan really blows is that we have a single table with 220M rows and growing.

Re: [PERFORM] SAN performance mystery

2006-06-19 Thread John Vincent

On 6/19/06, Tim Allen <[EMAIL PROTECTED]> wrote:
As I noted in another thread, the HBA is an Emulex LP1050, and they havea rather old driver for it. I've recommended that they update ASAP. Thishasn't happened yet.Yeah, I saw that in a later thread. I would suggest also that the BIOS settings on the HBA itself have been investigated. An example is the Qlogic HBAs have a profile of sorts, one for tape and one for disk. Could be something there.
OK, thanks, I'll ask the customer whether they've used PowerPath at all.
They do seem to have it installed on the machine, but I suppose thatdoesn't guarantee it's being used correctly. However, it looks like theyhave just the one HBA, so, if I've correctly understood what loadbalancing means in this context, it's not going to help; right?
If they have a single HBA then no it won't help. I'm not very intimate on powerpath but it might even HURT if they have it enabled with one HBA. As an example, we were in the process of migrating an AIX LPAR to our DS6800. We only had one spare HBA to assign it. The default policy with the SDD driver is lb (load balancing). The problem is that with the SDD driver you see multiple hdisks per HBA per controller port on the SAN. Since we had 4 controller ports active on the SAN, our HBA saw 4 hdisks per LUN. The SDD driver abstracts that out as a single vpath and you use the vpaths as your pv on the system. The problem was that it was attempting to load balance across a single hba which was NOT what we wanted.
I've done some dd'ing myself, as described in another thread. The
results are not at all encouraging - their SAN seems to do about 20MB/sor less.I saw that as well.
The SAN possibly is over-subscribed. Can you suggest any easy ways forme to find out? The customer has an IT department who look after theirSANs, and they're not keen on outsiders poking their noses in. It's hard
for me to get any direct access to the SAN itself.When I say over-subscribed, you have to look at all the active LUNs and all of the systems attached as well. With the DS4300 (standard not turbo option), the SAN can handle 512 I/Os per second. If I have 4 LUNs assigned to four systems (1 per system), and each LUN has a queue_depth of 128 from each system, I''ll oversubscribe with the next host attach unless I back the queue_depth off on each host. Contrast that with the Turbo controller option which does 1024 I/Os per sec and I can duplicate what I have now or add a second LUN per host. I can't even find how much our DS6800 supports.
Thanks for all the suggestions, John. I'll keep trying to follow some of
them up.From what I can tell, it sounds like the SATA problem other people have mentioned sounds like the culprit.

Re: [PERFORM] SAN performance mystery

2006-06-19 Thread John Vincent


I'd have to agree with you about the specific SAN/setup you're working
with there.  I certainly disagree that it's a general property of SAN'sthough.  We've got a DS4300 with FC controllers and drives, hosts aregenerally dual-controller load-balanced and it works quite decently. 
How are you guys doing the load balancing? IIRC, the RDAC driver only does failover. Or are you using the OS level multipathing instead? While we were on the 4300 for our AIX boxes, we just created two big RAID5 LUNs and assigned one to each controller. With 2 HBAs and LVM stripping that was about the best we could get in terms of load balancing.
Indeed, the EMC SANs are generally the high-priced ones too, so not
really sure what to tell you about the poor performance you're seeing
out of it.  Your IT folks and/or your EMC rep. should be able to resolvethat, really...The only exception I've heard to this is the Clarion AX150. We looked at one and we were warned off of it by some EMC gearheads.

Re: [PERFORM] Optimizer internals

2006-06-21 Thread John Vincent

Any suggestions? FYI the original question wasn't meant as a poke at comparing PG to MySQL to DB2. I'm not making an yvalue judgements either way. I'm just trying to understand how we can use it the best way possible.
Actually we just thought about something. With PG, we can create an index that is a SUM of the column where indexing, no? We're going to test this in a few hours. Would that be able to be satisfied by an index scan?

Re: [PERFORM] SAN performance mystery

2006-06-21 Thread John Vincent

I'd have to agree with you about the specific SAN/setup you're working
with there.  I certainly disagree that it's a general property of SAN'sthough.  We've got a DS4300 with FC controllers and drives, hosts aregenerally dual-controller load-balanced and it works quite decently.
How are you guys doing the load balancing? IIRC, the RDAC driver only does failover. Or are you using the OS level multipathing instead? While we were on the 4300 for our AIX boxes, we just created two big RAID5 LUNs and assigned one to each controller. With 2 HBAs and LVM stripping that was about the best we could get in terms of load balancing.
Indeed, the EMC SANs are generally the high-priced ones too, so notreally sure what to tell you about the poor performance you're seeing
out of it.  Your IT folks and/or your EMC rep. should be able to resolvethat, really...The only exception I've heard to this is the Clarion AX150. We looked at one and we were warned off of it by some EMC gearheads.
Enjoy,Stephen-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.3 (GNU/Linux)iD8DBQFElpuRrzgMPqB3kigRAuo8AJ9vlxRK7VPMb9rN7AFm/qMNHLbdBwCfZiihZHApIcDhhj/J/Es9KPXEl/s==25MX-END PGP SIGNATURE-

[PERFORM] Degenerate Performance Problem

2008-12-09 Thread Vincent Predoehl

I have postgresql 8.3.5 installed on MacOS X / Darwin.  I remember  
setting shared memory buffer parameters and that solved the initial  
performance problem, but after running several tests, the performance  
goes way, way down. Restarting the server doesn't seem to help.


I'm using pqxx to access the database, if that makes any difference.

--
Vincent

[PERFORM] DELETE taking too much memory

2011-07-07 Thread vincent dephily

Hi,

I have a delete query taking 7.2G of ram (and counting) but I do not
understant why so much memory is necessary. The server has 12G, and
I'm afraid it'll go into swap. Using postgres 8.3.14.

I'm purging some old data from table t1, which should cascade-delete
referencing rows in t2. Here's an anonymized rundown :


# \d t1
 Table "public.t1"
  Column   |Type | Modifiers
---+-+-
 t1id  | integer | not null default
nextval('t1_t1id_seq'::regclass)
(...snip...)
Indexes:
"message_pkey" PRIMARY KEY, btree (id)
(...snip...)

# \d t2
   Table "public.t2"
 Column  |Type |Modifiers
-+-+-
 t2id| integer | not null default
nextval('t2_t2id_seq'::regclass)
 t1id| integer | not null
 foo | integer | not null
 bar | timestamp without time zone | not null default now()
Indexes:
"t2_pkey" PRIMARY KEY, btree (t2id)
"t2_bar_key" btree (bar)
"t2_t1id_key" btree (t1id)
Foreign-key constraints:
"t2_t1id_fkey" FOREIGN KEY (t1id) REFERENCES t1(t1id) ON UPDATE
RESTRICT ON DELETE CASCADE

# explain delete from t1 where t1id in (select t1id from t2 where
foo=0 and bar < '20101101');
   QUERY PLAN
-
 Nested Loop  (cost=5088742.39..6705282.32 rows=30849 width=6)
   ->  HashAggregate  (cost=5088742.39..5089050.88 rows=30849 width=4)
 ->  Index Scan using t2_bar_key on t2  (cost=0.00..5035501.50
rows=21296354 width=4)
   Index Cond: (bar < '2010-11-01 00:00:00'::timestamp
without time zone)
   Filter: (foo = 0)
   ->  Index Scan using t1_pkey on t1  (cost=0.00..52.38 rows=1 width=10)
 Index Cond: (t1.t1id = t2.t1id)
(7 rows)


Note that the estimate of 30849 rows is way off : there should be
around 55M rows deleted from t1, and 2-3 times as much from t2.

When looking at the plan, I can easily imagine that data gets
accumulated below the nestedloop (thus using all that memory), but why
isn't each entry freed once one row has been deleted from t1 ? That
entry isn't going to be found again in t1 or in t2, so why keep it
around ?

Is there a better way to write this query ? Would postgres 8.4/9.0
handle things better ?



Thanks in advance.


-- 
Vincent de Phily

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Explain analyze time overhead

2013-12-05 Thread vincent elschot



On 05-12-13 15:09, salah jubeh wrote:


Hello guys,

When I excute a query,  the exection time is about 1 minute; however, 
when I execute the query with explain analyze the excution time jumps 
to 10 minutes.
I have tried this for several queries, where  I need to optimize; and 
using explain analyze leads alway to a huge time overhead in factor of 10.


This is a little bit starnge for me; did any one experience somthing 
like this? Can I trust the generated plans?


Regards


Explain analyze does a lot more work than just explaining the query, it 
excecutes it and takes not of how long things actually took, which 
itself takes time. Apparently on some machines, it can take much longer 
than just executing the query would take.


From the manual:
"In order to measure the run-time cost of each node in the execution 
plan, the current implementation ofEXPLAIN ANALYZEadds profiling 
overhead to query execution. As a result, runningEXPLAIN ANALYZEon a 
query can sometimes take significantly longer than executing the query 
normally. The amount of overhead depends on the nature of the query, as 
well as the platform being used. The worst case occurs for plan nodes 
that in themselves require very little time per execution, and on 
machines that have relatively slow operating system calls for obtaining 
the time of day."

Re: [PERFORM] CPU load spikes when CentOS tries to reclaim 'cached' memory

2014-06-05 Thread Vincent Lasmarias

Thanks for the informative responses and suggestions. My responses below:

* Sorry for the double post. I posted the original message using my gmail
account and got a "is not a member of any of the restrict_post groups"
response and when I didn't see it for a day, I ended up wondering if it was
due to my use of a gmail account - so I tried using my company email account
instead to post an updated version of the original post.

* Our system is not virtualized.

* Jeff, the output format of the load and free/cached memory did not come
from a tool but came from my script. My script does OEuptime; free m¹, and
then another script massages the data to only grab date, 1-minute load
average, free, and cached.

* For the 'top' outputs, I don't have the actual 'top' output, but I have
the following:

2014-05-30 00:01:01 procs ---memory-- ---swap-- -io
--system-- -cpu-
2014-05-30 00:01:01  r  b   swpd   free   buff  cache   si   sobibo
in   cs us sy id wa st
(...)
2014-05-30 04:59:52  1  0  0 621976  92340 2343134000 0
16756 3538 3723  7  2 91  0  0
2014-05-30 04:59:53  1  0  0 671896  92340 2343136400 0
236 1933  825  2  1 97  0  0
2014-05-30 04:59:54  2  0  0 542964  92340 2343359600  2148
300 3751 1394  6  1 92  0  0
2014-05-30 04:59:55  0  0  0 566140  92340 2343361600 0
192 3485 1465  6  1 94  0  0
2014-05-30 04:59:56  2  0  0 614348  92340 2343376000 0
424 3238 4278  4  1 95  0  0
2014-05-30 04:59:57  4  0  0 408812  92340 2343379200 8
944 6249 12512 12  2 86  0  0
2014-05-30 04:59:58  3  0  0 471716  92356 2343401200 0
440 9028 4164 13  1 86  0  0
2014-05-30 04:59:59  4  0  0 380988  92356 2343442800 0
248 10009 10967 15  3 83  0  0
2014-05-30 05:00:00  6  0  0 462052  92356 2343490400 0
960 7260 9242 12  2 85  0  0
2014-05-30 05:00:01  5  0  0 409796  92360 234352240096
1860 11475 95765 18  7 75  0  0
2014-05-30 05:00:02 10  0  0 221464  92360 2343386800   428
10800 13933 128264 23  9 67  0  0
2014-05-30 05:00:03 12  0  0 231444  91956 2335564400 0
1480 26651 10817 10 35 54  0  0
2014-05-30 05:00:04 11  0  0 385672  91508 2325412000 0
3096 30849 44776 22 28 50  0  0
2014-05-30 05:00:05  9  0  0 408932  91508 2327021600 0
1996 21925 24978 12 26 63  0  0
2014-05-30 05:00:06 10  0  0 373992  91508 2327058000 0
2160 25778 5994 11 31 58  0  0
2014-05-30 05:00:07  5  0  0 457900  91508 2327068800 0
6080 25185 11705 14 21 65  0  0
2014-05-30 05:00:08  0  0  0 658300  91508 2327080400 0
2089 5989 5849 11  2 88  0  0
2014-05-30 05:00:09  1  0  0 789972  91508 2327092800 0
2508 2346 2550  2  1 97  0  0
2014-05-30 05:00:10  0  0  0 845736  91508 232742600012
1728 2109 1494  2  1 97  0  0
2014-05-30 05:00:11  3  0  0 686352  91516 2327464400 8
2100 4039 5288  6  2 92  0  0
2014-05-30 05:00:12 11  1  0 447636  91516 2327480800   520
1436 10299 50523 24  7 68  1  0
2014-05-30 05:00:13 13  0  0 352380  91516 2327622000  1060
816 18283 18682 15 36 48  1  0
2014-05-30 05:00:14 12  0  0 356720  0 2317927600   704
868 16193 140313 36 12 51  1  0
2014-05-30 05:00:15  5  0  0 513784  0 2317334400  2248
748 12350 21178 30  6 62  2  0
2014-05-30 05:00:16  2  0  0 623020  4 2317580800  1568
500 5841 4999 12  2 86  1  0
2014-05-30 05:00:17  5  0  0 590488  4 231758440024
584 6573 4905 14  2 84  0  0
2014-05-30 05:00:18  3  0  0 632408  4 2317611600 0
496 6846 4358 14  2 84  0  0
2014-05-30 05:00:19  5  0  0 596716  4 2317694800   656
668 7135 5262 14  3 83  0  0
2014-05-30 05:00:20  6  0  0 558692  4 2317996400  2816
1012 8566 7742 17  4 79  0  0
2014-05-30 05:00:21  7  1  0 476580  8 2318120000  1272
968 11240 14308 23  6 71  1  0
2014-05-30 05:00:22  8  0  0 695396  8 2318302800   728
1128 9751 7121 22  4 74  1  0
2014-05-30 05:00:23  9  0  0 536084  8 2319908000   392
1024 12523 22269 26  6 68  0  0
2014-05-30 05:00:24 13  0  0 416296  8 232004160040
1000 16319 61822 29 21 51  0  0
2014-05-30 05:00:25 14  0  0 386904  8 232007040024
816 20850 4424 16 38 46  0  0
2014-05-30 05:00:26 17  0  0 334688  88896 232010280024
1000 26758 16934 24 46 30  0  0
2014-05-30 05:00:27 18  0  0 307304  88896 2319392800 0
1068 27051 67778 21 46 33  0  0
2014-05-30 05:00:28 20  1  0 295560  88896 2316245600 0
860 31012 27787 15 67 18  0  0
2014-05-30 05:00:29 22  1  0 281272  88896 231533120016
928 28899 2857  9 78 13  0  0
2014-05-30 05:00:30 26  0  0 400804  87976 2297932400 0
1536 37689 4368  9

Re: [PERFORM] Recursive query performance issue

2015-10-22 Thread vincent elschot




On 20-10-15 19:34, Jamie Koceniak wrote:


Version:

---

PostgreSQL 9.1.14 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 
4.7.2-5) 4.7.2, 64-bit


Query Plan

http://explain.depesz.com/s/4s37

Normally, this query takes around 200-300 ms to execute.

However when several queries are run concurrently, query performance 
drops to 30-60 seconds.



Is the concurrency the cause or the result of the slowdown?
Are you executing the same query with the same parameters or do the 
parameters differ, perhaps making PostgreSQL

choose different queryplan?

[PERFORM] Estimation row error

2015-12-11 Thread Mathieu VINCENT

Hello,

I would like to know how row estimation is calculed by explain ?
In my execution plan, this estimation is extremely wrong (267 instead of
198000)
I reproduced this estimation error in this simple case :

drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
drop table if exists t4;

create table t1 as select generate_Series(1,30) as c1;
create table t2 as select generate_Series(1,400) as c1;
create table t3 as select generate_Series(1,20)%100 as
c1,generate_Series(1,20) as c2;
create table t4 as select generate_Series(1,20) as c1;

alter table t1 add PRIMARY KEY (c1);
alter table t2 add PRIMARY KEY (c1);
alter table t3 add PRIMARY KEY (c1,c2);
create index on t3 (c1);
create index on t3 (c2);
alter table t4 add PRIMARY KEY (c1);

analyze t1;
analyze t2;
analyze t3;
analyze t4;

EXPLAIN (analyze on, buffers on, verbose on)
select
*
from
t1 t1
inner join t2 on t1.c1=t2.c1
inner join t3 on t2.c1=t3.c1
inner join t4 on t3.c2=t4.c1

Explain plan :
http://explain.depesz.com/s/wZ3v

I think this error may be problematic because planner will choose nested
loop instead of hash joins for ultimate join. Can you help me to improve
this row estimation ?

Thank you for answering

Best Regards,
<http://www.psih.fr/>PSIH Décisionnel en santé
Mathieu VINCENT
Data Analyst
PMSIpilot - 61 rue Sully - 69006 Lyon - France

Re: [PERFORM] Estimation row error

2015-12-11 Thread Mathieu VINCENT

Sorry, I forget to precise Postgresql version

'PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7
20120313 (Red Hat 4.4.7-11), 64-bit'


BR

Mathieu VINCENT



2015-12-11 9:53 GMT+01:00 Mathieu VINCENT :

> Hello,
>
> I would like to know how row estimation is calculed by explain ?
> In my execution plan, this estimation is extremely wrong (267 instead of
> 198000)
> I reproduced this estimation error in this simple case :
>
> drop table if exists t1;
> drop table if exists t2;
> drop table if exists t3;
> drop table if exists t4;
>
> create table t1 as select generate_Series(1,30) as c1;
> create table t2 as select generate_Series(1,400) as c1;
> create table t3 as select generate_Series(1,20)%100 as
> c1,generate_Series(1,20) as c2;
> create table t4 as select generate_Series(1,20) as c1;
>
> alter table t1 add PRIMARY KEY (c1);
> alter table t2 add PRIMARY KEY (c1);
> alter table t3 add PRIMARY KEY (c1,c2);
> create index on t3 (c1);
> create index on t3 (c2);
> alter table t4 add PRIMARY KEY (c1);
>
> analyze t1;
> analyze t2;
> analyze t3;
> analyze t4;
>
> EXPLAIN (analyze on, buffers on, verbose on)
> select
> *
> from
> t1 t1
> inner join t2 on t1.c1=t2.c1
> inner join t3 on t2.c1=t3.c1
> inner join t4 on t3.c2=t4.c1
>
> Explain plan :
> http://explain.depesz.com/s/wZ3v
>
> I think this error may be problematic because planner will choose nested
> loop instead of hash joins for ultimate join. Can you help me to improve
> this row estimation ?
>
> Thank you for answering
>
> Best Regards,
> <http://www.psih.fr/>PSIH Décisionnel en santé
> Mathieu VINCENT
> Data Analyst
> PMSIpilot - 61 rue Sully - 69006 Lyon - France
>

Re: [PERFORM] Estimation row error

2015-12-15 Thread Mathieu VINCENT

Hello,

No one to help me to understand this bad estimation rows ?

Mathieu VINCENT

2015-12-11 12:35 GMT+01:00 Mathieu VINCENT :

> Sorry, I forget to precise Postgresql version
>
> 'PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7
> 20120313 (Red Hat 4.4.7-11), 64-bit'
>
>
> BR
>
> Mathieu VINCENT
>
>
>
> 2015-12-11 9:53 GMT+01:00 Mathieu VINCENT :
>
>> Hello,
>>
>> I would like to know how row estimation is calculed by explain ?
>> In my execution plan, this estimation is extremely wrong (267 instead of
>> 198000)
>> I reproduced this estimation error in this simple case :
>>
>> drop table if exists t1;
>> drop table if exists t2;
>> drop table if exists t3;
>> drop table if exists t4;
>>
>> create table t1 as select generate_Series(1,30) as c1;
>> create table t2 as select generate_Series(1,400) as c1;
>> create table t3 as select generate_Series(1,20)%100 as
>> c1,generate_Series(1,20) as c2;
>> create table t4 as select generate_Series(1,20) as c1;
>>
>> alter table t1 add PRIMARY KEY (c1);
>> alter table t2 add PRIMARY KEY (c1);
>> alter table t3 add PRIMARY KEY (c1,c2);
>> create index on t3 (c1);
>> create index on t3 (c2);
>> alter table t4 add PRIMARY KEY (c1);
>>
>> analyze t1;
>> analyze t2;
>> analyze t3;
>> analyze t4;
>>
>> EXPLAIN (analyze on, buffers on, verbose on)
>> select
>> *
>> from
>> t1 t1
>> inner join t2 on t1.c1=t2.c1
>> inner join t3 on t2.c1=t3.c1
>> inner join t4 on t3.c2=t4.c1
>>
>> Explain plan :
>> http://explain.depesz.com/s/wZ3v
>>
>> I think this error may be problematic because planner will choose nested
>> loop instead of hash joins for ultimate join. Can you help me to improve
>> this row estimation ?
>>
>> Thank you for answering
>>
>> Best Regards,
>> <http://www.psih.fr/>PSIH Décisionnel en santé
>> Mathieu VINCENT
>> Data Analyst
>> PMSIpilot - 61 rue Sully - 69006 Lyon - France
>>
>
>

Re: [PERFORM] Estimation row error

2015-12-17 Thread Mathieu VINCENT

thks Gunnar,

I removed the correlation between t3.c1 and t3.c2 in this sql script :

drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
drop table if exists t4;

create table t1 as select generate_Series(1,30) as c1;
create table t2 as select generate_Series(1,400) as c1;
create table t3 as select floor(random()*100+1) as c1, c2 from
generate_Series(1,20) c2;
create table t4 as select generate_Series(1,20) as c1;

alter table t1 add PRIMARY KEY (c1);
alter table t2 add PRIMARY KEY (c1);
alter table t3 add PRIMARY KEY (c1,c2);
create index on t3 (c1);
create index on t3 (c2);
alter table t4 add PRIMARY KEY (c1);

analyze verbose t1;
analyze verbose t2;
analyze verbose t3;
analyze verbose t4;

EXPLAIN (analyze on, buffers on, verbose on)
select
*
from
t1 t1
inner join t2 on t1.c1=t2.c1
inner join t3 on t2.c1=t3.c1
inner join t4 on t3.c2=t4.c1

Now, the estimate is good : http://explain.depesz.com/s/gCX

Have a good day

Mathieu VINCENT

2015-12-15 11:21 GMT+01:00 Gunnar "Nick" Bluth <
gunnar.bluth.ext...@elster.de>:

> Am 15.12.2015 um 10:49 schrieb Andreas Kretschmer:
> > Gunnar Nick Bluth  wrote:
> >
> >> Am 15.12.2015 um 09:05 schrieb Mathieu VINCENT:
> >>> Hello,
> >>>
> >>> No one to help me to understand this bad estimation rows ?
> >>
> >> Well,
> >>
> >> on a rather beefy machine, I'm getting quite a different plan:
> >> http://explain.depesz.com/s/3y5r
> >
> > you are using 9.5, right? Got the same plan with 9.5.
>
> Nope...:
>   version
>
>
> 
>  PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc
> (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
>
> So much for those correlation improvements then ;-/
>
>
> > Btw.: Hi Gunnar ;-)
>
> Hi :)
>
> --
> Gunnar "Nick" Bluth
> DBA ELSTER
>
> Tel:   +49 911/991-4665
> Mobil: +49 172/8853339
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

Re: [PERFORM] Estimation row error

2015-12-17 Thread Mathieu VINCENT

Here, another issue with row estimate.
And, in this example, there is not correlation beetween columns in a same
table.

drop table if exists t1;
drop table if exists t2;
drop table if exists t3;

create table t1 as select generate_Series(1,20) as c1;
create table t2 as select generate_Series(1,20)%100 as c1;
create table t3 as select generate_Series(1,1500)%750 as c1;

alter table t1 add PRIMARY KEY (c1);
create index on t2 (c1);
create index on t3 (c1);

analyze verbose t1;
analyze verbose t2;
analyze verbose t3;

EXPLAIN (analyze on, buffers on, verbose on)
select
*
from
t1 t1
inner join t2 on t1.c1=t2.c1
inner join t3 on t2.c1=t3.c1
the explain plan : http://explain.depesz.com/s/YVw
Do you understand how postgresql calculate the row estimate ?

BR
Mathieu VINCENT

2015-12-17 10:14 GMT+01:00 Matteo Grolla :

> Thank you both for the help!
> happy holidays
>
> 2015-12-17 10:10 GMT+01:00 Mathieu VINCENT 
> :
>
>> thks Gunnar,
>>
>> I removed the correlation between t3.c1 and t3.c2 in this sql script :
>>
>> drop table if exists t1;
>> drop table if exists t2;
>> drop table if exists t3;
>> drop table if exists t4;
>>
>> create table t1 as select generate_Series(1,30) as c1;
>> create table t2 as select generate_Series(1,400) as c1;
>> create table t3 as select floor(random()*100+1) as c1, c2 from
>> generate_Series(1,20) c2;
>> create table t4 as select generate_Series(1,20) as c1;
>>
>> alter table t1 add PRIMARY KEY (c1);
>> alter table t2 add PRIMARY KEY (c1);
>> alter table t3 add PRIMARY KEY (c1,c2);
>> create index on t3 (c1);
>> create index on t3 (c2);
>> alter table t4 add PRIMARY KEY (c1);
>>
>> analyze verbose t1;
>> analyze verbose t2;
>> analyze verbose t3;
>> analyze verbose t4;
>>
>> EXPLAIN (analyze on, buffers on, verbose on)
>> select
>> *
>> from
>> t1 t1
>> inner join t2 on t1.c1=t2.c1
>> inner join t3 on t2.c1=t3.c1
>> inner join t4 on t3.c2=t4.c1
>>
>> Now, the estimate is good : http://explain.depesz.com/s/gCX
>>
>> Have a good day
>>
>> Mathieu VINCENT
>>
>> 2015-12-15 11:21 GMT+01:00 Gunnar "Nick" Bluth <
>> gunnar.bluth.ext...@elster.de>:
>>
>>> Am 15.12.2015 um 10:49 schrieb Andreas Kretschmer:
>>> > Gunnar Nick Bluth  wrote:
>>> >
>>> >> Am 15.12.2015 um 09:05 schrieb Mathieu VINCENT:
>>> >>> Hello,
>>> >>>
>>> >>> No one to help me to understand this bad estimation rows ?
>>> >>
>>> >> Well,
>>> >>
>>> >> on a rather beefy machine, I'm getting quite a different plan:
>>> >> http://explain.depesz.com/s/3y5r
>>> >
>>> > you are using 9.5, right? Got the same plan with 9.5.
>>>
>>> Nope...:
>>>   version
>>>
>>>
>>> 
>>>  PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc
>>> (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
>>>
>>> So much for those correlation improvements then ;-/
>>>
>>>
>>> > Btw.: Hi Gunnar ;-)
>>>
>>> Hi :)
>>>
>>> --
>>> Gunnar "Nick" Bluth
>>> DBA ELSTER
>>>
>>> Tel:   +49 911/991-4665
>>> Mobil: +49 172/8853339
>>>
>>>
>>> --
>>> Sent via pgsql-performance mailing list (
>>> pgsql-performance@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-performance
>>>
>>
>>
>

Re: [PERFORM] Estimation row error

2015-12-17 Thread Mathieu VINCENT

Adding foreign key between on t2 and t3, does not change the plan.

drop table if exists t1;
drop table if exists t2;
drop table if exists t3;

create table t1 as select generate_Series(1,20) as c1;
create table t2 as select generate_Series(1,20)%100+1 as c1;
create table t3 as select generate_Series(1,1500)%750+1 as c1;

alter table t1 add PRIMARY KEY (c1);
create index on t2 (c1);
create index on t3 (c1);
ALTER TABLE t2  ADD CONSTRAINT t2_fk FOREIGN KEY (c1) REFERENCES t1(c1);
ALTER TABLE t3  ADD CONSTRAINT t3_fk FOREIGN KEY (c1) REFERENCES t1(c1);

analyze verbose t1;
analyze verbose t2;
analyze verbose t3;

EXPLAIN (analyze on, buffers on, verbose on)
select
*
from
t1 t1
inner join t2 on t1.c1=t2.c1
inner join t3 on t1.c1=t3.c1

Cordialement,
<http://www.psih.fr/>PSIH Décisionnel en santé
Mathieu VINCENT
Data Analyst
PMSIpilot - 61 rue Sully - 69006 Lyon - France

2015-12-17 11:37 GMT+01:00 Mathieu VINCENT :

> Here, another issue with row estimate.
> And, in this example, there is not correlation beetween columns in a same
> table.
>
> drop table if exists t1;
> drop table if exists t2;
> drop table if exists t3;
>
> create table t1 as select generate_Series(1,20) as c1;
> create table t2 as select generate_Series(1,20)%100 as c1;
> create table t3 as select generate_Series(1,1500)%750 as c1;
>
> alter table t1 add PRIMARY KEY (c1);
> create index on t2 (c1);
> create index on t3 (c1);
>
> analyze verbose t1;
> analyze verbose t2;
> analyze verbose t3;
>
> EXPLAIN (analyze on, buffers on, verbose on)
> select
> *
> from
> t1 t1
> inner join t2 on t1.c1=t2.c1
> inner join t3 on t2.c1=t3.c1
> the explain plan : http://explain.depesz.com/s/YVw
> Do you understand how postgresql calculate the row estimate ?
>
> BR
> Mathieu VINCENT
>
> 2015-12-17 10:14 GMT+01:00 Matteo Grolla :
>
>> Thank you both for the help!
>> happy holidays
>>
>> 2015-12-17 10:10 GMT+01:00 Mathieu VINCENT > >:
>>
>>> thks Gunnar,
>>>
>>> I removed the correlation between t3.c1 and t3.c2 in this sql script :
>>>
>>> drop table if exists t1;
>>> drop table if exists t2;
>>> drop table if exists t3;
>>> drop table if exists t4;
>>>
>>> create table t1 as select generate_Series(1,30) as c1;
>>> create table t2 as select generate_Series(1,400) as c1;
>>> create table t3 as select floor(random()*100+1) as c1, c2 from
>>> generate_Series(1,20) c2;
>>> create table t4 as select generate_Series(1,20) as c1;
>>>
>>> alter table t1 add PRIMARY KEY (c1);
>>> alter table t2 add PRIMARY KEY (c1);
>>> alter table t3 add PRIMARY KEY (c1,c2);
>>> create index on t3 (c1);
>>> create index on t3 (c2);
>>> alter table t4 add PRIMARY KEY (c1);
>>>
>>> analyze verbose t1;
>>> analyze verbose t2;
>>> analyze verbose t3;
>>> analyze verbose t4;
>>>
>>> EXPLAIN (analyze on, buffers on, verbose on)
>>> select
>>> *
>>> from
>>> t1 t1
>>> inner join t2 on t1.c1=t2.c1
>>> inner join t3 on t2.c1=t3.c1
>>> inner join t4 on t3.c2=t4.c1
>>>
>>> Now, the estimate is good : http://explain.depesz.com/s/gCX
>>>
>>> Have a good day
>>>
>>> Mathieu VINCENT
>>>
>>> 2015-12-15 11:21 GMT+01:00 Gunnar "Nick" Bluth <
>>> gunnar.bluth.ext...@elster.de>:
>>>
>>>> Am 15.12.2015 um 10:49 schrieb Andreas Kretschmer:
>>>> > Gunnar Nick Bluth  wrote:
>>>> >
>>>> >> Am 15.12.2015 um 09:05 schrieb Mathieu VINCENT:
>>>> >>> Hello,
>>>> >>>
>>>> >>> No one to help me to understand this bad estimation rows ?
>>>> >>
>>>> >> Well,
>>>> >>
>>>> >> on a rather beefy machine, I'm getting quite a different plan:
>>>> >> http://explain.depesz.com/s/3y5r
>>>> >
>>>> > you are using 9.5, right? Got the same plan with 9.5.
>>>>
>>>> Nope...:
>>>>   version
>>>>
>>>>
>>>> 
>>>>  PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc
>>>> (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
>>>>
>>>> So much for those correlation improvements then ;-/
>>>>
>>>>
>>>> > Btw.: Hi Gunnar ;-)
>>>>
>>>> Hi :)
>>>>
>>>> --
>>>> Gunnar "Nick" Bluth
>>>> DBA ELSTER
>>>>
>>>> Tel:   +49 911/991-4665
>>>> Mobil: +49 172/8853339
>>>>
>>>>
>>>> --
>>>> Sent via pgsql-performance mailing list (
>>>> pgsql-performance@postgresql.org)
>>>> To make changes to your subscription:
>>>> http://www.postgresql.org/mailpref/pgsql-performance
>>>>
>>>
>>>
>>
>

Re: [PERFORM] Estimation row error

2015-12-18 Thread Mathieu VINCENT

Hello,

No one to help me to understand this bad estimation rows ?
It's *NOT* caused by :

   - correlation between columns (cross-correlation)
   - bad statistics (i tried with  default_statistics_target to 10 000)
   - bad number of distinct values
   - complexe join conditions

I have no more ideas.

thank you for your help.
Mathieu VINCENT

2015-12-17 11:58 GMT+01:00 Mathieu VINCENT :

> Adding foreign key between on t2 and t3, does not change the plan.
>
> drop table if exists t1;
> drop table if exists t2;
> drop table if exists t3;
>
> create table t1 as select generate_Series(1,20) as c1;
> create table t2 as select generate_Series(1,20)%100+1 as c1;
> create table t3 as select generate_Series(1,1500)%750+1 as c1;
>
> alter table t1 add PRIMARY KEY (c1);
> create index on t2 (c1);
> create index on t3 (c1);
> ALTER TABLE t2  ADD CONSTRAINT t2_fk FOREIGN KEY (c1) REFERENCES t1(c1);
> ALTER TABLE t3  ADD CONSTRAINT t3_fk FOREIGN KEY (c1) REFERENCES t1(c1);
>
> analyze verbose t1;
> analyze verbose t2;
> analyze verbose t3;
>
> EXPLAIN (analyze on, buffers on, verbose on)
> select
> *
> from
> t1 t1
> inner join t2 on t1.c1=t2.c1
> inner join t3 on t1.c1=t3.c1
>
> Cordialement,
> <http://www.psih.fr/>PSIH Décisionnel en santé
> Mathieu VINCENT
> Data Analyst
> PMSIpilot - 61 rue Sully - 69006 Lyon - France
>
> 2015-12-17 11:37 GMT+01:00 Mathieu VINCENT 
> :
>
>> Here, another issue with row estimate.
>> And, in this example, there is not correlation beetween columns in a same
>> table.
>>
>> drop table if exists t1;
>> drop table if exists t2;
>> drop table if exists t3;
>>
>> create table t1 as select generate_Series(1,20) as c1;
>> create table t2 as select generate_Series(1,20)%100 as c1;
>> create table t3 as select generate_Series(1,1500)%750 as c1;
>>
>> alter table t1 add PRIMARY KEY (c1);
>> create index on t2 (c1);
>> create index on t3 (c1);
>>
>> analyze verbose t1;
>> analyze verbose t2;
>> analyze verbose t3;
>>
>> EXPLAIN (analyze on, buffers on, verbose on)
>> select
>> *
>> from
>> t1 t1
>> inner join t2 on t1.c1=t2.c1
>> inner join t3 on t2.c1=t3.c1
>> the explain plan : http://explain.depesz.com/s/YVw
>> Do you understand how postgresql calculate the row estimate ?
>>
>> BR
>> Mathieu VINCENT
>>
>> 2015-12-17 10:14 GMT+01:00 Matteo Grolla :
>>
>>> Thank you both for the help!
>>> happy holidays
>>>
>>> 2015-12-17 10:10 GMT+01:00 Mathieu VINCENT <
>>> mathieu.vinc...@pmsipilot.com>:
>>>
>>>> thks Gunnar,
>>>>
>>>> I removed the correlation between t3.c1 and t3.c2 in this sql script :
>>>>
>>>> drop table if exists t1;
>>>> drop table if exists t2;
>>>> drop table if exists t3;
>>>> drop table if exists t4;
>>>>
>>>> create table t1 as select generate_Series(1,30) as c1;
>>>> create table t2 as select generate_Series(1,400) as c1;
>>>> create table t3 as select floor(random()*100+1) as c1, c2 from
>>>> generate_Series(1,20) c2;
>>>> create table t4 as select generate_Series(1,20) as c1;
>>>>
>>>> alter table t1 add PRIMARY KEY (c1);
>>>> alter table t2 add PRIMARY KEY (c1);
>>>> alter table t3 add PRIMARY KEY (c1,c2);
>>>> create index on t3 (c1);
>>>> create index on t3 (c2);
>>>> alter table t4 add PRIMARY KEY (c1);
>>>>
>>>> analyze verbose t1;
>>>> analyze verbose t2;
>>>> analyze verbose t3;
>>>> analyze verbose t4;
>>>>
>>>> EXPLAIN (analyze on, buffers on, verbose on)
>>>> select
>>>> *
>>>> from
>>>> t1 t1
>>>> inner join t2 on t1.c1=t2.c1
>>>> inner join t3 on t2.c1=t3.c1
>>>> inner join t4 on t3.c2=t4.c1
>>>>
>>>> Now, the estimate is good : http://explain.depesz.com/s/gCX
>>>>
>>>> Have a good day
>>>>
>>>> Mathieu VINCENT
>>>>
>>>> 2015-12-15 11:21 GMT+01:00 Gunnar "Nick" Bluth <
>>>> gunnar.bluth.ext...@elster.de>:
>>>>
>>>>> Am 15.12.2015 um 10:49 schrieb Andreas Kretschmer:
>>>>> > Gunnar Nick Bluth  wrote:
>>>>> >
>>>>> >> Am 15.12.2015 um 09:05 schrieb Mathieu VINCENT:
>>>>> >>> Hello,
>>>>> >>

[PERFORM] Speed differences between two servers

2017-05-08 Thread Vincent Veyron

using tblcontentieux_pkey on tblcontentieux t1  
(cost=0.28..4.02 rows=1 width=116) (actual time=0.010..0.011 rows=1 loops=124)
   Index Cond: (id_contentieux = tblcontentieux_log.id_contentieux)
   Filter: (id_client = 13)
   Rows Removed by Filter: 0
   Buffers: shared hit=372
 Planning time: 1.311 ms
 Execution time: 10.813 ms


Both are bare metal servers, with 4GB of RAM; the dataset is small (compressed 
dump is 3MB). The main differences that I found are in disk I/O as shown by 
hdparm, and processor type :

#Kimsufi server 
hdparm -tT /dev/sda
 Timing cached reads:   1744 MB in  2.00 seconds = 872.16 MB/sec
 Timing buffered disk reads: 482 MB in  3.00 seconds = 160.48 MB/sec
Processor Intel(R) Atom(TM) CPU N2800   @ 1.86GHz (4 cores, cache size  : 512 
KB)
Disk 2TB, 7200rpm, db on 500MB partition

#Online server
hdparm -tT /dev/sda
 Timing cached reads:   2854 MB in  2.00 seconds = 1427.05 MB/sec
 Timing buffered disk reads: 184 MB in  3.00 seconds =  61.26 MB/sec
Processor Intel(R) Atom(TM) CPU  C2350  @ 1.74GHz (2 cores, cache size  : 1024 
KB)
Disk 1TB, 7200rpm, db on 1TB partition

I've created two pastebins with the output of the following commands for each 
server:
# hdparm /dev/sda
# hdparm -i /dev/sda
# df
# cat /proc/cpuinfo
# cat /proc/meminfo

#Kimsufi server
https://pastebin.com/3860hS92

#Online server
https://pastebin.com/FT1HFbD7


My questions: 

-Does the difference in 'buffered disk reads' explain the 6 fold increase in 
execution time for truncate/copy on the Online server?

-Why are regular queries much faster on this same server?




-- 
Bien à vous, Vincent Veyron 

https://legalcase.libremen.com/
Legal case management software


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Speed differences between two servers

2017-05-08 Thread Vincent Veyron

On Mon, 8 May 2017 12:48:29 -0600
Scott Marlowe  wrote:

Hi Scott,

Thank you for your input.

> 
> The most likely cause of the difference would be that one server IS
> honoring fsync requests from the db and the other one isn't.
> 
> If you run pgbench on both (something simple like pgbench -c 1 -T 60,
> aka one thread for 60 seconds) on a machine running on a 7200RPM hard
> drive, you should get approximately 120 transactions per second

Here are the results :

#Kimsufi
pgbench -c 1 -T 60 test
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 60 s
number of transactions actually processed: 6618
latency average: 9.069 ms
tps = 110.270771 (including connections establishing)
tps = 110.283733 (excluding connections establishing)

#Online
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 60 s
number of transactions actually processed: 1150
latency average: 52.317 ms
tps = 19.114403 (including connections establishing)
tps = 19.115739 (excluding connections establishing)

> 
> > -Why are regular queries much faster on this same server?
> 
> That's a whole nother subject. Most likely the faster machine can fit
> the whole db in memory, or has much faster memory, or the whole
> dataset is cached etc etc.
> 

The dataset is small (35 MB) and both servers have 4GB memory. It appears to be 
faster on the Online server.

using 'dmidecode -t 17' :

#Kimsufi
Memory Device
Array Handle: 0x0016
Error Information Handle: Not Provided
Total Width: 64 bits
Data Width: 64 bits
Size: 2048 MB
Form Factor: DIMM
Set: None
Locator: SO DIMM 0
Bank Locator: Channel A DIMM0
Type: DDR3
Type Detail: Synchronous
Speed: 1066 MHz
Manufacturer: 0x
Serial Number: 0x
Asset Tag: Unknown
Part Number: 0x
Rank: Unknown
Configured Clock Speed: 1066 MHz

[repeated for second locator]

#Online
Memory Device
Array Handle: 0x0015
Error Information Handle: No Error
Total Width: Unknown
Data Width: Unknown
Size: 4096 MB
Form Factor: DIMM
Set: None
Locator: DIMM0
Bank Locator: BANK 0
Type: DDR3
Type Detail: Synchronous Unbuffered (Unregistered)
Speed: 1600 MHz
Manufacturer: 
Serial Number: 
Asset Tag: 
Part Number: 
Rank: 1
Configured Clock Speed: 1333 MHz
Minimum voltage:  Unknown
Maximum voltage:  Unknown
Configured voltage:  Unknown

-- 
Bien à vous, Vincent Veyron 

https://libremen.com
Logiciels de gestion, libres

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Speed differences between two servers

2017-05-09 Thread Vincent Veyron

On Mon, 8 May 2017 17:35:38 -0600
Scott Marlowe  wrote:
> Without seeing what your test sql file does I have no idea what the
> big difference in the other direction. 

It truncates 59 tables, copies data back from a set of text files, inserts a 
few single records and does a few select setval('') to reset the serial columns.

here it is :
https://pastebin.com/LVsvFzkj

>You'll have to pull out and run
> the individual queries, or turn on auto explain or something to see
> the plans and compare. 

I used log_duration; it shows that the truncate and all the \copy are much 
slower, while all insert/select statements are twice as fast

>A lot of time it's just some simple tuning in
> postgresql.conf or maybe a database got an alter database on it to
> change something? 

Server setups are identical : same software, same configurations, same 
databases.

I've put in a ticket at the Online provider with the data to see if they have 
an answer (now 14H00 in Paris, so they may take a while to respond)


-- 
    Bien à vous, Vincent Veyron 

https://compta.libremen.com
Logiciel libre de comptabilité générale en partie double



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Speed differences between two servers

2017-05-09 Thread Vincent Veyron


Well, the response to the ticket was quite fast :

https://status.online.net/index.php?do=details&task_id=720

Here's the stated cause :

>Our tests have confirmed an issue caused by the fans of the power supplies 
>installed in several chassis.

>The fans create vibrations amplifying errors on the discs.

Now on to decide whether I'm waiting for the fix or re-building a new box...

Thanks a bunch for your help.


-- 
        Bien à vous, Vincent Veyron 

https://libremen.com
Logiciels de gestion, libres


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Speed differences between two servers

2017-05-10 Thread Vincent Veyron

On Tue, 9 May 2017 10:24:20 -0600
Scott Marlowe  wrote:
> 
> As for the hard drives, can you upgrade to a pair of SSDs? If your
> data set fits on (and will continue to fit on) SSDs, the performance
> gained from SSDs is HUGE and worth a few hundred extra for the drive.
> Note that you want to use the Intel enterprise stuff that survives
> power loss, not the cheap low end SSDs. May be an easy fix for your
> hosting company and a big performance gain.
> 

Sure, but I'm getting plenty of performance already : my little machines can 
serve 40 requests/second with 6 or 7 queries per request. So I'm fine for a 
while.

You can see for yourself if you enter the demo account for the site in my sig, 
and click in a couple of files (it's the database that gets re-created by the 
procedure I mentionned in my original post)


-- 
        Bien à vous, Vincent Veyron 

https://legalcase.libremen.com/ 
Legal case, contract and insurance claim management software



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Performance of pg_dump on PGSQL 8.0

2006-06-14 Thread John E. Vincent

-- this is the third time I've tried sending this and I never saw it get 
through to the list. Sorry if multiple copies show up.


Hi all,

I've been lurking using the web archives for a while and haven't found 
an answer that seems to answer my questions about pg_dump.


We have a 206GB data warehouse running on version 8.0.3. The server is 
somewhat underpowered in terms of CPU: (1) 2.8 GHz Xeon 4GB Ram and a 
single HBA to our SAN (IBM DS4300). We in the process of migrating to a 
new server that we've repurposed from our production OLTP database (8) 
2.0 GHz Xeon, 16GB Ram and dual HBAs to the same SAN running version 8.1.


Independant of that move, we still need to get by on the old system and 
I'm concerned that even on the new system, pg_dump will still perform 
poorly. I can't do a full test because we're also taking advantage of 
the table partitioning in 8.1 so we're not doing a dump and restore.


We backup the database using:

pg_dump -Fc -cv ${CURDB} > ${BACKDIR}/${CURDB}-${DATE}.bak

There a three different LUNs allocated to the old warehouse on the SAN - 
data, wal and a dump area for the backups. The SAN has two controllers 
(only 128MB of cache per) and the data is on one controller while the 
WAL and dump area are on the other. Still a single HBA though.


Creating the compressed backup of this database takes 12 hours. We start 
at 6PM and it's done a little after 1AM, just in time for the next day's 
load. The load itself takes about 5 hours.


I've watched the backup process and I/O is not a problem. Memory isn't a 
problem either. It seems that we're CPU bound but NOT in I/O wait. The 
server is a dedicated PGSQL box.


Here are our settings from the conf file:

maintenance_work_mem = 524288
work_mem = 1048576 ( I know this is high but you should see some of our 
sorts and aggregates)

shared_buffers = 5
effective_cache_size = 45
wal_buffers = 64
checkpoint_segments = 256
checkpoint_timeout = 3600

We're inserting around 3mil rows a night if you count staging, info, dim 
and fact tables. The vacuum issue is a whole other problem but right now 
I'm concerned about just the backup on the current hardware.


I've got some space to burn so I could go to an uncompressed backup and 
compress it later during the day.


If there are any tips anyone can provide I would greatly appreciate it. 
I know that the COPY performance was bumped up in 8.1 but I'm stuck on 
this 8.0 box for a while longer.


Thanks,
John E. Vincent

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

[PERFORM] slow query : very simple delete, 100% cpu, nearly no disk activity

2009-09-06 Thread Vincent de Phily

Hi list,

I've been running this simple delete since yesterday afternoon :
> db=# explain delete from message where datetime < '2009-03-03';
>  Seq Scan on message  (cost=0.00..34131.95 rows=133158 width=6)
>Filter: (datetime < '2009-03-03 00:00:00'::timestamp without time zone)

There is no index on that column, so a seqscan is fine. But it really 
shouldn't take > 15 hours to delete :

> db=# select count(*) from message where datetime < '2009-03-03';
> 184368
> Time: 751.721 ms
>
> db=# select count(*) from message;
> 1079463
> Time: 593.899 ms
>
> db=# select pg_size_pretty(pg_relation_size('message')); 
> 161 MB
> Time: 96.062 ms
>
> db=# \o /dev/null 
> db=# select * from message where datetime < '2009-03-03';
> Time: 4975.123 ms


Most of the time, there is no other connection to that database. This is on an 
oldish laptop. atop reports 100% cpu and about 24KB/s of writes for postgres. 
Machine is mostly idle (although I did run a multi-hours compile during the 
night). Nothing looks wrong in postgres logs.

PostgreSQL 8.3.7 on i686-pc-linux-gnu, compiled by GCC i686-pc-linux-gnu-gcc 
(Gentoo 4.3.2-r3 p1.6, pie-10.1.5) 4.3.2

postgresql.conf :
> max_connections = 100
> shared_buffers = 24MB
> max_fsm_pages = 153600
> log_destination = 'stderr'
> logging_collector = on
> log_directory = '/var/log/postgres/'
> log_filename = '%Y-%m-%d_%H%M%S.log'
> log_rotation_size = 100MB
> log_min_duration_statement = 3
> log_line_prefix = '%t %d %p '
> datestyle = 'iso, mdy'
> lc_messages = 'C'
> lc_monetary = 'C'
> lc_numeric = 'C'
> lc_time = 'C'
> default_text_search_config = 'pg_catalog.english'


Not sure what to look at to debug this further (I could work around the 
problem with pg_dump + grep, but that's beside the point). Any idea ?


Thanks.

-- 
Vincent de Phily
Mobile Devices
+33 (0) 666 301 306
+33 (0) 142 119 325

Warning
This message (and any associated files) is intended only for the use of its
intended recipient and may contain information that is confidential, subject
to copyright or constitutes a trade secret. If you are not the intended
recipient you are hereby notified that any dissemination, copying or
distribution of this message, or files associated with this message, is
strictly prohibited. If you have received this message in error, please
notify us immediately by replying to the message and deleting it from your
computer. Any views or opinions presented are solely those of the author
vincent.deph...@mobile-devices.fr and do not necessarily represent those of 
the
company. Although the company has taken reasonable precautions to ensure no
viruses are present in this email, the company cannot accept responsibility
for any loss or damage arising from the use of this email or attachments.

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] slow query : very simple delete, 100% cpu, nearly no disk activity

2009-09-10 Thread Vincent de Phily

On Monday 07 September 2009 03:25:23 Tom Lane wrote:
> Vincent de Phily  writes:
> > I've been running this simple delete since yesterday afternoon :
> >> db=# explain delete from message where datetime < '2009-03-03';
> >> Seq Scan on message  (cost=0.00..34131.95 rows=133158 width=6)
> >> Filter: (datetime < '2009-03-03 00:00:00'::timestamp without time zone)
> >
> > There is no index on that column, so a seqscan is fine. But it really
> > shouldn't take > 15 hours to delete :
>
> 99% of the time, the reason a delete takes way longer than it seems like
> it should is trigger firing time.  In particular, foreign key triggers
> where you don't have an index on the referencing column.  Are there
> any foreign keys linking to this table?

Yes, but they look fine to me (?). Only one FK references the table; it's an 
internal reference :

 Table "public.message"
  Column   |Type |  Modifiers
---+-+--
 id| integer | not null default 
nextval('message_id_seq'::regclass)
 unitid| integer | not null
 userid| integer |
 refid | integer |
(...)
Indexes:
"message_pkey" PRIMARY KEY, btree (id)
"message_unitid_fromto_status_idx" btree (unitid, fromto, status)
"message_userid_idx" btree (userid)
Foreign-key constraints:
"message_refid_fkey" FOREIGN KEY (refid) REFERENCES message(id) ON UPDATE 
CASCADE ON DELETE CASCADE
"message_unitid_fkey" FOREIGN KEY (unitid) REFERENCES units(id) ON UPDATE 
CASCADE ON DELETE CASCADE
"message_userid_fkey" FOREIGN KEY (userid) REFERENCES users(id) ON UPDATE 
CASCADE ON DELETE CASCADE

  Table "public.units"
   Column|Type | Modifiers
-+-+
 id  | integer | not null default 
nextval('units_id_seq'::regclass)
(...)
Indexes:
"units_pkey" PRIMARY KEY, btree (id)
"units_modid_ukey" UNIQUE, btree (modid)
"units_profileid_idx" btree (profileid)
Foreign-key constraints:
"units_profileid_fkey" FOREIGN KEY (profileid) REFERENCES profiles(id) ON 
UPDATE CASCADE ON DELETE RESTRICT

 Table "public.users"
  Column  | Type  | Modifiers
--+---+
 id   | integer   | not null default 
nextval('users_id_seq'::regclass)
(...)
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
"users_login_ukey" UNIQUE, btree (login)

Table users has a handdull of rows, table units has around 4. 43% of 
message.refid is NULL.

The delete finished during the weekend (DELETE 184368). Nothing in the logs 
except the duration time (103113291.307 ms). I took a db dump before the 
delete finished, in order to be able to reproduce the issue (a 30min test 
shows me it is still slow).

-- 
Vincent de Phily
Mobile Devices
+33 (0) 666 301 306
+33 (0) 142 119 325

Warning
This message (and any associated files) is intended only for the use of its
intended recipient and may contain information that is confidential, subject
to copyright or constitutes a trade secret. If you are not the intended
recipient you are hereby notified that any dissemination, copying or
distribution of this message, or files associated with this message, is
strictly prohibited. If you have received this message in error, please
notify us immediately by replying to the message and deleting it from your
computer. Any views or opinions presented are solely those of the author
vincent.deph...@mobile-devices.fr and do not necessarily represent those of 
the
company. Although the company has taken reasonable precautions to ensure no
viruses are present in this email, the company cannot accept responsibility
for any loss or damage arising from the use of this email or attachments.

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] slow query : very simple delete, 100% cpu, nearly no disk activity

2009-09-21 Thread Vincent de Phily

On Friday 11 September 2009 23:30:37 Robert Haas wrote:
> On Mon, Sep 7, 2009 at 5:05 AM, Vincent de Phily
>  wrote:
> > On Monday 07 September 2009 03:25:23 Tom Lane wrote:
> >>
> >> 99% of the time, the reason a delete takes way longer than it seems like
> >> it should is trigger firing time.  In particular, foreign key triggers
> >> where you don't have an index on the referencing column.  Are there
> >> any foreign keys linking to this table?
> >
> > Yes, but they look fine to me (?). Only one FK references the table; it's
> > an internal reference :
> >
(...)
> I would try EXPLAIN ANALYZE DELETE ... with a query that is modified
> so as to delete only a handful of rows.  That will report the amount
> of time spent in triggers vs. the main query, which will help you
> assess whether your conclusion that the foreign keys are OK is
> correct.

Good idea. I'll try that in a little while and report the result.

-- 
Vincent de Phily
Mobile Devices
+33 (0) 666 301 306
+33 (0) 142 119 325

Warning
This message (and any associated files) is intended only for the use of its
intended recipient and may contain information that is confidential, subject
to copyright or constitutes a trade secret. If you are not the intended
recipient you are hereby notified that any dissemination, copying or
distribution of this message, or files associated with this message, is
strictly prohibited. If you have received this message in error, please
notify us immediately by replying to the message and deleting it from your
computer. Any views or opinions presented are solely those of the author
vincent.deph...@mobile-devices.fr and do not necessarily represent those of 
the
company. Although the company has taken reasonable precautions to ensure no
viruses are present in this email, the company cannot accept responsibility
for any loss or damage arising from the use of this email or attachments.

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] slow query : very simple delete, 100% cpu, nearly no disk activity

2009-09-21 Thread Vincent de Phily

On Friday 11 September 2009 23:55:09 Merlin Moncure wrote:
> On Mon, Sep 7, 2009 at 5:05 AM, Vincent de Phily
>  wrote: 
> >                                     Table "public.message"
> >  Column   |            Type             |                      Modifiers
> > ---+-+---
> >--- id        | integer                     | not null
> > default
> > nextval('message_id_seq'::regclass)
> >  unitid    | integer                     | not null
> >  userid    | integer                     |
> >  refid     | integer                     |
> >
> > Indexes:
> >    "message_pkey" PRIMARY KEY, btree (id)
> >    "message_unitid_fromto_status_idx" btree (unitid, fromto, status)
> >    "message_userid_idx" btree (userid)
> > Foreign-key constraints:
> >    "message_refid_fkey" FOREIGN KEY (refid) REFERENCES message(id) ON
> > UPDATE CASCADE ON DELETE CASCADE
> >    "message_unitid_fkey" FOREIGN KEY (unitid) REFERENCES units(id) ON
> > UPDATE CASCADE ON DELETE CASCADE
> >    "message_userid_fkey" FOREIGN KEY (userid) REFERENCES users(id) ON
> > UPDATE CASCADE ON DELETE CASCADE
>
> where is the index on refid?

It's
"message_pkey" PRIMARY KEY, btree (id)
because
(refid) REFERENCES message(id)

-- 
Vincent de Phily
Mobile Devices
+33 (0) 666 301 306
+33 (0) 142 119 325

Warning
This message (and any associated files) is intended only for the use of its
intended recipient and may contain information that is confidential, subject
to copyright or constitutes a trade secret. If you are not the intended
recipient you are hereby notified that any dissemination, copying or
distribution of this message, or files associated with this message, is
strictly prohibited. If you have received this message in error, please
notify us immediately by replying to the message and deleting it from your
computer. Any views or opinions presented are solely those of the author
vincent.deph...@mobile-devices.fr and do not necessarily represent those of 
the
company. Although the company has taken reasonable precautions to ensure no
viruses are present in this email, the company cannot accept responsibility
for any loss or damage arising from the use of this email or attachments.

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] slow query : very simple delete, 100% cpu, nearly no disk activity

2009-09-21 Thread Vincent de Phily

On Monday 21 September 2009 17:00:36 Merlin Moncure wrote:
> On Mon, Sep 21, 2009 at 10:50 AM, Vincent de Phily
>
>  wrote:
> > On Friday 11 September 2009 23:55:09 Merlin Moncure wrote:
> >> On Mon, Sep 7, 2009 at 5:05 AM, Vincent de Phily
> >>
> >>  wrote:
> >> >                                     Table "public.message"
> >> >  Column   |            Type             |                    
> >> >  Modifiers
> >> > ---+-+
> >> >--- --- id        | integer                     |
> >> > not null default
> >> > nextval('message_id_seq'::regclass)
> >> >  unitid    | integer                     | not null
> >> >  userid    | integer                     |
> >> >  refid     | integer                     |
> >> >
> >> > Indexes:
> >> >    "message_pkey" PRIMARY KEY, btree (id)
> >> >    "message_unitid_fromto_status_idx" btree (unitid, fromto, status)
> >> >    "message_userid_idx" btree (userid)
> >> > Foreign-key constraints:
> >> >    "message_refid_fkey" FOREIGN KEY (refid) REFERENCES message(id) ON
> >> > UPDATE CASCADE ON DELETE CASCADE
> >> >    "message_unitid_fkey" FOREIGN KEY (unitid) REFERENCES units(id) ON
> >> > UPDATE CASCADE ON DELETE CASCADE
> >> >    "message_userid_fkey" FOREIGN KEY (userid) REFERENCES users(id) ON
> >> > UPDATE CASCADE ON DELETE CASCADE
> >>
> >> where is the index on refid?
> >
> > It's
> > "message_pkey" PRIMARY KEY, btree (id)
> > because
> > (refid) REFERENCES message(id)
>
> You are thinking about this backwards.  Every time you delete a
> message, the table has to be scanned for any messages that reference
> the message being deleted because of the refid constraint (in order to
> see if any deletions must be cascaded).   PostgreSQL creates a backing
> index for primary keys automatically but not foreign keys...so you
> likely need to create an index on refid.

D'Oh ! Sounds obvious now that you mention it, and it's a very good 
explanation of the delete's slowness.

I'll test this tonight or tomorrow.

-- 
Vincent de Phily
Mobile Devices
+33 (0) 666 301 306
+33 (0) 142 119 325

Warning
This message (and any associated files) is intended only for the use of its
intended recipient and may contain information that is confidential, subject
to copyright or constitutes a trade secret. If you are not the intended
recipient you are hereby notified that any dissemination, copying or
distribution of this message, or files associated with this message, is
strictly prohibited. If you have received this message in error, please
notify us immediately by replying to the message and deleting it from your
computer. Any views or opinions presented are solely those of the author
vincent.deph...@mobile-devices.fr and do not necessarily represent those of 
the
company. Although the company has taken reasonable precautions to ensure no
viruses are present in this email, the company cannot accept responsibility
for any loss or damage arising from the use of this email or attachments.

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Strange result: UNIX vs. TCP/IP sockets

2003-07-04 Thread Vincent van Leeuwen

http://grotto11.com/blog/slash.html?+1039831658

Summary: IE and IIS cheat at TCP level by leaving out various SYN and ACK
packets, thereby making IE requests from IIS servers blazingly fast, and
making IE requests to non-IIS servers infuriatingly slow.

But since this only relates to making and breaking TCP connections, I don't
think this is relevant for a larger query time. It's probably normal for a TCP
connection to be slightly slower than a unix socket, but I don't think that's
wat Andrew is experiencing.

On 2003-07-04 14:35:18 -0300, The Hermit Hacker wrote:
> 
> 'K, this is based on "old information", I don't know if Sun changed it
> 'yet again' ... but, when I was working at the University, one of our IT
> directors gave me a report that deal with something Sun did (god, I'm so
> detailed  here, eh?) to "mimic" how Microsoft broke the TCP/IP protocol
> ... the report was in relation to Web services, and how the change
> actually made Sun/Solaris appear to be slower then Microsoft ...
> 
> And Sun made this the 'default' setting, but it was disablable in
> /etc/systems ...
> 
> Sorry for being so vague, but if I recall correctly, it had something to
> do with adding an extra ACK to each packet ... maybe even as vague as the
> above is, it will jar a memory for someone else?
> 
> 
> On Fri, 4 Jul 2003, Andrew Sullivan wrote:
> 
> > Hi all,
> >
> > We're run into a rather odd problem here, and we're puzzling out
> > what's going on.  But while we do, I thought I'd see if anyone else
> > has anything similar to report.
> >
> > This is for 7.2.4 on Solaris 8.
> >
> > We have a query for which EXPLAIN ANALYSE on a local psql connection
> > always returns a time of between about 325 msec and 850 msec
> > (depending on other load, whether the result is in cache, &c. -- this
> > is an aggregates query involving min() and count()).
> >
> > If I connect using -h 127.0.0.1, however, I can _sometimes_ get the
> > query to take as long as 1200 msec.  The effect is sporadic (of
> > course.  If it were totally predictable, the computing gods wouldn't
> > be having any fun with me), but it is certainly there off and on.
> > (We discovered it because our application is regularly reporting
> > times on this query roughly twice as long as I was able to get with
> > psql, until I connected via TCP/IP.)
> >
> > I'll have more to report as we investigate further -- at the moment,
> > this has cropped up on a production system, and so we're trying to
> > reproduce it in our test environment.  Naturally, we're looking at
> > the TCP/IP stack configuration, among other stuff.  In the meantime,
> > however, I wondered if anyone knows which bits I ought to be prodding
> > at to look for sub-optimal libraries, &c.; or whether anyone else has
> > run into similar problems on Solaris or elsewhere.
> >
> > A
> >
> > --
> > 
> > Andrew Sullivan 204-4141 Yonge Street
> > Liberty RMS   Toronto, Ontario Canada
> > <[EMAIL PROTECTED]>  M2P 2A8
> >  +1 416 646 3304 x110
> >
> >
> > ---(end of broadcast)---
> > TIP 8: explain analyze is your friend
> >
> 
> Marc G. Fournier   ICQ#7615664   IRC Nick: Scrappy
> Systems Administrator @ hub.org
> primary: [EMAIL PROTECTED]   secondary: [EMAIL PROTECTED]|postgresql}.org
> 
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Vincent van Leeuwen
Media Design - http://www.mediadesign.nl/

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [PERFORM] Hardware performance

2003-07-17 Thread Vincent van Leeuwen

On 2003-07-16 19:57:22 -0700, Balazs Wellisch wrote:
> We're now stuck on the question of what type of RAID configuration to use
> for this server. RAID 5 offers the best fault tolerance but doesn't perform
> all that well. RAID 10 offers much better performance, but no hot swap. Or
> should we not use RAID at all. I know that ideally the log (WAL) files
> should reside on a separate disk from the rest of the DB. Should we use 4
> separate drives instead? One for the OS, one for data, one for WAL, one for
> swap? Or RAID 10 for everything plus 1 drive for WAL? Or RAID 5 for
> everything?
> 

We have recently run our own test (simulating our own database load) on a new
server which contained 7 15K rpm disks. Since we always want to have a
hot-spare drive (servers are located in a hard-to-reach datacenter) and we
always want redundancy, we tested two different configurations:
- 6 disk RAID 10 array, holding everything
- 4 disk RAID 5 array holding postgresql data and 2 disk RAID 1 array holding
  OS, swap and WAL logs

Our database is used for a very busy community website, so our load contains a
lot of inserts/updates for a website, but much more selects than there are
updates.

Our findings were that the 6 disk RAID 10 set was significantly faster than
the other setup.

So I'd recommend a 4-disk RAID 10 array. I'd use the 5th drive for a hot-spare
drive, but that's your own call. However, it would be best if you tested some
different setups under your own database load to see what works best for you.


Vincent van Leeuwen
Media Design - http://www.mediadesign.nl/

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] Sanity check requested

2003-07-18 Thread Vincent van Leeuwen

On 2003-07-17 10:41:35 -0500, Nick Fankhauser wrote:
> I'm using ext2. For now, I'll leave this and the OS version alone. If I
> 

I'd upgrade to a journaling filesystem as soon as possible for reliability.
Testing in our own environment has shown that PostgreSQL performs best on ext3
(yes, better than XFS, JFS or ReiserFS) with a linux 2.4.21 kernel. Be sure to
mount noatime and to create the ext3 partition with the correct stripe size of
your RAID array using the '-R stride=foo' option (see man mke2fs).

Vincent van Leeuwen
Media Design - http://www.mediadesign.nl/

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] Sanity check requested

2003-07-18 Thread Vincent van Leeuwen

On 2003-07-18 18:20:55 +0200, Oliver Scheit wrote:
> > Be sure to mount noatime 
> 
> I did "chattr -R +A /var/lib/pgsql/data"
> that should do the trick as well or am I wrong?
> 

According to the man page it gives the same effect. There are a few things you
should consider though:
- new files won't be created with the same options (I think), so you'll have
to run this command as a daily cronjob or something to that effect
- chattr is probably more filesystem-specific than a noatime mount, although
this isn't a problem on ext[23] ofcourse

Vincent van Leeuwen
Media Design - http://www.mediadesign.nl/

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [PERFORM] Tuning PostgreSQL

2003-07-21 Thread Vincent van Leeuwen

On 2003-07-21 09:06:10 -0700, Josh Berkus wrote:
> Alexander,
> 
> > Hmmm. Seems to me that this setup would be better than one RAID5 with three
> > 36Gb disks, wouldn't you think so? With one RAID5 array, I would still have
> > the data and the WAL on one volume...
> 
> Definitely.   As I've said, my experience with RAID5 is that with less than 5 
> disks, it performs around 40% of a single scsi disk for large read-write 
> operation on Postgres.   
> 
> If you have only 3 disks, I'd advocate one disk for WAL and one RAID 1 array 
> for the database.
> 

In this setup your database is still screwed if a single disk (the WAL disk)
stops working. You'll have to revert to your last backup if this happens. The
RAID-1 redundancy on your data disks buys you almost nothing: marginally
better performance and no real redundancy should a single disk fail.

I'd use RAID-5 if you absolutely cannot use more disks, but I would use
RAID-10 or two RAID-1 partitions if you can afford to use 4 disks.

Vincent van Leeuwen
Media Design - http://www.mediadesign.nl/

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] Tuning PostgreSQL

2003-07-22 Thread Vincent van Leeuwen

On 2003-07-22 09:04:42 +0200, Alexander Priem wrote:
> Hi all,
> 
> Vincent, You said that using RAID1, you don't have real redundancy. But
> RAID1 is mirroring, right? So if one of the two disks should fail, there
> should be no data lost, right?
> 

Right. But the proposal was a single disk for WAL, without redundancy, and I
argued that wasn't really safe. RAID1 by itself is extremely safe, possibly
even the safest RAID type there is.

> I have been thinking some more. 18Gb drives are cheaper than 36 or 72Gb
> drives. I don't know if I can get the money for this, but how would the
> following setup sound?
> 
> Two 18Gb (15.000rpm) disks in RAID1 array for Operating System + WAL.
> Four 18Gb (15.000rpm) disks in RAID5 array for data.
> 

Our own testing has shown that a 6 disk RAID-10 array is faster than what you
describe. Of course, this is very much dependant on how much INSERT/UPDATES
you generate (which taxes your WAL more), so your mileage may vary.

> For the same amount of money, I could also get:
> 
> Two 36Gb (10.000rpm) disks in RAID1 array for Operating System + WAL.
> Five/Six 36Gb (10.000rpm) disks in RAID5 array for data.
> 

It is said that a higher RPM is particularly useful for a WAL disk. So you
might consider using two 18GB 15K rpm drives for a RAID-1 WAL disk (+OS and
swap), and using 36GB 10K rpm disks in a RAID-5 array if you need that
diskspace.

> Which would be the best of the above? The one with four 15k-rpm disks or the
> one with five/six 10k-rpm disks?
> Would these configs be better than all disks in one huge RAID5 array? There
> are so many possible configs with RAID...
> 

15K rpm disks are significantly faster than 10K rpm disks. If your only
concern is performance, buy 15K rpm disks. If you want more diskspace for your
money, fall back to larger 10K rpm disks.

I personally think seperate WAL disks are vastly overrated, since they haven't
shown a big performance gain in our own tests. But as I have said, this is
extremely dependant on the type of load you generate, so only your own tests
can tell you what you should do in this respect.

About RAID types: the fastest RAID type by far is RAID-10. However, this will
cost you a lot of useable diskspace, so it isn't for everyone. You need at
least 4 disks for a RAID-10 array. RAID-5 is a nice compromise if you want as
much useable diskspace as possible and still want to be redundant. RAID-1 is
very useful for small (2-disk) arrays.

If you have the time and are settled on buying 6 disks, I'd test the following
scenarios:
- 6-disk RAID-10 array (should perform best)
- 4-disk RAID-10 array containing data, 2-disk RAID-1 array for WAL, OS, etc
- 4-disk RAID-5 array containing data, 2-disk RAID-1 array for WAL, OS, etc
- 6-disk RAID-5 array (will probably perform worst)

Hope this helps.

Vincent van Leeuwen
Media Design - http://www.mediadesign.nl/

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [PERFORM] [GENERAL] DELETE taking too much memory

On Thursday 07 July 2011 22:26:45 Guillaume Lelarge wrote:
> On Thu, 2011-07-07 at 15:34 +0200, vincent dephily wrote:
> > Hi,
> > 
> > I have a delete query taking 7.2G of ram (and counting) but I do not
> > understant why so much memory is necessary. The server has 12G, and
> > I'm afraid it'll go into swap. Using postgres 8.3.14.
> > 
> > I'm purging some old data from table t1, which should cascade-delete
> > referencing rows in t2. Here's an anonymized rundown :
> > 
> > 
> > # \d t1
> > 
> >  Table
> >  "public.t1"
> >   
> >   Column   |Type | Modifiers
> > 
> > ---+-+--
> > ---
> > 
> >  t1id  | integer | not null default
> > 
> > nextval('t1_t1id_seq'::regclass)
> > (...snip...)
> > 
> > Indexes:
> > "message_pkey" PRIMARY KEY, btree (id)
> > 
> > (...snip...)
> > 
> > # \d t2
> > 
> >Table
> >"public.t
> >2"
> >  
> >  Column  |Type |Modifiers
> > 
> > -+-+
> > -
> > 
> >  t2id| integer | not null default
> > 
> > nextval('t2_t2id_seq'::regclass)
> > 
> >  t1id| integer | not null
> >  foo | integer | not null
> >  bar | timestamp without time zone | not null default now()
> > 
> > Indexes:
> > "t2_pkey" PRIMARY KEY, btree (t2id)
> > "t2_bar_key" btree (bar)
> > "t2_t1id_key" btree (t1id)
> > 
> > Foreign-key constraints:
> > "t2_t1id_fkey" FOREIGN KEY (t1id) REFERENCES t1(t1id) ON UPDATE
> > 
> > RESTRICT ON DELETE CASCADE
> > 
> > # explain delete from t1 where t1id in (select t1id from t2 where
> > foo=0 and bar < '20101101');
> > 
> >QUERY PLAN
> > 
> > 
> > -
> > 
> >  Nested Loop  (cost=5088742.39..6705282.32 rows=30849 width=6)
> >  
> >->  HashAggregate  (cost=5088742.39..5089050.88 rows=30849
> >width=4)
> >
> >  ->  Index Scan using t2_bar_key on t2 
> >  (cost=0.00..5035501.50
> > 
> > rows=21296354 width=4)
> > 
> >Index Cond: (bar < '2010-11-01
> >00:00:00'::timestamp
> > 
> > without time zone)
> > 
> >Filter: (foo = 0)
> >
> >->  Index Scan using t1_pkey on t1  (cost=0.00..52.38 rows=1
> >width=10)
> >
> >  Index Cond: (t1.t1id = t2.t1id)
> > 
> > (7 rows)
> > 
> > 
> > Note that the estimate of 30849 rows is way off : there should be
> > around 55M rows deleted from t1, and 2-3 times as much from t2.
> > 
> > When looking at the plan, I can easily imagine that data gets
> > accumulated below the nestedloop (thus using all that memory), but why
> > isn't each entry freed once one row has been deleted from t1 ? That
> > entry isn't going to be found again in t1 or in t2, so why keep it
> > around ?
> > 
> > Is there a better way to write this query ? Would postgres 8.4/9.0
> > handle things better ?
> 
> Do you have any DELETE triggers in t1 and/or t2?

No, there are triggers on insert/update to t1 which both insert into t2, but 
no delete trigger. Deletions do cascade from t1 to t2 because of the foreign 
key.
-- 
Vincent de Phily
Mobile Devices
+33 (0) 142 119 325
+353 (0) 85 710 6320 

Warning
This message (and any associated files) is intended only for the use of its
intended recipient and may contain information that is confidential, subject
to copyright or constitutes a trade secret. If you are not the intended
recipient you are hereby notified that any dissemination, copying or
distribution of this message, or files associated with this message, is
strictly prohibited. If you have received this message in error, please
notify us immediately by replying to the message and deleting it from your
computer. Any views or opinions presented are solely those of the author
vincent.deph...@mobile-devices.fr and do not necessarily represent those of 
the
company. Although the company has taken reasonable precautions to ensure no
viruses are present in this email, the company cannot accept responsibility
for any loss or damage arising from the use of this email or attachments.

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] DELETE taking too much memory

On Thursday 07 July 2011 19:54:08 French, Martin wrote:
> How up to date are the statistics for the tables in question?
> 
> What value do you have for effective cache size?
> 
> My guess would be that planner thinks the method it is using is right
> either for its current row number estimations, or the amount of memory
> it thinks it has to play with.

Not very up to date I'm afraid (as shown by the low estimate of deleted rows). 
Table t2 has been insert-only since its re-creation (that's another story), 
while t1 is your classic insert-many, update-recent.

We haven't tweaked effective cache size yet, it's on the TODO... like many 
other things :/
-- 
Vincent de Phily

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] [GENERAL] DELETE taking too much memory

On Friday 08 July 2011 10:05:47 Dean Rasheed wrote:
> > On Thu, 2011-07-07 at 15:34 +0200, vincent dephily wrote:
> >> Hi,
> >> 
> >> I have a delete query taking 7.2G of ram (and counting) but I do not
> >> understant why so much memory is necessary. The server has 12G, and
> >> I'm afraid it'll go into swap. Using postgres 8.3.14.
> >> 
> >> I'm purging some old data from table t1, which should cascade-delete
> >> referencing rows in t2. Here's an anonymized rundown :
> >> 
> >> # explain delete from t1 where t1id in (select t1id from t2 where
> >> foo=0 and bar < '20101101');
> 
> It looks as though you're hitting one of the known issues with
> PostgreSQL and FKs. The FK constraint checks and CASCADE actions are
> implemented using AFTER triggers, which are queued up during the query
> to be executed at the end. For very large queries, this queue of
> pending triggers can become very large, using up all available memory.
> 
> There's a TODO item to try to fix this for a future version of
> PostgreSQL (maybe I'll have another go at it for 9.2), but at the
> moment all versions of PostgreSQL suffer from this problem.

That's very interesting, and a more plausible not-optimized-yet item than my 
guesses so far, thanks. Drop me a mail if you work on this, and I'll find some 
time to test your code.

I'm wondering though : this sounds like the behaviour of a "deferrable" fkey, 
which AFAICS is not the default and not my case ? I haven't explored that area 
of constraints yet, so there's certainly some detail that I'm missing.


> The simplest work-around for you might be to break your deletes up
> into smaller chunks, say 100k or 1M rows at a time, eg:
> 
> delete from t1 where t1id in (select t1id from t2 where foo=0 and bar
> < '20101101' limit 10);

Yes, that's what we ended up doing. We canceled the query after 24h, shortly 
before the OOM killer would have, and started doing things in smaller batches.


-- 
Vincent de Phily

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] DELETE taking too much memory