Re: [PERFORM] Prepared statements and suboptimal plans

2011-09-21 Thread Grzegorz Jaśkiewicz
one thing, in SUM() , you don't have to coalesce. Consider following example: foo=# create table bar(id serial primary key, a float); NOTICE: CREATE TABLE will create implicit sequence bar_id_seq for serial column bar.id NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index bar_pkey for

Re: [PERFORM] cannot use multicolumn index

2011-09-14 Thread Grzegorz Jaśkiewicz
http://wiki.postgresql.org/wiki/Guide_to_reporting_problems -- 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] Query performance issue

2011-09-04 Thread Grzegorz Jaśkiewicz
Order by ...upper(xyz), do you have functional index on these ? -- 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 optimization regression?

2011-08-30 Thread Grzegorz Jaśkiewicz
2011/8/29 Mark Kirkwood mark.kirkw...@catalyst.net.nz: I note from the commit message that the fix test case was from Grzegorz Jaskiewicz (antijoin against a small subset of a relation).  I was not able to find this in the archives - Grzegorz do you recall the actual test case? I thought it

Re: [PERFORM] Delete performance

2011-05-31 Thread Grzegorz Jaśkiewicz
9.0rc1 ? You know that the stable 9.0 has been out for quite a while now. Its not going to affect the delete speed in any way, but I would generally advice you to upgrade it to the lates 9.0.x As for the delete it self, check if you have indices on the tables that refer the main table on the

Re: [PERFORM] serveRAID M5014 SAS

2011-05-26 Thread Grzegorz Jaśkiewicz
The card is configured in 1+0 . with 128k stripe afaik (I'm a developer, we don't have hardware guys here). Are you's sure about the lack of cache by default on the card ? I thought the difference is that 5104 has 256, and 5105 has 512 ram already on it. -- Sent via pgsql-performance mailing

[PERFORM] serveRAID M5014 SAS

2011-05-25 Thread Grzegorz Jaśkiewicz
Does anyone here have any bad experiences with the RAID card in subject ? This is in an IBM server, with 2.5 10k drives. But we seem to observe its poor performance in other configurations as well (with different drives, different settings) in comparison with - say, what dell provides. Any

Re: [PERFORM] Is Query need to be optimized

2011-03-03 Thread Grzegorz Jaśkiewicz
do you have any indexes on that table ? -- 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] Tunning Postgres

2010-12-13 Thread Grzegorz Jaśkiewicz
Try going through the archives first because your question probably has been answered many times already (altho there is no definitive question as to what server postgresql would need to run to fit your purpose). Also, this is English list. If you prefer to ask questions in Brazilian/Portuguese

Re: [PERFORM] anti-join chosen even when slower than old plan

2010-11-09 Thread Grzegorz Jaśkiewicz
you're joining on more than one key. That always hurts performance. -- 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] which one is faster

2010-10-26 Thread Grzegorz Jaśkiewicz
implementation wise, count(*) is faster. Very easy to test: SELECT COUNT(*) FROM generate_series(1,100) a, generate_series(1,1000) b; SELECT COUNT(a) FROM generate_series(1,100) a, generate_series(1,1000) b; ;] -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To

Re: [PERFORM] Inefficient query plan

2010-08-23 Thread Grzegorz Jaśkiewicz
On Mon, Aug 23, 2010 at 2:47 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Grzegorz Jaœkiewiczgryz...@gmail.com wrote: joining on varchars is always going to be very expensive. Longer the value is, more expensive it will be. Consider going for surrogate keys. Surrogate keys come

Re: [PERFORM] Inefficient query plan

2010-08-23 Thread Grzegorz Jaśkiewicz
I am not a fan of 'do this - this is best' response to queries like that. Rather: this is what you should try, and choose whichever one suits you better. So, rather than 'natural keys ftw', I am giving him another option to choose from. You see, in my world, I was able to improve some large dbs

Re: [PERFORM] Inefficient query plan

2010-08-23 Thread Grzegorz Jaśkiewicz
Oh, and I second using same types in joins especially, very much so :) -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] raid10 write performance

2010-06-22 Thread Grzegorz Jaśkiewicz
Hi folks, is there a general problem with raid10 performance postgresql on it? We see very low performance on writes (2-3x slower than on less performant servers). I wonder if it is solely problem of raid10 configuration, or if it is postgresql's thing. Would moving WAL dir to separate disk help

Re: [PERFORM] performance of temporary vs. regular tables

