from:"Vitalii Tymchyshyn"

There is switch-like sql case:
39.6.2.4. Simple CASE

CASE search-expression
WHEN expression [, expression [ ... ]] THEN
  statements
  [ WHEN expression [, expression [ ... ]] THEN
  statements
... ]
  [ ELSE
  statements ]
END CASE;

It should work like C switch statement.

Also, for bulk insert, have you tried for each statement triggers instead
of for each row?
This would look like a lot of inserts and would not be fast in
single-row-insert case, but can give you benefit for huge inserts.
It should look like
insert into quotes_2012_09_10 select * from new where
cast(new.received_time as date) = '2012-09-10' ;
insert into quotes_2012_09_11 select * from new where
cast(new.received_time as date) = '2012-09-11' ;
...

2012/12/27 Stephen Frost sfr...@snowman.net

 * Jeff Janes (jeff.ja...@gmail.com) wrote:
  If the main goal is to make it faster, I'd rather see all of plpgsql get
  faster, rather than just a special case of partitioning triggers.  For
  example, right now a CASE expression statement with 100 branches is
 about
  the same speed as an equivalent list of 100 elsif.  So it seems to be
 doing
  a linear search, when it could be doing a hash that should be a lot
 faster.

 That's a nice thought, but I'm not sure that it'd really be practical.
 CASE statements in plpgsql are completely general and really behave more
 like an if/elsif tree than a C-style switch() statement or similar.  For
 one thing, the expression need not use the same variables, could be
 complex multi-variable conditionals, etc.

 Figuring out that you could build a dispatch table for a given CASE
 statement and then building it, storing it, and remembering to use it,
 wouldn't be cheap.

 On the other hand, I've actually *wanted* a simpler syntax on occation.
 I have no idea if there'd be a way to make it work, but this would be
 kind of nice:

 CASE OF x -- or whatever
   WHEN 1 THEN blah blah
   WHEN 2 THEN blah blah
   WHEN 3 THEN blah blah
 END

 which would be possible to build into a dispatch table by looking at the
 type of x and the literals used in the overall CASE statement.  Even so,
 there would likely be some number of WHEN conditions required before
 it'd actually be more efficient to use, though perhaps getting rid of
 the expression evaluation (if that'd be possible) would make up for it.

 Thanks,

 Stephen




-- 
Best regards,
 Vitalii Tymchyshyn

Re: [PERFORM] Performance on Bulk Insert to Partitioned Table

BTW: If select count(*) from new is fast, you can even choose the
strategy in trigger depending on insert size.


2012/12/28 Vitalii Tymchyshyn tiv...@gmail.com

 There is switch-like sql case:
 39.6.2.4. Simple CASE

 CASE search-expression
 WHEN expression [, expression [ ... ]] THEN
   statements
   [ WHEN expression [, expression [ ... ]] THEN
   statements
 ... ]
   [ ELSE
   statements ]
 END CASE;

 It should work like C switch statement.

 Also, for bulk insert, have you tried for each statement triggers
 instead of for each row?
 This would look like a lot of inserts and would not be fast in
 single-row-insert case, but can give you benefit for huge inserts.
 It should look like
 insert into quotes_2012_09_10 select * from new where
 cast(new.received_time as date) = '2012-09-10' ;
 insert into quotes_2012_09_11 select * from new where
 cast(new.received_time as date) = '2012-09-11' ;
 ...

 2012/12/27 Stephen Frost sfr...@snowman.net

 * Jeff Janes (jeff.ja...@gmail.com) wrote:
  If the main goal is to make it faster, I'd rather see all of plpgsql get
  faster, rather than just a special case of partitioning triggers.  For
  example, right now a CASE expression statement with 100 branches is
 about
  the same speed as an equivalent list of 100 elsif.  So it seems to be
 doing
  a linear search, when it could be doing a hash that should be a lot
 faster.

 That's a nice thought, but I'm not sure that it'd really be practical.
 CASE statements in plpgsql are completely general and really behave more
 like an if/elsif tree than a C-style switch() statement or similar.  For
 one thing, the expression need not use the same variables, could be
 complex multi-variable conditionals, etc.

 Figuring out that you could build a dispatch table for a given CASE
 statement and then building it, storing it, and remembering to use it,
 wouldn't be cheap.

 On the other hand, I've actually *wanted* a simpler syntax on occation.
 I have no idea if there'd be a way to make it work, but this would be
 kind of nice:

 CASE OF x -- or whatever
   WHEN 1 THEN blah blah
   WHEN 2 THEN blah blah
   WHEN 3 THEN blah blah
 END

 which would be possible to build into a dispatch table by looking at the
 type of x and the literals used in the overall CASE statement.  Even so,
 there would likely be some number of WHEN conditions required before
 it'd actually be more efficient to use, though perhaps getting rid of
 the expression evaluation (if that'd be possible) would make up for it.

 Thanks,

 Stephen




 --
 Best regards,
  Vitalii Tymchyshyn




-- 
Best regards,
 Vitalii Tymchyshyn

Re: [PERFORM] Performance on Bulk Insert to Partitioned Table

Why so? Basic form case lvalue when rvalue then out ... end is much like
switch.
The case when condition then out ... end is different, more complex
beast, but first one is essentially a switch. If it is now trnasformed into
case when lvalue = rvalue1 then out1 when lvalue=rvalue2 then out2 ...
end then this can be optimized and this would benefit many users, not only
ones that use partitioning.


2012/12/28 Stephen Frost sfr...@snowman.net

 Vitalii,

 * Vitalii Tymchyshyn (tiv...@gmail.com) wrote:
  There is switch-like sql case:
 [...]
  It should work like C switch statement.

 It does and it doesn't.  It behaves generally like a C switch statement,
 but is much more flexible and therefore can't be optimized like a C
 switch statement can be.

 Thanks,

 Stephen




-- 
Best regards,
 Vitalii Tymchyshyn

Re: [PERFORM] Performance on Bulk Insert to Partitioned Table

It's a pity. Why does not it listed in Compatibility section of create
trigger documentation? I think, this makes for each statement triggers
not compatible with SQL99.


2012/12/28 Pavel Stehule pavel.steh...@gmail.com

 Hello

 
  Also, for bulk insert, have you tried for each statement triggers
 instead
  of for each row?
  This would look like a lot of inserts and would not be fast in
  single-row-insert case, but can give you benefit for huge inserts.
  It should look like
  insert into quotes_2012_09_10 select * from new where
 cast(new.received_time
  as date) = '2012-09-10' ;
  insert into quotes_2012_09_11 select * from new where
 cast(new.received_time
  as date) = '2012-09-11' ;
  ...

 It has only one problem - PostgreSQL has not relations NEW and OLD for
 statements triggers.

 Regards

 Pavel




-- 
Best regards,
 Vitalii Tymchyshyn

Re: [PERFORM] Slow query: bitmap scan troubles

2012-12-04 Thread Vitalii Tymchyshyn

Well, you don't need to put anything down. Most settings that change
planner decisions can be tuned on per-quey basis by issuing set commands in
given session. This should not affect other queries more than it is needed
to run query in the way planner chooses.

Best regards, Vitalii Tymchyshyn


2012/12/4 postgre...@foo.me.uk


  But the row estimates are not precise at the top of the join/filter.
  It thinks there will 2120 rows, but there are only 11.

 Ah... I didn't spot that one...

 Yes, you are right there - this is probably a slightly atypical query of
 this sort actually, 2012 is a pretty good guess.

 On Claudio's suggestion I have found lots more things to read up on and am
 eagerly awaiting 6pm when I can bring the DB down and start tweaking. The
 effective_work_mem setting is going from 6Gb-88Gb which I think will make
 quite a difference.

 I still can't quite wrap around my head why accessing an index is expected
 to use more disk access than doing a bitmap scan of the table itself, but I
 guess it does make a bit of sense if postgres assumes the table is more
 likely to be cached.

 It's all quite, quite fascinating :)

 I'll let you know how it goes.

 - Phil



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




-- 
Best regards,
 Vitalii Tymchyshyn

Re: [PERFORM] Optimize update query

2012-12-02 Thread Vitalii Tymchyshyn

Well, it seems that my data can be outdated, sorry for that. I've just
checked performance numbers on Tom's hardware and it seems that best sad
really do 500 MB/s. Some others do 100. So, I'd say one must choose wisely
(as always :-) ).

Best regards,
Vitalii Tymchyshyn
1 груд. 2012 00:43, Mark Kirkwood mark.kirkw...@catalyst.net.nz напис.

 Hmm - not strictly true as stated: 1 SSD will typically do 500MB/s
 sequential read/write. 1 HDD will be lucky to get a 1/3 that.

 We are looking at replacing 4 to 6 disk RAID10 arrays of HDD with a RAID1
 pair of SSD, as they perform about the same for sequential work and vastly
 better at random. Plus they only use 2x 2.5 slots (or, ahem 2x PCIe
 sockets), so allow smaller form factor servers and save on power and
 cooling.

 Cheers

 Mark

 On 30/11/12 23:07, Vitalii Tymchyshyn wrote:

 Oh, yes. I don't imagine DB server without RAID+BBU :)
 When there is no BBU, SSD can be handy.
 But you know, SSD is worse in linear read/write than HDD.

 Best regards, Vitalii Tymchyshyn


 2012/11/30 Mark Kirkwood mark.kirkw...@catalyst.net.nz
 mailto:mark.kirkwood@**catalyst.net.nz mark.kirkw...@catalyst.net.nz

 Most modern SSD are much faster for fsync type operations than a
 spinning disk - similar performance to spinning disk + writeback
 raid controller + battery.

 However as you mention, they are great at random IO too, so Niels,
 it might be worth putting your postgres logs *and* data on the SSDs
 and retesting.

Re: [PERFORM] Optimize update query

2012-11-30 Thread Vitalii Tymchyshyn

Actually, what's the point in putting logs to ssd? SSDs are good for random
access and logs are accessed sequentially. I'd put table spaces on ssd and
leave logs on hdd
30 лист. 2012 04:33, Niels Kristian Schjødt nielskrist...@autouncle.com
напис.

 Hmm I'm getting suspicious here. Maybe my new great setup with the SSD's
 is not really working as it should., and maybe new relic is not monitoring
 as It should.

 If I do a sudo iostat -k 1
 I get a lot of output like this:
 Device:tpskB_read/skB_wrtn/skB_readkB_wrtn
 sda   0.00 0.00 0.00  0  0
 sdb   0.00 0.00 0.00  0  0
 sdc 546.00  2296.00  6808.00   2296   6808
 sdd 593.00  1040.00  7416.00   1040   7416
 md1   0.00 0.00 0.00  0  0
 md0   0.00 0.00 0.00  0  0
 md21398.00  3328.00 13064.00   3328  13064
 md3   0.00 0.00 0.00  0  0

 The storage thing is, that the sda and sdb is the SSD drives and the sdc
 and sdd is the HDD drives. The md0, md1 and md2 is the raid arrays on the
 HDD's and the md3 is the raid on the SSD's. Neither of the md3 or the SSD's
 are getting utilized - and I should expect that since they are serving my
 pg_xlog right? - so maybe I did something wrong in the setup. Here is the
 path I followed:

 # 1) First setup the SSD drives in a software RAID1 setup:
 #
 http://askubuntu.com/questions/223194/setup-of-two-additional-ssd-drives-in-raid-1
 #
 # 2) Then move the postgres pg_xlog dir
 #   sudo /etc/init.d/postgresql-9.2 stop
 #   sudo mkdir -p /ssd/pg_xlog
 #   sudo chown -R  postgres.postgres /ssd/pg_xlog
 #   sudo chmod 700 /ssd/pg_xlog
 #   sudo cp -rf /var/lib/postgresql/9.2/main/pg_xlog/* /ssd/pg_xlog
 #   sudo mv /var/lib/postgresql/9.2/main/pg_xlog
 /var/lib/postgresql/9.2/main/pg_xlog_old
 #   sudo ln -s /ssd/pg_xlog /var/lib/postgresql/9.2/main/pg_xlog
 #   sudo /etc/init.d/postgresql-9.2 start

 Can you spot something wrong?



 Den 30/11/2012 kl. 02.43 skrev Niels Kristian Schjødt 
 nielskrist...@autouncle.com:

  Den 30/11/2012 kl. 02.24 skrev Kevin Grittner kgri...@mail.com:
 
  Niels Kristian Schjødt wrote:
 
  Okay, now I'm done the updating as described above. I did the
  postgres.conf changes. I did the kernel changes, i added two
  SSD's in a software RAID1 where the pg_xlog is now located -
  unfortunately the the picture is still the same :-(
 
  You said before that you were seeing high disk wait numbers. Now it
  is zero accourding to your disk utilization graph. That sounds like
  a change to me.
 
  When the database is under heavy load, there is almost no
  improvement to see in the performance compared to before the
  changes.
 
  In client-visible response time and throughput, I assume, not
  resource usage numbers?
 
  A lot of both read and writes takes more than a 1000 times as
  long as they usually do, under lighter overall load.
 
  As an odd coincidence, you showed your max_connections setting to
  be 1000.
 
  http://wiki.postgresql.org/wiki/Number_Of_Database_Connections
 
  -Kevin
 
  Hehe, I'm sorry if it somehow was misleading, I just wrote a lot of
 I/O it was CPU I/O, it also states that in the chart in the link.
  However, as I'm not very familiar with these deep down database and
 server things, I had no idea wether a disk bottle neck could hide in this
 I/O, so i went along with Shauns great help, that unfortunately didn't
 solve my issues.
  Back to the issue: Could it be that it is the fact that I'm using
 ubuntus built in software raid to raid my disks, and that it is not at all
 capable of handling the throughput?
 



 --
 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] Optimize update query

2012-11-30 Thread Vitalii Tymchyshyn

Oh, yes. I don't imagine DB server without RAID+BBU :)
When there is no BBU, SSD can be handy.
But you know, SSD is worse in linear read/write than HDD.

Best regards, Vitalii Tymchyshyn

2012/11/30 Mark Kirkwood mark.kirkw...@catalyst.net.nz

Most modern SSD are much faster for fsync type operations than a spinning
disk - similar performance to spinning disk + writeback raid controller +
battery.

However as you mention, they are great at random IO too, so Niels, it
might be worth putting your postgres logs *and* data on the SSDs and
retesting.

Regards

Mark

On 30/11/12 21:37, Vitalii Tymchyshyn wrote:

Actually, what's the point in putting logs to ssd? SSDs are good for
random access and logs are accessed sequentially. I'd put table spaces
on ssd and leave logs on hdd

30 лист. 2012 04:33, Niels Kristian Schjødt
nielskrist...@autouncle.com
mailto:nielskristian@**autouncle.comnielskrist...@autouncle.com
напис.

Hmm I'm getting suspicious here. Maybe my new great setup with the
SSD's is not really working as it should., and maybe new relic is
not monitoring as It should.

If I do a sudo iostat -k 1
I get a lot of output like this:
Device:tpskB_read/skB_wrtn/skB_readkB_wrtn
sda 0.00 0.00 0.00 0 0
sdb 0.00 0.00 0.00 0 0
sdc 546.00 2296.00 6808.00 2296 6808
sdd 593.00 1040.00 7416.00 1040 7416
md1 0.00 0.00 0.00 0 0
md0 0.00 0.00 0.00 0 0
md21398.00 3328.00 13064.00 3328 13064
md3 0.00 0.00 0.00 0 0

The storage thing is, that the sda and sdb is the SSD drives and the
sdc and sdd is the HDD drives. The md0, md1 and md2 is the raid
arrays on the HDD's and the md3 is the raid on the SSD's. Neither of
the md3 or the SSD's are getting utilized - and I should expect that
since they are serving my pg_xlog right? - so maybe I did something
wrong in the setup. Here is the path I followed:

# 1) First setup the SSD drives in a software RAID1 setup:
#
http://askubuntu.com/**questions/223194/setup-of-two-**
additional-ssd-drives-in-raid-**1http://askubuntu.com/questions/223194/setup-of-two-additional-ssd-drives-in-raid-1
#
# 2) Then move the postgres pg_xlog dir
# sudo /etc/init.d/postgresql-9.2 stop
# sudo mkdir -p /ssd/pg_xlog
# sudo chown -R postgres.postgres /ssd/pg_xlog
# sudo chmod 700 /ssd/pg_xlog
# sudo cp -rf /var/lib/postgresql/9.2/main/**pg_xlog/* /ssd/pg_xlog
# sudo mv /var/lib/postgresql/9.2/main/**pg_xlog
/var/lib/postgresql/9.2/main/**pg_xlog_old
# sudo ln -s /ssd/pg_xlog /var/lib/postgresql/9.2/main/**pg_xlog
# sudo /etc/init.d/postgresql-9.2 start

Can you spot something wrong?

Den 30/11/2012 kl. 02.43 skrev Niels Kristian Schjødt
nielskrist...@autouncle.com
mailto:nielskristian@**autouncle.comnielskrist...@autouncle.com
:

Den 30/11/2012 kl. 02.24 skrev Kevin Grittner kgri...@mail.com
mailto:kgri...@mail.com:

Niels Kristian Schjødt wrote:

Okay, now I'm done the updating as described above. I did the
postgres.conf changes. I did the kernel changes, i added two
SSD's in a software RAID1 where the pg_xlog is now located -
unfortunately the the picture is still the same :-(

You said before that you were seeing high disk wait numbers. Now
it
is zero accourding to your disk utilization graph. That sounds
like
a change to me.

When the database is under heavy load, there is almost no
improvement to see in the performance compared to before the
changes.

In client-visible response time and throughput, I assume, not
resource usage numbers?

A lot of both read and writes takes more than a 1000 times as
long as they usually do, under lighter overall load.

As an odd coincidence, you showed your max_connections setting to
be 1000.

http://wiki.postgresql.org/**wiki/Number_Of_Database_**
Connectionshttp://wiki.postgresql.org/wiki/Number_Of_Database_Connections

-Kevin

Hehe, I'm sorry if it somehow was misleading, I just wrote a lot
of I/O it was CPU I/O, it also states that in the chart in the link.
However, as I'm not very familiar with these deep down database
and server things, I had no idea wether a disk bottle neck could
hide in this I/O, so i went along with Shauns great help, that
unfortunately didn't solve my issues.
Back to the issue: Could it be that it is the fact

Re: [PERFORM] Optimize update query

2012-11-30 Thread Vitalii Tymchyshyn

SSDs are not faster for sequential IO as I know. That's why (with BBU or
synchronious_commit=off) I prefer to have logs on regular HDDs.

Best reag


2012/11/30 Willem Leenen willem_lee...@hotmail.com


  Actually, what's the point in putting logs to ssd? SSDs are good for
 random access and logs are accessed sequentially. I'd put table spaces on
 ssd and leave logs on hdd
  30 лист. 2012 04:33, Niels Kristian Schjødt 
 nielskrist...@autouncle.com напис.
 Because SSD's are considered faster. Then you have to put the most
 phyisical IO intensive operations on SSD. For the majority of databases,
 these are the logfiles. But you should investigate where the optimum is for
 your situation.





-- 
Best regards,
 Vitalii Tymchyshyn

Re: [PERFORM] Database design - best practice

2012-11-28 Thread Vitalii Tymchyshyn

Let me be devil advocate here :)
First of all, even if you read any basics about normalization, don't take
it to your heart :) Think.
Know that each normalization/denormalization step has it's cons and pros.
E.g. in NoSQL world they often don't normalize much.
What's interesting with PosgreSQL is that it is suited quite good for
NoSQL-like scenarios.
First of all, each unfilled (null) data column takes 1 bit only. This, BTW,
leads to interesting consequence that performance-wise it can be better to
have null/true boolean than false/true. Especially if you've got a lot of
false.
So, PostgreSQL should be good with 10th, possible 100th of data column with
most columns empty. Record of 151 null columns would take header +
roundup(151/8 ) = 19 bytes. Not much. NoSQLs usually put column names into
records and this costs more.
Any null columns at the end of record take no space at all (so, you can
think on reordering your columns to put the least used to the record end).
Adding column with null as default is cheap operation that do not require
table scan.
You can have partial indexes to speed things up, like create index on car
(car_id) where (has_automatic_transmission);

At the other side, when you normalize you need to join. Instead of select *
from car where has_automatic_transmission (that will use index above), you
will have to select * from car where id in (select id from
car_with_automatic_transmission). The plan is much more complex here. It
will be slower.

The main normalization plus for you is that you work with record as a
whole, so if there is a lot of information in there that is rarely used,
you will pay for it's access every time, both on selects and updates.

So, as conclusion, I agree with others, that you should check. But
remember, joining two tables with millions of records os never cheap :)

Best regards, Vitalii Tymchyshyn


2012/11/28 Niels Kristian Schjødt nielskrist...@autouncle.com

 Hi,

 I'm on the hunt for some solid knowledge on a theoretical level about the
 performance of postgresql. My question is regarding best practices, and how
 architectural decisions might influence the performance. First a little
 background:

 The setup:
 I have a database which holds informations on used cars. The database has
 mainly 3 tables of interest for this case:
 A cars table, an adverts table and a sellers table. One car has many
 adverts and one seller has many adverts. One advert belongs to one car and
 one seller.
 The database is powering a website for searching used cars. When searching
 for used cars, the cars table is mainly used, and a lot of the columns
 should be directly available for searching e.g. color, milage, price,
 has_automatic_transmission etc.

 So my main concern is actually about the cars table, since this one
 currently has a lot of columns (151 - I expect thats quite a lot?), and a
 lot of data (4 mil. rows, and growing). Now you might start by thinking,
 this could sound like a regular need for some normalization, but wait a
 second and let me explain :-)
 The columns in this table is for the most very short stings, integers,
 decimals or booleans. So take for an example has_automatic_transmission
 (boolean) I can't see why it would make sense to put that into a separate
 table and join in the values. Or the milage or the price as another
 example. The cars table used for search is indexed quite a lot.

 The questions:
 Having the above setup in mind, what impact on performance, in terms of
 read performance and write performance, does it have, whether I do the
 following:
 1) In general would the read and/or the write on the database be
 faster, if I serialized some of the not searched columns in the table into
 a single text columns instead of let's say 20 booleans?
 2) Lets say I'm updating a timestamp in a single one of the 151
 columns in the cars table. The update statement is using the id to find the
 car. Would the write performance of that UPDATE be affected, if the table
 had fewer columns?
 3) When adding a new column to the table i know that it becomes
 slower the more rows is in the table, but what about the width of the
 table does that affect the performance when adding new columns?
 4) In general what performance downsides do you get when adding a
 lot of columns to one table instead of having them in separate tables?
 5) Is it significantly faster to select * from a table with 20
 columns, than selecting the same 20 in a table with 150 columns?

 Hope there is some good answers out there :-)

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




-- 
Best regards,
 Vitalii Tymchyshyn

Re: SOLVED - RE: [PERFORM] Poor performance using CTE

2012-11-22 Thread Vitalii Tymchyshyn

I'd also add ANALYZED/NOT ANALYZED. This should force it behave like
'create table, analyze, select' with statistics used in second query plan.

P.S. defaults can be configurable.
20 лист. 2012 02:22, Gavin Flower gavinflo...@archidevsys.co.nz напис.

 On 15/11/12 15:03, Peter Geoghegan wrote:

 On 15 November 2012 01:46, Andrew Dunstan and...@dunslane.net wrote:

 It cuts both ways. I have used CTEs a LOT precisely because this
 behaviour
 lets me get better plans. Without that I'll be back to using the offset
 0
 hack.

 Is the OFFSET 0 hack really so bad? We've been telling people to do
 that for years, so it's already something that we've effectively
 committed to.

  How about adding the keywords FENCED and NOT FENCED to the SQL
 definition of CTE's - with FENCED being the default?


 Cheers,
 Gavin



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

Re: [PERFORM] Postgres delete performance problem

2012-06-25 Thread Vitalii Tymchyshyn


Hello.

This may be wrong type for parameter, like using setObject(param, value) 
instead of setObject(param, value, type). Especially if value passed is 
string object. AFAIR index may be skipped in this case. You can check by 
changing statement to delete from xxx where xxx_pk=?::bigint. If it 
works, check how parameter is set in java code.


25.06.12 18:42, Frits Jalvingh написав(ла):

Hi,

I have a Java application that tries to synchronize tables in two 
databases (remote source to local target). It does so by removing all 
constraints, then it compares table contents row by row, inserts 
missing rows and deletes extra rows in the target database. Delete 
performance is incredibly bad: it handles 100 record deletes in about 
16 to 20 seconds(!). Insert and update performance is fine.


The Java statement to handle the delete uses a prepared statement:

delete from xxx where xxx_pk=?

The delete statement is then executed using addBatch() and 
executeBatch() (the latter every 100 deletes), and committed. Not 
using executeBatch makes no difference.


An example table where deletes are slow:

pzlnew=# \d cfs_file
Table public.cfs_file
Column | Type | Modifiers
--+-+---
cfsid | bigint | not null
cfs_date_created | timestamp without time zone | not null
cfs_name | character varying(512) | not null
cfs_cfaid | bigint |
cfs_cfdid | bigint |
Indexes:
cfs_file_pkey PRIMARY KEY, btree (cfsid)

with no FK constraints at all, and a table size of 940204 rows.

While deleting, postgres takes 100% CPU all of the time.


Inserts and updates are handled in exactly the same way, and these are 
a few orders of magnitude faster than the deletes.


I am running the DB on an Ubuntu 12.04 - 64bits machine with Postgres 
9.1, the machine is a fast machine with the database on ssd, ext4, 
with 16GB of RAM and a i7-3770 CPU @ 3.40GHz.


Anyone has any idea?

Thanks in advance,

Frits




--
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] pg 9.1 brings host machine down

2012-06-06 Thread Vitalii Tymchyshyn


Hello.

Seen this already.
It looks like cross join + sort. Badly configured ORM tools like 
Hibernate with multiple one-to-many relationships fetched with 'join' 
strategy may produce such result.
Unfortunately I don't know if it's possible to protect from such a case 
at server side.


Best regards, Vitalii Tymchyshyn

06.06.12 15:05, Konstantin Mikhailov написав(ла):

I'm faced with a problem running postgres 9.1.3 which seems to
nobody else see before. Tried to search and only one relevant
post fond (about millions of files in pgsql_tmp).

Sympthoms:

Some postgres process size is getting abnormally big compared
to other postgres processes. Top shows the 'normal' pg processed
is about VIRT 120m, RES ~30m and SHR ~30m. That one
is about 6500m, 3.4g, 30m corresp. Total RAM avail - 8g.
When one more such a process appears the host going into
deep swap and pg restart can help only (actually the stop
won't even stop such a process - after shutdown it still alive
and can be only killed).

base/pgsql_tmp contains millions of files. In this situation stop
and dirty restart is possible - the normal startup is impossible
either. Read somewhere that it tries to delete (a millions
files) from that directory. I can't even imagine when it finish
the deletion so i'm simple move that folder outside the base
- then start can succeed.

on ubuntu 11.10,12.04 x64. cpu intel core Q9650 3GHz.
8G RAM.

Does anybody see that behaviour or maybe have some glue how to
handle it.

PS: the my preliminary conclusion: some sql is produces
a lot of files in the temporary table spaces - very quickly.
When sql is finished postgres tries to cleanup the folder
reading all contents of the folder and removing the files
one by one. It does the removal slow (watched the folder
by `find pgsql_tmp | wc -l') but process still consumes the
RAM. Next such sql will be a killer :(





--
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] Trouble with plan statistics for behaviour for query.

2012-06-01 Thread Vitalii Tymchyshyn

If I am correct, JDBC uses named portal only on the 5th time you use 
PreparedStatement (configurable). Before it uses unnamed thing that 
should work as if you did embed the value. So the solution is to 
recreate PreparedStatement each time (so you will have no problems with 
SQL injection). Note that smart pools may detect this situation and 
reuse PreparedStatement for same query texts internally. If so, this to 
switch this off.
In case you still have problems, I'd recommend you to ask in postgresql 
jdbc mailing list.
Also I've heard that somewhere in 9.2 postgresql server may replan such 
cases each time.


Best regards, Vitalii Tymchyshyn

01.06.12 02:34, Trevor Campbell написав(ла):

Thanks Craig, that certainly leads down the right path.

The following is all done in pgAdmin3:

Using an actual value we I get the plan I expect
explain analyze select CG.ID, CG.ISSUEID, CG.AUTHOR, CG.CREATED, 
CI.ID, CI.FIELDTYPE, CI.FIELD, CI.OLDVALUE, CI.OLDSTRING, CI.NEWVALUE, 
CI.NEWSTRING
from PUBLIC.CHANGEGROUP CG inner join PUBLIC.CHANGEITEM CI on CG.ID = 
CI.GROUPID where CG.ISSUEID=10006 order by CG.CREATED asc, CI.ID asc


Sort (cost=106.18..106.22 rows=13 width=434) (actual 
time=0.115..0.115 rows=12 loops=1)

 Sort Key: cg.created, ci.id
 Sort Method: quicksort Memory: 29kB
 - Nested Loop (cost=0.00..105.94 rows=13 width=434) (actual 
time=0.019..0.067 rows=12 loops=1)
 - Index Scan using chggroup_issue on changegroup cg 
(cost=0.00..19.73 rows=10 width=29) (actual time=0.009..0.013 rows=10 
loops=1)

 Index Cond: (issueid = 10006::numeric)
 - Index Scan using chgitem_chggrp on changeitem ci (cost=0.00..8.58 
rows=3 width=411) (actual time=0.004..0.005 rows=1 loops=10)

 Index Cond: (groupid = cg.id)
Total runtime: 0.153 ms

Using a prepared statement with a variable , I get a poor plan 
requiring a sequential scan

prepare t2(real) as
select CG.ID, CG.ISSUEID, CG.AUTHOR, CG.CREATED, CI.ID, CI.FIELDTYPE, 
CI.FIELD, CI.OLDVALUE, CI.OLDSTRING, CI.NEWVALUE, CI.NEWSTRING
from PUBLIC.CHANGEGROUP CG inner join PUBLIC.CHANGEITEM CI on CG.ID = 
CI.GROUPID where CG.ISSUEID=$1 order by CG.CREATED asc, CI.ID asc;


explain analyze execute t2 (10006);

Sort (cost=126448.89..126481.10 rows=12886 width=434) (actual 
time=1335.615..1335.616 rows=12 loops=1)

 Sort Key: cg.created, ci.id
 Sort Method: quicksort Memory: 29kB
 - Nested Loop (cost=0.00..125569.19 rows=12886 width=434) (actual 
time=0.046..1335.556 rows=12 loops=1)
 - Seq Scan on changegroup cg (cost=0.00..44709.26 rows=10001 
width=29) (actual time=0.026..1335.460 rows=10 loops=1)

 Filter: ((issueid)::double precision = $1)
 - Index Scan using chgitem_chggrp on changeitem ci (cost=0.00..8.05 
rows=3 width=411) (actual time=0.007..0.008 rows=1 loops=10)

 Index Cond: (groupid = cg.id)
Total runtime: 1335.669 ms

Using a prepared statement with a cast of the variable to the right 
type, I get the good plan back

prepare t2(real) as
select CG.ID, CG.ISSUEID, CG.AUTHOR, CG.CREATED, CI.ID, CI.FIELDTYPE, 
CI.FIELD, CI.OLDVALUE, CI.OLDSTRING, CI.NEWVALUE, CI.NEWSTRING
from PUBLIC.CHANGEGROUP CG inner join PUBLIC.CHANGEITEM CI on CG.ID = 
CI.GROUPID where CG.ISSUEID=cast($1 as numeric) order by CG.CREATED 
asc, CI.ID asc;


explain analyze execute t2 (10006);

Sort (cost=106.19..106.22 rows=13 width=434) (actual 
time=0.155..0.156 rows=12 loops=1)

 Sort Key: cg.created, ci.id
 Sort Method: quicksort Memory: 29kB
 - Nested Loop (cost=0.00..105.95 rows=13 width=434) (actual 
time=0.048..0.111 rows=12 loops=1)
 - Index Scan using chggroup_issue on changegroup cg 
(cost=0.00..19.73 rows=10 width=29) (actual time=0.031..0.042 rows=10 
loops=1)

 Index Cond: (issueid = ($1)::numeric)
 - Index Scan using chgitem_chggrp on changeitem ci (cost=0.00..8.58 
rows=3 width=411) (actual time=0.006..0.006 rows=1 loops=10)

 Index Cond: (groupid = cg.id)
Total runtime: 0.203 ms

Now the challenge is to get java/jdbc to get this done right. We make 
a big effort to ensure we always use prepared statements and variable 
bindings to help protect from SQL injection vulnerabilities.




On 01/06/12 09:08, Craig James wrote:

I use Perl, not JDBC, but this thread may be relevant to your problem.

http://postgresql.1045698.n5.nabble.com/Slow-statement-when-using-JDBC-td3368379.html 









--
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] Write workload is causing severe slowdown in Production

2012-03-22 Thread Vitalii Tymchyshyn


Check for next messages in your log:
LOG: checkpoints are occurring too frequently (ZZZ seconds apart)
HINT: Consider increasing the configuration parameter checkpoint_segments.

Best regards, Vitalii Tymchyshyn

22.03.12 09:27, Gnanakumar написав(ла):

Hi,

We're running a web-based application powered by PostgreSQL.  Recently,
we've developed a new separate Java-based standalone (daemon process)
threaded program that performs both read and write operations heavily on 2
huge tables.  One table has got 5.4 million records and other has 1.3
million records.  Moreover, more than one read and/or write operations may
be executing concurrently.

The issue that we're facing currently in our Production server is, whenever
this newly developed Java program is started/run, then immediately the
entire web application becomes very slow in response.  At this time, I could
also see from the output of  iostat -tx that %util is even crossing more
than 80%.  So, what I could infer here based on my knowledge is, this is
creating heavy IO traffic because of write operation.  Since it was entirely
slowing down web application, we've temporarily stopped running this
standalone application.

Meantime, I also read about checkpoint spikes could be a reason for slow
down in write workload database.  I'm also reading that starting in
PostgreSQL 8.3, we can get verbose logging of the checkpoint process by
turning on log_checkpoints.

My question is, how do I determine whether checkpoint occurrences are the
root cause of this slowdown in my case?  We're running PostgreSQL v8.2.22 on
CentOS5.2 having 35 GB RAM.  log_checkpoints is not available in
PostgreSQL v8.2.22.




--
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] PostgreSQL Parallel Processing !

2012-01-27 Thread Vitalii Tymchyshyn


27.01.12 11:06, Marti Raudsepp написав(ла):

On Fri, Jan 27, 2012 at 06:31, sridhar bamandlapally
sridhar@gmail.com  wrote:

--
| Id  | Operation | Name | Rows  | Bytes | Cost (%CPU)| Time |
--
|   0 | SELECT STATEMENT  |  |  7444K|   944M| 16077   (4)| 00:03:13 |
|   1 |  TABLE ACCESS FULL| EMP  |  7444K|   944M| 16077   (4)| 00:03:13 |
--

Sorry to take this off topic, but... Seriously, over 3 minutes to read
944 MB of data? That's less than 5 MB/s, what's wrong with your
database? :)
Actually I'd ask how parallel CPU may help table sequence scan? Usually 
sequence scan does not take large amount of cpu time, so I see no point 
in parallelism.


--
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] array_except -- Find elements that are not common to both arrays

2011-09-30 Thread Vitalii Tymchyshyn

Since you are using except and not except all, you are not looking at 
arrays with duplicates.

For this case next function what the fastest for me:

create or replace function array_except2(anyarray,anyarray) returns
anyarray as $$
select ARRAY(
(
select r.elements
from(
(select 1,unnest($1))
union all
(select 2,unnest($2))
) as r (arr, elements)
group by 1
having min(arr)=max(arr)
))
$$ language sql strict immutable;

Best regards, Vitalii Tymchyshyn

--
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] PostgreSQL-related topics of theses and seminary works sought (Was: Hash index use presently(?) discouraged...)


17.09.11 23:01, Stefan Keller написав(ла):

* more... ?
What I miss from my DB2 UDB days are buffer pools. In PostgreSQL terms 
this would be part of shared buffers dedicated to a relation or a set of 
relations. When you have a big DB (not fitting in memory) you also 
usually want some small tables/indexes be in memory, no matter what 
other load DB has.

Complimentary features are:
1) Relations preloading at startup - ensure this relation are in memory.
2) Per buffer pool (or relation) page costs - tell it that this 
indexes/tables ARE in memory


Best regards, Vitalii Tymchyshyn.

--
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] PostgreSQL-related topics of theses and seminary works sought (Was: Hash index use presently(?) discouraged...)


Hello.

I did read and AFAIR sometimes responded on this long discussions. The 
main point for me is that many DBAs dont want to have even more random 
plans with postgresql knowing what's in memory now and using this 
information directly in runtime. I also think this point is valid.
What I would like to have is to force some relations to be in memory by 
giving them fixed part of shared buffers and to tell postgresql they are 
in memory (lowering page costs) to have fixed optimal plans.


Best regards, Vitalii Tymchyshyn.

19.09.11 14:57, Cédric Villemain написав(ла):

2011/9/19 Vitalii Tymchyshyntiv...@gmail.com:

17.09.11 23:01, Stefan Keller написав(ла):

* more... ?

What I miss from my DB2 UDB days are buffer pools. In PostgreSQL terms this
would be part of shared buffers dedicated to a relation or a set of
relations. When you have a big DB (not fitting in memory) you also usually
want some small tables/indexes be in memory, no matter what other load DB
has.
Complimentary features are:
1) Relations preloading at startup - ensure this relation are in memory.

you can use pgfincore extension to achieve that, for the OS cache. It
does not look interesting to do that for shared_buffers of postgresql
(the subject has been discussed and can be discussed again, please
check mailling list archieve first)


2) Per buffer pool (or relation) page costs - tell it that this
indexes/tables ARE in memory

you can use tablespace parameters (*_cost) for that, it has been
rejected for tables in the past.
I did propose something to start to work in this direction.
See [WIP] cache estimates, cache access cost in postgresql-hackers
mailling list.

This proposal let inform the planner of the table memory usage and
take that into account.



--
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] Hash index use presently(?) discouraged since 2005: revive or bury it?


19.09.11 18:19, Robert Klemme написав(ла):

On Mon, Sep 19, 2011 at 4:04 PM, Merlin Moncuremmonc...@gmail.com  wrote:


Postgres's hash index implementation used to be pretty horrible -- it
stored the pre-hashed datum in the index which, while making it easier
to do certain things,  made it horribly slow, and, for all intents and
purposes, useless.  Somewhat recently,a lot of work was put in to fix
that -- the index now packs the hash code only which made it
competitive with btree and superior for larger keys.  However, certain
technical limitations like lack of WAL logging and uniqueness hold
hash indexing back from being used like it really should be.  In cases
where I really *do* need hash indexing, I do it in userland.

create table foo
(
  a_long_field text;
);
create index on foo(hash(a_long_field));

select * from foo where hash(a_long_field) = hash(some_value) and
a_long_field = some_value;

This technique works fine -- the main disadvantage is that enforcing
uniqueness is a PITA but since the standard index doesn't support it
either it's no great loss.  I also have the option of getting
'uniqueness' and being able to skip the equality operation if I
sacrifice some performance and choose a strong digest.  Until the hash
index issues are worked out, I submit that this remains the go-to
method to do this.

Is this approach (storing the hash code in a btree) really faster than
a regular btree index on a_long_field?  And if so, for which kind of
data and load?


Actually sometimes the field in [potentially] so long, you can't use 
regular b-tree because it won't fit in the page. Say, it is text type. 
If you will create regular index, you will actually limit column value 
size to few KB. I am using md5(text) indexes in this case coupled with 
rather ugly queries (see above). Native support would be nice.


Best regards, Vitalii Tymchyshyn.

--
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] Hash index use presently(?) discouraged since 2005: revive or bury it?


19.09.11 18:19, Robert Klemme написав(ла):


I still haven't seen a solution to locking when a hash table needs
resizing.  All hashing algorithms I can think of at the moment would
require a lock on the whole beast during the resize which makes this
type of index impractical for certain loads (heavy updating).
Sorry for the second reply, I should have not start writing until I've 
read all your post. Anyway.
Do you need read lock? I'd say readers could use old copy of hash 
table up until the moment new bigger copy is ready. This will simply 
look like the update is not started yet, which AFAIK is OK for MVCC.

Yep, all the writers will wait.

Another option could be to start background build of larger hash - for 
some time your performance will be degraded since you are writing to two 
indexes instead of one plus second one is rebuilding, but I'd say low 
latency solution is possible here.


One more: I don't see actually why can't you have a rolling expand of 
hash table. I will try to describe it correct me if I am wrong:
1) The algorithm I am talking about will take n bits from hash code to 
for hash table. So, during expansion it will double number of baskets.
2) Say, we are going from 2^n = n1 to 2^(n+1) = n2 = n1 * 2 baskets. 
Each new pair of baskets will take data from single source basket 
depending on the value of new hash bit used. E.g. if n were 2, we've had 
4 baskets and new table will have 8 baskets. Everything from old basket 
#1 will go into new baskets #2 and #3 depending on hash value.
3) So, we can have a counter on number of baskets processed. Any 
operation on any lower numbered basket will go to new set. Any 
operation on any higher numbered basket will go to old set. Any 
operation on currently converting basket will block until conversion is 
done.


P.S. Sorry for a lot of possibly dumb thoughts, I don't know why I've 
got such a though stream on this topic :)


Best regards, Vitalii Tymchyshyn.

--
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] cannot use multicolumn index

2011-09-14 Thread Vitalii Tymchyshyn


14.09.11 18:14, MirrorX написав(ла):

i think in my first post i provided most of these details but -
1) what i expect is to be able to understand why the index is not used and
if possibly to use it somehow, or recreate it in a better way
2) the table has 115 GB and about 700 milion rows
3) the result should be less than 10 millions rows
4) the index is a btree

i tried to disable seq_scan and the query plan was changed and used another
index and not the one i wanted.
You has  check on both columns, this means that it has to scan each 
subtree that satisfy one criteria to check against the other. Here index 
column order is significant. E.g. if you have a lot of xid  100 and xid 
is first index column, it must check all (a lot) the index subtrees for 
xid100.
Multicolumn indexes work best when first columns are checked with = 
and only last column with range criteria.
You may still try to change order of columns in your index if this will 
give best selectivity on first column.
Another option is multiple single column indexes - postgres may merge 
such an indexes at runtime (don't remember since which version this 
feature is available).


Best regards, Vitalii Tymchyshyn.


--
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] Rather large LA

2011-09-07 Thread Vitalii Tymchyshyn


Hello.

As it turned out to be iowait, I'd recommend to try to load at least 
some hot relations into FS cache with dd on startup. With a lot of RAM 
on FreeBSD I even sometimes use this for long queries that require a lot 
of index scans.

This converts random IO into sequential IO that is much much faster.
You can try it even while your DB starting - if it works you will see 
IOwait drop and user time raise.
What I do on FreeBSD (as I don't have enough RAM to load all the DB into 
RAM) is:

1) ktrace on backend process[es]. Linux seems to have similar tool
2) Find files that take a lot of long reads
3) dd this files to /dev/null

In this way you can find hot files. As soon as you have them (or if you 
can afford to load everything), you can put dd into startup scripts. Or 
I can imagine an automatic script that will do such things for some time 
after startup.


Best regards, Vitalii Tymchyshyn

--
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] Performance die when COPYing to table with bigint PK

2011-08-05 Thread Vitalii Tymchyshyn


05.08.11 11:44, Robert Ayrapetyan написав(ла):

Yes, you are right. Performance become even more awful.
Can some techniques from pg_bulkload be implemented in postgres core?
Current performance is not suitable for any enterprise-wide production system.

BTW: I was thinking this morning about indexes.
How about next feature:
Implement new index type, that will have two zones - old  new. New 
zone is of fixed configurable size, say 100 pages (800 K).
Any search goes into both zones. So, as soon as index is larger then 
800K, the search must be done twice.
As soon as new zone hit's it's size limit, part (may be only one?) of 
it's pages are merged with old zone. The merge is rolling - if last 
merge've stopped at X entry, next merge will start at entry right after X.


As for me, this should greatly resolve large index insert problem:
1) Insert into new zone must be quick because it's small and hot in cache.
2) During merge writes will be grouped because items with near keys (for 
B-tree) or hashes (for hash index) will go to small subset of old zone 
pages. In future, merge can be also done by autovacuum in background.
Yes, we get dual index search, but new zone will be hot, so this won't 
make it twice as costly.


Best regards, Vitalii Tymchyshyn


--
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] Performance die when COPYing to table with bigint PK

2011-08-04 Thread Vitalii Tymchyshyn


04.08.11 18:59, Kevin Grittner написав(ла):

Robert Ayrapetyanrobert.ayrapet...@comodo.com  wrote:

Kevin Grittnerkevin.gritt...@wicourts.gov  wrote:



[regarding tests which do show the problem]
tried same with 2 columns (bigint and int) - it didn't produced
such effect probably because data volume has critical effect.


Based on what you're showing, this is almost certainly just a matter
of pushing your volume of active data above the threshold of what
your cache holds, forcing it to do disk access rather than RAM
access for a significant portion of the reads.

-Kevin

Yep. Seems so. Plus famous you'd better insert data, then create indexes.
On my database it takes twice the time for int8 then for int4 to insert 
data.
Also it takes ~twice a time (2 hours) to add 200K of rows to 200M of 
rows than to make an index over 200M of rows (1 hour).


Best regards, Vitalii Tymchyshyn

--
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] Performance die when COPYing to table with bigint PK

2011-08-02 Thread Vitalii Tymchyshyn


02.08.11 11:26, Robert Ayrapetyan написав(ла):

Seems this assumption is not right. Just created simple index on
bigint column - situation with huge performance
degradation repeated. Dropping this index solved COPY issues on the fly.
So I'm still convinced - this bug relates to FreeBSD 64-bit + UFS +
bigint column index
(some of these may be superfluous, but I have no resources to check on
different platforms with different filesystems).
Inteesting. We also have FreeBSDx64 on UFS and are using bigint 
(bigserial) keys. It seems I will need to perform more tests here 
because I do see similar problems. I for sure can do a copy of data with 
int4 keys and test the performance.
BTW: The thing we are going to try on next upgrade is to change UFS 
block size from 16K to 8K. What problem I saw is that with default 
setting, UFS needs to read additional 8K when postgresql writes it's 
page (and for index random writes can be vital). Unfortunately, such a 
changes requires partition reformat and I can't afford it for now.


Best regards, Vitalii Tymchyshyn

--
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] insert

2011-08-01 Thread Vitalii Tymchyshyn


Hello.

Please note that in multitasking environment you may have problems with 
your code. Two connections may check if a is available and if not (and 
both got empty select result), try to insert. One will succeed, 
another will fail if you have a unique constraint on category name (and 
you'd better have one).


Please note that select for update won't help you much, since this is 
new record you are looking for, and select don't return (and lock) it. I 
am using lock table tableName in SHARE ROW EXCLUSIVE mode in this case.


But then, if you have multiple lookup dictinaries, you need to ensure 
strict order of locking or you will be getting deadlocks. As for me, I 
did create a special application-side class to retrieve such values. If 
I can't find a value in main connection with simple select, I open new 
connection, perform table lock, check if value is in there. If it is 
not, add the value and commit. This may produce orphaned dictionary 
entries (if dictionary entry is committed an main transaction is rolled 
back), but this is usually OK for dictionaries. At the same time I don't 
introduce hard locks into main transaction and don't have to worry about 
deadlocks.


Best regards, Vitalii Tymchyshyn


--
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] Performance die when COPYing to table with bigint PK

2011-08-01 Thread Vitalii Tymchyshyn


31.07.11 16:51, Robert Ayrapetyan написав(ла):

Hello.

I've found strange behavior of my pg installation (tested both 8.4 and
9.0 - they behave same) on FreeBSD platform.
In short - when some table have PK on bigint field - COPY to that
table from file becomes slower and slower as table grows. When table
reaches ~5GB - COPY of 100k records may take up to 20 mins. I've
experimented with all params in configs, moved indexes to separate hdd
etc - nothing made any improvement. However, once I'm dropping 64 bit
PK - COPY of 100k records passes in seconds. Interesting thing - same
table has other indexes, including composite ones, but none of them
include bigint fields, that's why I reached decision that bug
connected with indexes on bigint fields only.
I did see this behavior, but as for me it occurs for UNIQUE indexes only 
(including PK), not dependent on field type.
You can check this by dropping PK and creating it as a regular 
non-unique index.


Best regards, Vitalii Tymchyshyn

--
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] Long Running Update

2011-06-24 Thread Vitalii Tymchyshyn


24.06.11 14:16, Harry Mantheakis написав(ла):


 EXPLAIN the statement

Here is the EXPLAIN result:

--
QUERY PLAN
--
Hash Join (cost=2589312.08..16596998.47 rows=74558048 width=63)
Hash Cond: (table_A.id = table_B.id)
- Seq Scan on table_A(cost=0.00..1941825.05 rows=95612705 width=47)
- Hash (cost=1220472.48..1220472.48 rows=74558048 width=20)
- Seq Scan on table_B(cost=0.00..1220472.48 rows=74558048 width=20)
--

The documentation says the 'cost' numbers are 'units of disk page 
fetches'.


Do you, by any chance, have any notion of how many disk page fetches 
can be processed per second in practice - at least a rough idea?


IOW how do I convert - guesstimate! - these numbers into (plausible) 
time values?

No chance. This are virtual values for planner only.
If I read correctly, your query should go into two phases: build hash 
map on one table, then update second table using the map. Not that this 
all valid unless you have any constraints (including foreign checks, 
both sides) to check on any field of updated table. If you have, you'd 
better drop them.
Anyway, this is two seq. scans. For a long query I am using a tool like 
ktrace (freebsd) to get system read/write calls backend is doing. Then 
with catalog tables you can map file names to relations 
(tables/indexes). Then you can see which stage you are on and how fast 
is it doing.
Note that partially cached tables are awful (in FreeBSD, dunno for 
linux) for such a query - I suppose this is because instead on 
sequential read, you get a lot of random reads that fools prefetch 
logic. dd if=table_file of=/dev/null bs=8m helps me a lot. You can see 
it it helps if CPU time goes up.


Best regards, Vitalii Tymchyshyn

--
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] Oracle v. Postgres 9.0 query performance

2011-06-08 Thread Vitalii Tymchyshyn


08.06.11 18:40, Tony Capobianco написав(ла):

pg_dw=# set enable_nestloop =0;
SET
Time: 0.165 ms
pg_dw=# explain CREATE TABLE ecr_opens with (FILLFACTOR=100)
pg_dw-# as
pg_dw-# select o.emailcampaignid, count(memberid) opencnt
pg_dw-#   from openactivity o,ecr_sents s
pg_dw-#  where s.emailcampaignid = o.emailcampaignid
pg_dw-#  group by o.emailcampaignid;
QUERY
PLAN
-
  HashAggregate  (cost=4391163.81..4391288.05 rows=9939 width=12)
-   Hash Join  (cost=14.78..4344767.23 rows=9279316 width=12)
  Hash Cond: (o.emailcampaignid = s.emailcampaignid)
  -   Seq Scan on openactivity o  (cost=0.00..3529930.67
rows=192540967 width=12)
  -   Hash  (cost=8.79..8.79 rows=479 width=4)
-   Seq Scan on ecr_sents s  (cost=0.00..8.79 rows=479
width=4)

Yikes.  Two sequential scans.


Yep. Can you see another options? Either you take each of 479 records 
and try to find matching records in another table using index (first 
plan), or you take both two tables fully (seq scan) and join - second plan.
First plan is better if your large table is clustered enough on 
emailcampaignid field (479 index reads and 479 sequential table reads). 
If it's not, you may get a 479 table reads transformed into a lot or 
random reads.
BTW: May be you have different data clustering in PostgreSQL  Oracle? 
Or data in Oracle may be hot in caches?
Also, sequential scan is not too bad thing. It may be cheap enough to 
read millions of records if they are not too wide. Please show select 
pg_size_pretty(pg_relation_size('openactivity')); Have you tried to 
explain analyze second plan?


Best regards, Vitalii Tymchyshyn




On Wed, 2011-06-08 at 11:33 -0400, Tom Lane wrote:

Tony Capobiancotcapobia...@prospectiv.com  writes:

pg_dw=# explain CREATE TABLE ecr_opens with (FILLFACTOR=100)
pg_dw-# as
pg_dw-# select o.emailcampaignid, count(memberid) opencnt
pg_dw-#   from openactivity o,ecr_sents s
pg_dw-#  where s.emailcampaignid = o.emailcampaignid
pg_dw-#  group by o.emailcampaignid;
  QUERY
PLAN
-
  GroupAggregate  (cost=0.00..1788988.05 rows=9939 width=12)
-   Nested Loop  (cost=0.00..1742467.24 rows=9279316 width=12)
  -   Index Scan using ecr_sents_ecid_idx on ecr_sents s
(cost=0.00..38.59 rows=479 width=4)
  -   Index Scan using openact_emcamp_idx on openactivity o
(cost=0.00..3395.49 rows=19372 width=12)
Index Cond: (o.emailcampaignid = s.emailcampaignid)
(5 rows)
Should this query be hashing the smaller table on Postgres rather than
using nested loops?

Yeah, seems like it.  Just for testing purposes, do set enable_nestloop
= 0 and see what plan you get then.



--
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] 8.4/9.0 simple query performance regression

2011-06-07 Thread Vitalii Tymchyshyn


07.06.11 00:45, Josh Berkus написав(ла):

All,

Just got this simple case off IRC today:

8.4.4
This plan completes in 100ms:
Filter: (NOT (hashed SubPlan 1))



9.0.2
This plan does not complete in 15 minutes or more:
Filter: (NOT (SubPlan 1))

Hashed is the key. Hashed subplans usually has much better performance.
You need to increase work_mem. I suppose it is in default state as you 
need not too much memory for hash of 70K integer values.
BTW: Why do it want to materialize a result of seq scan without filter. 
I can see no benefits (or is it more narrow rows?)


Best regards, Vitalii Tymchyshyn

--
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] [PERFORMANCE] expanding to SAN: which portion best to move

2011-05-25 Thread Vitalii Tymchyshyn


24.05.11 21:48, Greg Smith написав(ла):


Bitmap heap scan: Here, the exact list of blocks to fetch is known in 
advance, they're random, and it's quite possible for the kernel to 
schedule them more efficiently than serial access of them can do. This 
was added as the effective_io_concurrency feature (it's the only thing 
that feature impacts), which so far is only proven to work on Linux. 
Any OS implementing the POSIX API used will also get this however; 
FreeBSD was the next likely candidate that might benefit when I last 
looked around.

FreeBSD unfortunately do not have the support :(
It has AIO, but does not have the call needed to enable this settings.

Best regards, Vitalii Tymchyshyn

--
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] reducing random_page_cost from 4 to 2 to force index scan

2011-05-24 Thread Vitalii Tymchyshyn


Hello.

As of me, all this hot thing really looks like uncertain and dynamic 
enough.
Two things that I could directly use right now (and they are needed in 
pair) are:
1)Per-table/index/database bufferpools (split shared buffer into parts, 
allow to specify which index/table/database goes where)

2)Per-table/index cost settings

If I had this, I could allocate specific bufferpools for tables/indexes 
that MUST be hot in memory and set low costs for this specific tables.
P.S. Third thing, great to have to companion this two is Load on 
startup flag to automatically populate bufferpools with fast sequential 
read, but this can be easily emulated with a statement.


Best regards, Vitalii Tymchyshyn

--
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] Postgres refusing to use 1 core

2011-05-12 Thread Vitalii Tymchyshyn


12.05.11 06:18, Aren Cambre ???(??):


 Using one thread, the app can do about 111 rows per second, and it's
 only exercising 1.5 of 8 CPU cores while doing this. 12,000,000
rows /
 111 rows per second ~= 30 hours.

I don't know how I missed that. You ARE maxing out one cpu core, so
you're quite right that you need more threads unless you can make your
single worker more efficient.


And the problem is my app already has between 20 and 30 threads. 
Something about C#'s PLINQ may not be working as intended...


Have you checked that you are really doing fetch and processing in 
parallel? Dunno about C#, but under Java you have to make specific 
settings (e.g. setFetchSize) or driver will fetch all the data on query 
run. Check time needed to fetch first row from the query.


Best regards, Vitalii Tymchyshyn

Re: [PERFORM] Shouldn't we have a way to avoid risky plans?

2011-03-25 Thread Vitalii Tymchyshyn


24.03.11 20:41, Merlin Moncure написав(ла):

2011/3/24 Віталій Тимчишинtiv...@gmail.com:


This can se GUC-controllable. Like plan_safety=0..1 with low default value.
This can influence costs of plans where cost changes dramatically with small
table changes and/or statistics is uncertain. Also this can be used as
direct hint for such dangerous queries by changing GUC for session/single
query.

ISTM if you add statistics miss and 'risk margin' to the things the
planner would have to consider while generating a plan, you are
greatly increasing the number of plan paths that would have to be
considered for any non trivial query.
Why so? I simply change cost estimation functions. This won't change 
number of pathes.


Best regards, Vitalii Tymchyshyn.

--
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] Shouldn't we have a way to avoid risky plans?

2011-03-25 Thread Vitalii Tymchyshyn


25.03.11 16:12, Tom Lane написав(ла):

Vitalii Tymchyshyntiv...@gmail.com  writes:


Why so? I simply change cost estimation functions. This won't change
number of pathes.

If you have multiple figures of merit, that means you have to keep more
paths, with consequent slowdown when it comes to choosing which path to
use at higher join levels.

As an example, we used to keep only the paths with best total cost.
When we started to optimize LIMIT, we had to keep around paths with best
startup cost too, in case that made for the best combined result at a
higher join level.  If you're going to consider risk while choosing
paths, that means you'll start keeping paths you would have discarded
before, while not necessarily getting rid of any other paths.  The only
way to avoid that would be to have a completely brain-dead notion of
risk that wasn't affected by how the path is used at a higher join
level, and I'm pretty sure that that wouldn't solve anybody's problem.

Any significant expansion of the planner's fundamental cost model *will*
make it slower.  By a lot.  Rather than going into this with fantasies
of it won't cost anything, you should be worrying about how to keep
the speed penalty to factor-of-two rather than factor-of-ten.
But I am not talking about model change, it's more like formula change. 
Introducing limit added one variable where outer plan could influence 
inner plan selection.
But I am talking simply about cost calculation for given node. Now cost 
is based on statistical expected value, the proposal is (something like) 
to take maximum cost on n% probability range near expected value.
This, of course, will make calculations slower, but won't add any degree 
of freedom to calculations.


Best regards, Vitalii Tymchyshyn



--
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] Reason of Slowness of query


23.03.11 08:28, Adarsh Sharma ???(??):

*
*I perform a join query on it as :

* explain analyze select distinct(p.crawled_page_id) from page_content 
p , clause2  c where p.crawled_page_id != c.source_id ;*
Your query is wrong. This query will return every *crawled_page_id* if 
clause2 has more then 1 source_id. This is because DB will be able to 
find clause with source_id different from crawled_page_id. You need to 
use not exists or not in.


Best regards, Vitalii Tymchyshyn.

Re: [PERFORM] Re-Reason of Slowness of Query


23.03.11 11:17, Adarsh Sharma ???(??):


I think it is very much faster but I don't understand the query :

*explain select distinct(b) from t1,t2 where t1.b t2.d union all  
select distinct(b) from t1,t2 where  t1.b t2.d;

*

I don't understand it too. What are you trying to get? Is it
select distinct(b) from t1 where  b  (select min(d) from t2)**or b  
(select max(d) from t2)

?

Can you explain in words, not SQL, what do you expect do retrieve?

Best regards, Vitalii Tymchyshyn

Re: [PERFORM] Re-Reason of Slowness of Query


23.03.11 12:10, Adarsh Sharma ???(??):
I just want to retrieve that id 's from page_content which do not have 
any entry in clause2 table.



Then
select distinct(p.crawled_page_id) from page_content p
 where NOT EXISTS (select 1 from  clause2 c where c.source_id = 
p.crawled_page_id);

is correct query.

Best regards, Vitalii Tymchyshyn.

Re: [PERFORM] Re-Reason of Slowness of Query


23.03.11 12:19, Adarsh Sharma ???(??):

Vitalii Tymchyshyn wrote:

23.03.11 12:10, Adarsh Sharma ???(??):
I just want to retrieve that id 's from page_content which do not 
have any entry in clause2 table.



Then
select distinct(p.crawled_page_id) from page_content p
 where NOT EXISTS (select 1 from  clause2 c where c.source_id = 
p.crawled_page_id);

is correct query.



I can't understand how*select 1 from  clause2 c where c.source_id = 
p.crawled_page_id works too, *i get my output .


What is the significance of 1 here.
No significance. You can put anything there. E.g. *. Simply arbitrary 
constant. Exists checks if there were any rows, it does not matter which 
columns are there or what is in this columns.


Best regards, Vitalii Tymchyshyn

Re: [PERFORM] Re-Reason of Slowness of Query


23.03.11 13:21, Adarsh Sharma ???(??):

Thank U all, for U'r Nice Support.

Let me Conclude the results, below results are obtained after finding 
the needed queries :


*First Option :

*pdc_uima=# explain analyze select distinct(p.crawled_page_id)
pdc_uima-# from page_content p left join clause2 c on (p.crawled_page_id =
pdc_uima(# c.source_id) where (c.source_id is null);
 
QUERY PLAN

-
 HashAggregate  (cost=100278.16..104104.75 rows=382659 width=8) 
(actual time=87927.000..87930.084 rows=72 loops=1)
   -  Nested Loop Anti Join  (cost=0.00..99320.46 rows=383079 
width=8) (actual time=0.191..87926.546 rows=74 loops=1)
 -  Seq Scan on page_content p  (cost=0.00..87132.17 
rows=428817 width=8) (actual time=0.027..528.978 rows=428467 loops=1)
 -  Index Scan using idx_clause2_source_id on clause2 c  
(cost=0.00..18.18 rows=781 width=4) (actual time=0.202..0.202 rows=1 
loops=428467)

   Index Cond: (p.crawled_page_id = c.source_id)
 Total runtime: 87933.882 ms:-(
(6 rows)

*Second Option :

*pdc_uima=# explain analyze select distinct(p.crawled_page_id) from 
page_content p
pdc_uima-#  where NOT EXISTS (select 1 from  clause2 c where 
c.source_id = p.crawled_page_id);
 
QUERY PLAN

-
 HashAggregate  (cost=100278.16..104104.75 rows=382659 width=8) 
(actual time=7047.259..7050.261 rows=72 loops=1)
   -  Nested Loop Anti Join  (cost=0.00..99320.46 rows=383079 
width=8) (actual time=0.039..7046.826 rows=74 loops=1)
 -  Seq Scan on page_content p  (cost=0.00..87132.17 
rows=428817 width=8) (actual time=0.008..388.976 rows=428467 loops=1)
 -  Index Scan using idx_clause2_source_id on clause2 c  
(cost=0.00..18.18 rows=781 width=4) (actual time=0.013..0.013 rows=1 
loops=428467)

   Index Cond: (c.source_id = p.crawled_page_id)
 Total runtime: 7054.074 ms :-)
(6 rows)



Actually the plans are equal, so I suppose it depends on what were run 
first :). Slow query operates with data mostly on disk, while fast one 
with data in memory.


Best regards, Vitalii Tymchyshyn

Re: [PERFORM] Reason of Slowness of query