2010-05-26 Thread Grzegorz Jaśkiewicz
WAL matters in performance. Hence why it is advisable to have it on a separate drive :) -- 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 of temporary vs. regular tables

2010-05-25 Thread Grzegorz Jaśkiewicz
temporary tables are handled pretty much like the regular table. The magic happens on schema level, new schema is setup for connection, so that it can access its own temporary tables. Temporary tables also are not autovacuumed. And that's pretty much the most of the differences. -- Sent via

Re: [PERFORM] performance of temporary vs. regular tables

2010-05-25 Thread Grzegorz Jaśkiewicz
WAL does the same thing to DB journaling does to the FS. Plus allows you to roll back (PITR). As for the RAM, it will be in ram as long as OS decides to keep it in RAM cache, and/or its in the shared buffers memory. Unless you have a lot of doubt about the two, I don't think it makes too much

Re: [PERFORM] Slow Bulk Delete

2010-05-17 Thread Grzegorz Jaśkiewicz
On Mon, May 17, 2010 at 12:54 PM, Jon Nelson jnelson+pg...@jamponi.net wrote: On Mon, May 17, 2010 at 5:10 AM, Pierre C li...@peufeu.com wrote: - or use a JOIN delete with a virtual VALUES table - or fill a temp table with ids and use a JOIN DELETE What is a virtual VALUES table? Can you give

Re: [PERFORM] Slow Bulk Delete

2010-05-17 Thread Grzegorz Jaśkiewicz
again VALUES(1,2), (2,3), ; is a 'virtual table', as he calls it. It really is not a table to postgresql. I guess he is just using that naming convention. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription:

Re: [PERFORM] Replacing Cursors with Temporary Tables

2010-04-24 Thread Grzegorz Jaśkiewicz
On Sat, Apr 24, 2010 at 2:23 PM, Merlin Moncure mmonc...@gmail.com wrote: Well, you missed the most important part: not using cursors at all. Instead of declaring a cursor and looping it to build the array, build it with array(). That's what I've been saying: arrays can completely displace

Re: [PERFORM] PostgreSQL with Zabbix - problem of newbe

2010-04-14 Thread Grzegorz Jaśkiewicz
That really sounds like hardware issue. The I/O causes the system to freeze basically. Happens sometimes on cheaper hardware.

Re: [PERFORM] PostgreSQL with Zabbix - problem of newbe

2010-04-08 Thread Grzegorz Jaśkiewicz
starting with 8.3, there's this new feature called HOT, which helps a lot when you do loads of updates. Plus writer is much quicker (30-40% sometimes), and autovacuum behaves much nicer. Bottom line, upgrade to 8.3, 8.1 had autovacuum disabled by default for a reason.

Re: [PERFORM] LIMIT causes planner to do Index Scan using a less optimal index

2010-04-07 Thread Grzegorz Jaśkiewicz
On Wed, Apr 7, 2010 at 1:20 PM, sherry.ctr@faa.gov wrote: Guys, Thanks for trying and opening your mind. If you want to know how Oracle addressed this issue, here it is: index on two columns. I remember that they told me in the training postgres has no this kind of index, can

Re: [PERFORM] LIMIT causes planner to do Index Scan using a less optimal index

2010-04-07 Thread Grzegorz Jaśkiewicz
2010/4/7 sherry.ctr@faa.gov Do you mean one index on two columns? something like this: create index idx1 on tb1(col1, col2); yup :) It would be quite useless without that feature. Don't listen to oracle folks, they obviously know not much about products others than oracle db(s).

Re: [PERFORM] mysql to postgresql, performance questions

2010-03-18 Thread Grzegorz Jaśkiewicz
time that psql or pgAdmin shows is purely the postgresql time. Question here was about the actual application's time. Sometimes the data transmission, fetch and processing on the app's side can take longer than the 'postgresql' time.

Re: [PERFORM] Bad query plan inside EXISTS clause

2010-03-10 Thread Grzegorz Jaśkiewicz
try JOINs...

Re: [PERFORM] No hash join across partitioned tables?

2010-03-02 Thread Grzegorz Jaśkiewicz
On Tue, Mar 2, 2010 at 4:23 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Feb 25, 2010 at 7:03 PM, Tom Lane t...@sss.pgh.pa.us wrote: Partially. There are stats now but autovacuum is not bright about when to update them. Is that something

Re: [PERFORM] Autovacuum Tuning advice

2010-03-01 Thread Grzegorz Jaśkiewicz
storing all fields as varchar surely doesn't make: - indicies small, - the thing fly, - tables small. ...

Re: [PERFORM] Slow query: table iteration (8.3)

2010-02-04 Thread Grzegorz Jaśkiewicz
isn't that possible with window functions and cte ? rank, and limit ? -- 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] Poor query plan across OR operator

2010-01-26 Thread Grzegorz Jaśkiewicz
just create index on both columns: CREATE INDEX foo_i ON foo(bar1, bar2); HTH -- 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 optimization question

2010-01-09 Thread Grzegorz Jaśkiewicz
maybe that 'one big table' needs something called 'normalisation' first. See how much that will shed off. You might be surprised. The partitioning needs to be done by some constant intervals, of time - in your case. Whatever suits you, I would suggest to use the rate that will give you both ease

Re: [PERFORM] Joint index including MAX() ?

2010-01-09 Thread Grzegorz Jaśkiewicz
you can also try : select val FROM table ORDER BY val DESC LIMIT 1; which usually is much quicker. -- 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] Air-traffic benchmark

2010-01-07 Thread Grzegorz Jaśkiewicz
On Thu, Jan 7, 2010 at 3:05 PM, Lefteris lsi...@gmail.com wrote: On Thu, Jan 7, 2010 at 3:51 PM, Ivan Voras ivo...@freebsd.org wrote: On 7.1.2010 15:23, Lefteris wrote: I think what you all said was very helpful and clear! The only part that I still disagree/don't understand is the

Re: [PERFORM] Automatic optimization of IN clauses via INNER JOIN

2009-12-18 Thread Grzegorz Jaśkiewicz
On Fri, Dec 18, 2009 at 2:18 PM, Robert Haas robertmh...@gmail.com wrote: NOT IN is the only that really kills you as far as optimization is concerned.  IN can be transformed to a join.  NOT IN forces a NOT (subplan)-type plan, which bites - hard. in a well designed database (read: not

Re: [PERFORM] Automatic optimization of IN clauses via INNER JOIN

2009-12-18 Thread Grzegorz Jaśkiewicz
2009/12/18 Robert Haas robertmh...@gmail.com: 2009/12/18 Grzegorz Jaśkiewicz gryz...@gmail.com: On Fri, Dec 18, 2009 at 2:18 PM, Robert Haas robertmh...@gmail.com wrote: NOT IN is the only that really kills you as far as optimization is concerned.  IN can be transformed to a join

Re: [PERFORM] Automatic optimization of IN clauses via INNER JOIN

2009-12-17 Thread Grzegorz Jaśkiewicz
On Thu, Dec 17, 2009 at 6:05 PM, Robert Haas robertmh...@gmail.com wrote: On Thu, Dec 17, 2009 at 10:23 AM, Thomas Hamilton thomashamilto...@yahoo.com wrote: Apparently the latest version of MySQL has solved this problem:

Re: [PERFORM] Analyse without locking?

2009-11-26 Thread Grzegorz Jaśkiewicz
On Thu, Nov 26, 2009 at 4:20 PM, Richard Neill rn...@cam.ac.uk wrote: Dear All, I'm wondering whether Vacuum/analyse (notably by the autovaccuum daemon) is responsible for some deadlocks/dropouts I'm seeing. One particular table gets hit about 5 times a second (for single row updates and

Re: [PERFORM] DELETE performance problem

2009-11-25 Thread Grzegorz Jaśkiewicz
On Wed, Nov 25, 2009 at 4:13 PM, Luca Tettamanti kronos...@gmail.comwrote: DELETE FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t1.annotation_id = t2.annotation_id) performs event better: Seq Scan on t1 (cost=0.00..170388415.89 rows=22937406 width=6) (actual time=272.625..561241.294

Re: [PERFORM] Query times change by orders of magnitude as DB ages

2009-11-25 Thread Grzegorz Jaśkiewicz
On Wed, Nov 25, 2009 at 4:26 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Richard Neill rn...@cam.ac.uk wrote: In terms of just index bloat, does a regular vacuum help? You might want to use the REINDEX command to correct serious index bloat. A regular vacuum will make dead

Re: [PERFORM] DELETE performance problem

2009-11-24 Thread Grzegorz Jaśkiewicz
On Tue, Nov 24, 2009 at 3:19 PM, Thom Brown thombr...@gmail.com wrote: 2009/11/24 Luca Tettamanti kronos...@gmail.com On Tue, Nov 24, 2009 at 3:59 PM, Jerry Champlin jchamp...@absolute-performance.com wrote: You may want to consider using partitioning. That way you can drop the

Re: [PERFORM] Performance regression 8.3.8 - 8.4.1 with NOT EXISTS

2009-11-18 Thread Grzegorz Jaśkiewicz
usual answer - use LEFT JOIN luke.

Re: [PERFORM] CREATE TABLE slowing down significantly over time

2009-11-09 Thread Grzegorz Jaśkiewicz
On Mon, Nov 9, 2009 at 3:58 AM, Robert Haas robertmh...@gmail.com wrote: And maybe REINDEX, too. yup, nevermind the mess in table, indices are getting fscked much quicker than table it self, because of its structure. -- GJ

Re: [PERFORM] Problem with database performance, Debian 4gb ram ?

2009-11-02 Thread Grzegorz Jaśkiewicz
On Mon, Nov 2, 2009 at 2:16 PM, Grant Masan grant.mas...@gmail.com wrote: Hi Hi all, I have now readed many many forums and tried many different solutions and I am not getting good performance to database. My server is Debian linux, with 4gb ram, there is also java application and I am

Re: [PERFORM] sub-select in IN clause results in sequential scan

2009-10-30 Thread Grzegorz Jaśkiewicz
for explains, use http://explain.depesz.com/ besides, why are you using left join ? equivlent of IN () is just JOIN, not LEFT JOIN. And please, format your query so it readable without twisting eyeballs before sending.

Re: [PERFORM] sub-select in IN clause results in sequential scan

2009-10-29 Thread Grzegorz Jaśkiewicz
On Wed, Oct 28, 2009 at 6:13 PM, Anj Adu fotogra...@gmail.com wrote: Postgres consistently does a sequential scan on the child partitions for this query select * from partitioned_table where partitioned_column current_timestamp - interval 8 days where x in (select yy from z where colname

Re: [PERFORM] Postgresql optimisation

2009-10-28 Thread Grzegorz Jaśkiewicz
On Wed, Oct 28, 2009 at 12:11 PM, Denis BUCHER dbuche...@hsolutions.chwrote: Dear all, I need to optimize a database used by approx 10 people, I don't need to have the perfect config, simply to avoid stupid bottle necks and follow the best practices... The database is used from a web

Re: [PERFORM] Postgresql optimisation

2009-10-28 Thread Grzegorz Jaśkiewicz
2009/10/28 Denis BUCHER dbuche...@hsolutions.ch Grzegorz Jaśkiewicz a écrit : On Wed, Oct 28, 2009 at 12:11 PM, Denis BUCHER dbuche...@hsolutions.ch mailto:dbuche...@hsolutions.ch wrote: Dear all, I need to optimize a database used by approx 10 people, I don't need

Re: [PERFORM] query planning different in plpgsql?

2009-10-23 Thread Grzegorz Jaśkiewicz
On Fri, Oct 23, 2009 at 4:49 PM, Scott Mead scott.li...@enterprisedb.comwrote: Do you not have an index on last_snapshot.domain_id? that, and also try rewriting a query as JOIN. There might be difference in performance/plan. -- GJ

Re: [PERFORM] Partitioned Tables and ORDER BY

2009-10-19 Thread Grzegorz Jaśkiewicz
On Sun, Oct 11, 2009 at 3:30 PM, Michal Szymanski mich20...@gmail.comwrote: We have similar problem and now we are try to find solution. When you execute query on partion there is no sorting - DB use index to retrieve data and if you need let say 50 rows it reads 50 rows using index. But when

Re: [PERFORM] Known Bottlenecks

2009-10-19 Thread Grzegorz Jaśkiewicz
On Mon, Oct 19, 2009 at 2:43 PM, Vikul Khosla vkho...@gridsolv.com wrote: Jeff, Robert, I am still working on the low cardinality info you requested. Please bear with me. In the meantime, have the following question: Are there known scenarios where certain types of SQL queries perform

Re: [PERFORM] Partitioned Tables and ORDER BY

2009-10-19 Thread Grzegorz Jaśkiewicz
2009/10/19 Robert Haas robertmh...@gmail.com 2009/10/19 Grzegorz Jaśkiewicz gryz...@gmail.com: On Sun, Oct 11, 2009 at 3:30 PM, Michal Szymanski mich20...@gmail.com wrote: We have similar problem and now we are try to find solution. When you execute query on partion

Re: [PERFORM] index on two tables or Howto speedup max/aggregate-function

2009-10-13 Thread Grzegorz Jaśkiewicz
On Tue, Oct 13, 2009 at 9:59 AM, Michael Schwipps msc.lis...@online.dewrote: Hi, I want to select the last contact of person via mail. My sample database is build with the following shell-commands | createdb -U postgres test2 | psql -U postgres test2 mail_db.sql | mailtest.sh | psql -U

Re: [PERFORM] Getting a random row

2009-10-13 Thread Grzegorz Jaśkiewicz
On Tue, Oct 13, 2009 at 4:17 PM, Shaul Dar shaul...@gmail.com wrote: Hi, I am running performance simulation against a DB. I want to randomly pull different records from a large table. However the table has no columns that hold sequential integer values (1..MAX), i.e. the columns all have

Re: [PERFORM] Getting a random row

2009-10-13 Thread Grzegorz Jaśkiewicz
2009/10/13 Shaul Dar shaul...@gmail.com Sorry, I guess I wasn't clear. I have an existing table in my DB, and it doesn't have a column with serial values (actually it did originally, but due to later deletions of about 2/3 of the rows the column now has holes). I realize I could add a new

Re: [PERFORM] Query performance

2009-10-12 Thread Grzegorz Jaśkiewicz
On Mon, Oct 12, 2009 at 12:21 PM, S Arvind arvindw...@gmail.com wrote: In the below query both table has less than 1 million data. Can u tell me the reason of this plan? why its takin extensive cost , seq scan and sorting?? wat is Materialize? select 1 from service_detail left join

Re: [PERFORM] Query performance

2009-10-12 Thread Grzegorz Jaśkiewicz
btw, what's the version of db ? what's the work_mem setting ? try setting work_mem to higher value. As postgresql will fallback to disc sorting if the content doesn't fit in work_mem, which it probably doesn't (8.4+ show the memory usage for sorting, which your explain doesn't have).

Re: [PERFORM] Query performance

2009-10-12 Thread Grzegorz Jaśkiewicz
2009/10/12 Matthew Wakeling matt...@flymine.org This is an EXPLAIN, not an EXPLAIN ANALYSE. If it was an EXPLAIN ANALYSE, it would show how much memory was used, and whether it was a disc sort or an in-memory sort. As it is only an EXPLAIN, the query hasn't actually been run, and we have no

Re: [PERFORM] Query performance

2009-10-12 Thread Grzegorz Jaśkiewicz
2009/10/12 S Arvind arvindw...@gmail.com Thanks Grzegorz, But work memory is for each process (connection) rt? so if i keep more then 10MB will not affect the overall performance ? it will. But the memory is only allocated when needed. You can always set it before running that

Re: [PERFORM] Query plan for NOT IN

2009-10-07 Thread Grzegorz Jaśkiewicz
On Mon, Oct 5, 2009 at 8:35 PM, Guy Rouillier guyr-...@burntmail.comwrote: Grzegorz Jaśkiewicz wrote: well, as a rule of thumb - unless you can't think of a default value of column - don't use nulls. So using nulls as default 'idunno' - is a bad practice, but everybody's opinion

Re: [PERFORM] Bad performance of SELECT ... where id IN (...)

2009-10-05 Thread Grzegorz Jaśkiewicz
On Mon, Oct 5, 2009 at 1:24 PM, Omar Kilani omar.kil...@gmail.com wrote: I'm not really sure what the alternatives are -- it never really makes sense to get the selectivity for thousands of items in the IN clause. I've never seen a different plan for the same query against a DB with that

Re: [PERFORM] Query plan for NOT IN

2009-10-05 Thread Grzegorz Jaśkiewicz
On Mon, Oct 5, 2009 at 2:52 PM, Matthew Wakeling matt...@flymine.orgwrote: mnw21-modmine-r13features-copy=# select count(*) from project; count --- 10 (1 row) mnw21-modmine-r13features-copy=# select count(*) from intermineobject; count -- 26344616 (1 row)

Re: [PERFORM] Query plan for NOT IN

2009-10-05 Thread Grzegorz Jaśkiewicz
2009/10/5 Matthew Wakeling matt...@flymine.org Yes, that does work, but only because id is NOT NULL. I thought Postgres 8.4 had had a load of these join types unified to make it less important how the query is written? well, as a rule of thumb - unless you can't think of a default value of

Re: [PERFORM] Bad performance of SELECT ... where id IN (...)

2009-09-26 Thread Grzegorz Jaśkiewicz
if you reuse that set a lot, how about storing it in a table , and doing the join on db side ? if it is large, it sometimes makes sense to create temp table just for single query (I use that sort of stuff for comparing with few M records). But temp tables in that case have to be short lived, as

Re: [PERFORM] High CPU load on Postgres Server during Peak times!!!!

2009-09-25 Thread Grzegorz Jaśkiewicz
On Fri, Sep 25, 2009 at 9:06 AM, Shiva Raman raman.shi...@gmail.com wrote: Hi Gerhard I also found the pg_log has 73 G of data . clusternode2:/var/lib/pgsql/data # du -sh pg_log/ 73G pg_log/ Is it necessary to keep this Log files? Can i backup the logs and delete it from the original

Re: [PERFORM] High CPU load on Postgres Server during Peak times!!!!

2009-09-25 Thread Grzegorz Jaśkiewicz
2009/9/25 Shiva Raman raman.shi...@gmail.com As suggested, i had changed the log_statement='ddl' and now it is logging only the ddl statements . thanks for the tip. Can i delete the old log files in pg_log after backing up as zip archive ? is it neccesary to keep those log files ? they're

Re: [PERFORM] Speed while runnning large transactions.

2009-09-24 Thread Grzegorz Jaśkiewicz
On Thu, Sep 24, 2009 at 9:27 AM, jes...@krogh.cc wrote: Hi. I have a transaction running at the database for around 20 hours .. still isn't done. But during the last hours it has come to the point where it really hurts performance of other queries. Given pg_stat_activity output there seems

Re: [PERFORM] query memory consumption

2009-09-22 Thread Grzegorz Jaśkiewicz
On Tue, Sep 22, 2009 at 1:36 PM, Alan McKay alan.mc...@gmail.com wrote: Too high?  How high is too high? in a very simple scenario, you have 100 connections opened, and all of them run the query that was the reason you bumped work_mem to 256M. All of the sudden postgresql starts to complain

Re: [PERFORM] query memory consumption

2009-09-22 Thread Grzegorz Jaśkiewicz
On Tue, Sep 22, 2009 at 1:46 PM, Alan McKay alan.mc...@gmail.com wrote: Best practice to avoid that, is to bump the work_mem temporarily before the query, and than lower it again, lowers the chance of memory exhaustion. Interesting - I can do that dynamically? you can do set work_mem=128M;

Re: [PERFORM] Index row requires 9324 bytes maximum size is 8191

2009-09-21 Thread Grzegorz Jaśkiewicz
not only that's slow, but limited as you can see. Use something like: http://gjsql.wordpress.com/2009/04/19/how-to-speed-up-index-on-bytea-text-etc/ instead. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription:

Re: [PERFORM] CLUSTER and a problem

2009-09-16 Thread Grzegorz Jaśkiewicz
On Tue, Sep 15, 2009 at 9:10 PM, Andrzej Zawadzki zawa...@wp.pl wrote: So, I was close - bad index... DESCending is much better. Thanks to Grzegorz Ja\skiewicz  hi has strengthened me in the conjecture. I'm posting this - maybe someone will find something useful in that case. ps. query was

Re: [PERFORM] Forcing postgresql to use an index

2009-09-08 Thread Grzegorz Jaśkiewicz
Learn it to not generate with WITH IN (subq), is this can be quite slow on postgresql. Use joins instead. looks like planner was wrong about rowcount in one place: Hash IN Join (cost=2204.80..4809.31 rows=292 width=202) (actual time=12.856..283.916 rows=15702 loops=1) I have no idea why,

Re: [PERFORM] Using Gprof with Postgresql

2009-09-07 Thread Grzegorz Jaśkiewicz
postgresql was faster than the files ;) (sorry, I just couldn't resist). -- 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] Query tuning

2009-08-19 Thread Grzegorz Jaśkiewicz
that seems to be the killer: and time = extract ('epoch' from timestamp '2009-08-12') and time extract ('epoch' from timestamp '2009-08-13' ) You probably need an index on time/epoch: CREATE INDEX foo ON table(extract ('epoch' from timestamp time ); or something like that, vacuum analyze and

Re: [PERFORM] Why is PostgreSQL so slow on Windows ( Postgres 8.3.7) version

2009-08-03 Thread Grzegorz Jaśkiewicz
how about normalizing the schema for start ? by the looks of it, you have huge table,with plenty of varchars, that smells like bad design of db. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription:

Re: [PERFORM] hyperthreaded cpu still an issue in 8.4?

2009-07-21 Thread Grzegorz Jaśkiewicz
On Tue, Jul 21, 2009 at 1:42 PM, Doug Hunleyd...@hunley.homeip.net wrote: Just wondering is the issue referenced in http://archives.postgresql.org/pgsql-performance/2005-11/msg00415.php is still present in 8.4 or if some tunable (or other) made the use of hyperthreading a non-issue. We're

Re: [PERFORM] hyperthreaded cpu still an issue in 8.4?

2009-07-21 Thread Grzegorz Jaśkiewicz
On Tue, Jul 21, 2009 at 3:16 PM, Scott Marlowescott.marl...@gmail.com wrote: On Tue, Jul 21, 2009 at 6:42 AM, Doug Hunleyd...@hunley.homeip.net wrote: Just wondering is the issue referenced in http://archives.postgresql.org/pgsql-performance/2005-11/msg00415.php is still present in 8.4 or if

Re: [PERFORM] Sorting by an arbitrary criterion

2009-07-09 Thread Grzegorz Jaśkiewicz
On Thu, Jul 9, 2009 at 5:26 PM, Craig Jamescraig_ja...@emolecules.com wrote: Suppose I have a large table with a small-cardinality CATEGORY column (say, categories 1..5).  I need to sort by an arbitrary (i.e. user-specified) mapping of CATEGORY, something like this:  1 = 'z'  2 = 'a'  3 =

Re: [PERFORM] Sorting by an arbitrary criterion

2009-07-09 Thread Grzegorz Jaśkiewicz
2009/7/9 Tom Lane t...@sss.pgh.pa.us: =?UTF-8?Q?Grzegorz_Ja=C5=9Bkiewicz?= gryz...@gmail.com writes: On Thu, Jul 9, 2009 at 5:26 PM, Craig Jamescraig_ja...@emolecules.com wrote: Suppose I have a large table with a small-cardinality CATEGORY column (say, categories 1..5).  I need to sort by

Re: [PERFORM] How would you store read/unread topic status?

2009-06-23 Thread Grzegorz Jaśkiewicz
not better just to store last time user visited the topic ? or forum in general, and compare that ?

Re: [PERFORM] same query in high number of times

2009-06-21 Thread Grzegorz Jaśkiewicz
On Sun, Jun 21, 2009 at 9:01 PM, Justin Grafjus...@emproshunts.com wrote: work_mem = 51024    # min 64, size in KB Thats allot memory dedicated to work mem if you have 30 connections open this could eat up 1.5gigs pushing the data out of cache. I thought work memory is max

Re: [PERFORM] very slow selects on a small table

2009-06-18 Thread Grzegorz Jaśkiewicz
On Thu, Jun 18, 2009 at 6:06 PM, Brian Coxbrian@ca.com wrote: these queries are still running now 27.5 hours later... These queries are generated by some java code and in putting it into a test program so I could capture the queries, I failed to get the id range correct -- sorry for

Re: [PERFORM] very slow selects on a small table

2009-06-18 Thread Grzegorz Jaśkiewicz
On Thu, Jun 18, 2009 at 6:16 PM, Brian Cox brian@ca.com wrote: Grzegorz Jakiewicz [gryz...@gmail.com] wrote: this might be quite bogus question, just a hit - but what is your work_mem set to ? Guys, isn't postgresql giving hudge cost, when it can't sort in memory ? work_mem = 64MB try

Re: [PERFORM] Speeding up a query.

2009-06-17 Thread Grzegorz Jaśkiewicz
On Wed, Jun 17, 2009 at 8:33 AM, Albe Laurenzlaurenz.a...@wien.gv.at wrote: I don't understand your data model well enough to understand the query, so I can only give you general hints (which you probably already know): He is effectively joining same table 4 times in a for loop, to get

Re: [PERFORM] poor performing plan from analyze vs. fast default plan pre-analyze on new database

2009-06-03 Thread Grzegorz Jaśkiewicz
Postgresql isn't very efficient with subselects like that, try: explain select c.id from content c LEFT JOIN (select min(id) AS id from content group by hash) cg ON cg.id=c.id WHERE cg.id is null; -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to

Re: [PERFORM] Scalability in postgres

2009-05-29 Thread Grzegorz Jaśkiewicz
On Fri, May 29, 2009 at 2:54 AM, Greg Smith gsm...@gregsmith.com wrote:  The PostgreSQL connection handler is known to be bad at handling high connection loads compared to the popular pooling projects, so you really shouldn't throw this problem at it. While kernel problems stack on top of

Re: [PERFORM] Scalability in postgres

2009-05-29 Thread Grzegorz Jaśkiewicz
2009/5/29 Scott Marlowe scott.marl...@gmail.com: if it is implemented somewhere else better, shouldn't that make it obvious that postgresql should solve it internally ? It is really annoying to hear all the time that you should add additional path of execution to already complex stack, and

Re: [PERFORM] Scalability in postgres

2009-05-29 Thread Grzegorz Jaśkiewicz
2009/5/29 Scott Marlowe scott.marl...@gmail.com: Both Oracle and PostgreSQL have fairly heavy backend processes, and running hundreds of them on either database is a mistake.    Sure, Oracle can handle more transactions and scales a bit better, but no one wants to have to buy a 128 way E15K

Re: [PERFORM] Scalability in postgres

2009-05-29 Thread Grzegorz Jaśkiewicz
damn I agree with you Scott. I wish I had enough cash here to employ Tom and other pg magicians to improve performance for all of us ;) Thing is tho, postgresql is mostly used by companies, that either don't have that sort of cash, but still like to get the performance, or companies that have

Re: [PERFORM] Storing sensor data

2009-05-28 Thread Grzegorz Jaśkiewicz
depends on how soon do you need to access that data after it's being created, the way I do it in my systems, I get data from 8 points, bit less than you - but I dump it to csv, and import it on database host (separate server). now, you could go to BDB or whatever, but that's not the solution. So,

Re: [PERFORM] Improve Query

2009-05-27 Thread Grzegorz Jaśkiewicz
try creating index on all three columns. Btw, 38ms is pretty fast. If you run that query very often, do prepare it, cos I reckon it takes few ms to actually create plan for it. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription:

Re: [PERFORM] Improve Query

2009-05-27 Thread Grzegorz Jaśkiewicz
you have to vacuum analyze after you've created index, afaik. No, count(*) is still counting rows. -- 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] Problems with autovacuum

2009-05-25 Thread Grzegorz Jaśkiewicz
2009/5/25 Scott Marlowe scott.marl...@gmail.com: So, in 2000 databases, there's only an average of 2 relations per db and 102 dead rows?  Cause that's all you got room for with those settings. Whats the last 20 or so lines of vacuum verbose as run by a superuser say? according to

Re: [PERFORM] Problems with autovacuum

2009-05-25 Thread Grzegorz Jaśkiewicz
2009/5/25 Łukasz Jagiełło lukasz.jagie...@gforces.pl: W dniu 25 maja 2009 17:32 użytkownik Scott Marlowe scott.marl...@gmail.com napisał: Recent change postgresql server from Amazon EC2 small into large one. That gives me x86_64 arch, two core cpu and 7.5GB ram. Atm got almost ~2000 small

Re: [PERFORM] bad plan and LIMIT

2009-05-01 Thread Grzegorz Jaśkiewicz
EXISTS won't help much either, postgresql is not too fast, when it comes to that sort of approach. join is always going to be fast, it is about time you learn joins and use them ;) -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription:

Re: [PERFORM] bad plan and LIMIT

2009-05-01 Thread Grzegorz Jaśkiewicz
use join instead of where in(); -- 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] SQL With Dates

2009-04-20 Thread Grzegorz Jaśkiewicz
BETWEEN X AND Y On Mon, Apr 20, 2009 at 2:55 PM, Rafael Domiciano rafael.domici...@gmail.com wrote: Hello People, I have initiated a work to review the sqls of our internal software. Lot of them he problem are about sql logic, or join with table unecessary, and so on. But software has lot

[PERFORM] stats are way off on 8.4 b1

2009-04-17 Thread Grzegorz Jaśkiewicz
crawler=# select * from assigments; jobid | timeout | workerid ---+-+-- (0 rows) Time: 0.705 ms crawler=# \d+ assigments Table public.assigments Column | Type |Modifiers |

Re: [PERFORM] stats are way off on 8.4 b1

2009-04-17 Thread Grzegorz Jaśkiewicz
2009/4/18 Tom Lane t...@sss.pgh.pa.us: =?UTF-8?Q?Grzegorz_Ja=C5=9Bkiewicz?= gryz...@gmail.com writes: That expected 1510 rows in 'assigments' seems to be pretty off, The planner does not trust an empty table to stay empty.  Every Postgres version in living memory has acted like that; it's not

Re: [PERFORM] Shouldn't the planner have a higher cost for reverse index scans?

2009-04-16 Thread Grzegorz Jaśkiewicz
create index foobar on table(row desc); -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

  1   2   >