Re: [PERFORM] Tuning PostgreSQL

2003-07-22 Thread Bruno Wolff III
On Tue, Jul 22, 2003 at 11:40:35 +0200, Vincent van Leeuwen [EMAIL PROTECTED] wrote: About RAID types: the fastest RAID type by far is RAID-10. However, this will cost you a lot of useable diskspace, so it isn't for everyone. You need at least 4 disks for a RAID-10 array. RAID-5 is a nice

Re: [PERFORM] Indexes not used for min()

2003-08-04 Thread Bruno Wolff III
On Mon, Aug 04, 2003 at 15:05:08 -0600, Valsecchi, Patrick [EMAIL PROTECTED] wrote: Sir, I did a search with the index keyword on the mailing list archive and it did come with no result. Sorry if it's a known bug. It isn't a bug. It is a design trade off. The database has no special

Re: [PERFORM] bad estimates / non-scanning aggregates

2003-08-29 Thread Bruno Wolff III
On Thu, Aug 28, 2003 at 17:10:31 -0700, Ken Geis [EMAIL PROTECTED] wrote: The query I want to run is select stock_id, min(price_date) from day_ends group by stock_id; The fast way to do this is: select distinct on (stock_id) stock_id, price_date order by stock_id, price_date; Also, to

Re: [PERFORM] bad estimates / non-scanning aggregates

2003-08-29 Thread Bruno Wolff III
On Thu, Aug 28, 2003 at 19:50:38 -0700, Ken Geis [EMAIL PROTECTED] wrote: Bruno Wolff III wrote: On Thu, Aug 28, 2003 at 17:10:31 -0700, Ken Geis [EMAIL PROTECTED] wrote: The query I want to run is select stock_id, min(price_date) from day_ends group by stock_id; The fast way to do

Re: [PERFORM] bad estimates

2003-08-29 Thread Bruno Wolff III
On Thu, Aug 28, 2003 at 20:46:00 -0700, Ken Geis [EMAIL PROTECTED] wrote: A big problem is that the values I am working with are *only* the primary key and the optimizer is choosing a table scan over an index scan. That is why I titled the email bad estimates. The table has (stock_id,

Re: [PERFORM] bad estimates

2003-08-30 Thread Bruno Wolff III
I haven't come up with any great ideas for this one. It might be interesting to compare the explain analyze output from the distinct on query with and without seqscans enabled. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet,

Re: [PERFORM] pgsql inserts problem

2003-08-28 Thread Bruno Wolff III
On Wed, Aug 27, 2003 at 15:50:32 +0300, Tarhon-Onu Victor [EMAIL PROTECTED] wrote: The problems is that only ~15% of the lines are inserted into the database. The same script modified to insert the same data in a similar table created in a MySQL database inserts 100%. Did you check

Re: [PERFORM] Inconsistent performance

2003-09-15 Thread Bruno Wolff III
On Mon, Sep 15, 2003 at 17:34:12 -0400, Joseph Bove [EMAIL PROTECTED] wrote: I do a rather simple query: select count (*) from large-table where column = some value; About 80% of the time, the response time is sub-second. However, at 10% of the time, the response time is 5 - 10

Re: [PERFORM] count(*) slow on large tables

2003-10-02 Thread Bruno Wolff III
that hasn't committed or in the case of serializable isolation, a transaction that committed after the current transaction started. On Thu, Oct 02, 2003 at 02:39:05PM -0500, Bruno Wolff III wrote: On Thu, Oct 02, 2003 at 12:15:47 -0700, Dror Matalon [EMAIL PROTECTED] wrote: Hi, I have

Re: [PERFORM] reindex/vacuum locking/performance?

2003-10-03 Thread Bruno Wolff III
On Fri, Oct 03, 2003 at 15:47:01 -0600, Rob Nagler [EMAIL PROTECTED] wrote: vacuum full does require exclusive lock, plain vacuum does not. I think I need full, because there are updates on the table. As I understand it, an update in pg is an insert/delete, so it needs to be garbage

Re: [PERFORM] Postgres low end processing.

2003-10-06 Thread Bruno Wolff III
On Mon, Oct 06, 2003 at 09:55:51 +0200, Stef [EMAIL PROTECTED] wrote: Thanks, I'll try some of these, and post the results. The actual machines seem to be Pentium I machines, with 32M RAM. I've gathered that it is theoretically possible, so no to go try it. I am running 7.4beta2 on a

Re: [PERFORM] Speeding up Aggregates

2003-10-09 Thread Bruno Wolff III
On Thu, Oct 09, 2003 at 17:44:46 -0700, Dror Matalon [EMAIL PROTECTED] wrote: How is doing order by limit 1 faster than doing max()? Seems like the optimizer will need to sort or scan the data set either way. That part didn't actually make a difference in my specific case. max() will never

Re: [PERFORM] Ignoring index on (A is null), (A is not null) conditions

2003-10-30 Thread Bruno Wolff III
On Thu, Oct 30, 2003 at 12:34:15 +0100, Cestmir Hybl [EMAIL PROTECTED] wrote: Are you seeing this question as totally off-topic in this list, or there is really no one who knows something about indexing is null bits in postgres? There was some talk about IS NULL not being able to use indexes

Re: [PERFORM] problem with select count(*) ..

2003-11-19 Thread Bruno Wolff III
On Thu, Nov 20, 2003 at 07:07:30 +0530, Rajesh Kumar Mallah [EMAIL PROTECTED] wrote: If i dump and reload the performance improves and it takes 1 sec. This is what i have been doing since the upgrade. But its not a solution. The Vacuum full is at the end of a loading batch SQL file which

Re: [PERFORM] Where to start for performance problem?

2003-11-25 Thread Bruno Wolff III
On Mon, Nov 24, 2003 at 16:03:17 -0600, MK Spam [EMAIL PROTECTED] wrote: The archives of this list provides many ideas for improving performance, but the problem we are having is gradually degrading performance ending in postgres shutting down. So it's not a matter of optimizing a

Re: [PERFORM] hints in Postgres?

2003-12-11 Thread Bruno Wolff III
On Thu, Dec 11, 2003 at 11:00:19 -0500, sandra ruiz [EMAIL PROTECTED] wrote: Hi list, I need to know if there is anything like hints of Oracle in Postgres..otherwise..I wish to find a way to force a query plan to use the indexes or tell the optimizer things like optimize based in

Re: [PERFORM] deferred foreign keys

2004-01-05 Thread Bruno Wolff III
On Mon, Jan 05, 2004 at 11:33:40 -0500, Vivek Khera [EMAIL PROTECTED] wrote: Thanks. Then it sorta makes it moot for me to try deferred checks, since the Pimary and Foreign keys never change once set. I wonder what is making the transactions appear to run lockstep, then... I think this

Re: [PERFORM] Slow query problem

2004-01-08 Thread Bruno Wolff III
On Thu, Jan 08, 2004 at 19:27:16 -0800, Mike Glover [EMAIL PROTECTED] wrote: You should bump sort_mem as high as you can stand. with only 8MB sort memory available, you're swapping intermediate sort pages to disk -- a lot. Try the query with sort_mem set to 75MB (to do the entire sort in

Re: [PERFORM] ORDER BY and LIMIT with SubSelects

2004-01-21 Thread Bruno Wolff III
On Wed, Jan 21, 2004 at 09:18:18 -0800, Ron St-Pierre [EMAIL PROTECTED] wrote: My question is in regards to steps 2 and 3 above. Is there some way that I can combine both steps into one to save some time? TIP 4: Don't 'kill -9' the postmaster SELECT SS.* FROM (SELECT DISTINCT ON

Re: [PERFORM] [SQL] limit 1 and functional indexes

2004-01-29 Thread Bruno Wolff III
One other suggestion I forgot is that this should move over to the performance list rather than being on the sql list. The right people are more likely to see your question there. On Thu, Jan 29, 2004 at 16:02:06 +0100, Alexandra Birch [EMAIL PROTECTED] wrote: Postgres choses the wrong

Re: [PERFORM] cache whole data in RAM

2004-02-03 Thread Bruno Wolff III
On Tue, Feb 03, 2004 at 13:54:17 +0100, David Teran [EMAIL PROTECTED] wrote: Hi, we are trying to speed up a database which has about 3 GB of data. The server has 8 GB RAM and we wonder how we can ensure that the whole DB is read into RAM. We hope that this will speed up some queries.

Re: [PERFORM] [ADMIN] Index called with Union but not with OR clause

2004-02-20 Thread Bruno Wolff III
This discussion really belongs on the performance list and I am copying that list with mail-followup-to set. On Fri, Feb 20, 2004 at 12:26:22 +0530, V Chitra [EMAIL PROTECTED] wrote: Hi All, I have a select statement select * from v_func_actual_costs where parent_project='10478' or

Re: [PERFORM] [PERFORMANCE] slow small delete on large table

2004-02-23 Thread Bruno Wolff III
On Mon, Feb 23, 2004 at 19:10:57 -0700, Ed L. [EMAIL PROTECTED] wrote: A 7.3.4 question... I want to expire some data after 90 days, but not delete too much at once so as not to overwhelm a system with precariously balanced disk I/O and on a table with millions of rows. If I could say

Re: [PERFORM] Optimisation of INTERSECT expressions

2004-03-23 Thread Bruno Wolff III
On Tue, Mar 23, 2004 at 11:21:39 -0500, Phil Endecott [EMAIL PROTECTED] wrote: Does anyone have any suggestions about how to do this? I'd like a nice general technique that works for all possible subqueries, as my current composition with INTERSECT does. One adjustment you might make is

Re: [PERFORM] postgres eating CPU on HP9000

2004-03-29 Thread Bruno Wolff III
On Mon, Mar 29, 2004 at 12:00:16 -0500, Fabio Esposito [EMAIL PROTECTED] wrote: I'm sorry all, when you say regular user as opposed to superuser are you talking about the user that postgres is installed and running as? Should this be done as the os's root? The os user used for creating

Re: [PERFORM] single index on more than two coulumns a bad thing?

2004-04-02 Thread Bruno Wolff III
On Fri, Apr 02, 2004 at 01:00:45 +0200, Palle Girgensohn [EMAIL PROTECTED] wrote: Is it always bad to create index xx on yy (field1, field2, field3); I guess the problem is that the index might often grow bigger than the table, or at least big enough not to speed up the queries? One

Re: [PERFORM] Effect of too many columns

2004-04-12 Thread Bruno Wolff III
On Mon, Apr 12, 2004 at 17:24:17 +0530, Rajesh Kumar Mallah [EMAIL PROTECTED] wrote: Greetings, Is there any performance penalty of having too many columns in a table in terms of read and write speeds. To order to keep operational queries simple (avoid joins) we plan to add columns in

Re: [PERFORM] index v. seqscan for certain values

2004-04-12 Thread Bruno Wolff III
On Mon, Apr 12, 2004 at 15:05:02 -0400, Jeremy Dunn [EMAIL PROTECTED] wrote: Agreed. However, given that count(*) is a question that can be answered _solely_ using the index (without reference to the actual data blocks), I'd expect that the break-even point would be considerably higher

Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.

2004-04-15 Thread Bruno Wolff III
On Wed, Apr 14, 2004 at 21:12:18 +0100, Simon Riggs [EMAIL PROTECTED] wrote: I guess what I'm saying is it's not how many people you've got working on the optimizer, its how many accurate field reports of less-than perfect optimization reach them. In that case, PostgreSQL is likely in a

Re: [PERFORM] Horribly slow hash join

2004-04-18 Thread Bruno Wolff III
On Sun, Apr 18, 2004 at 18:27:09 +0200, Dennis Bjorklund [EMAIL PROTECTED] wrote: On Sun, 18 Apr 2004, Tom Lane wrote: What do you mean? int8 is supported on all platformas No it isn't. So on platforms where it isn't you would use int4 as the biggest int then. I don't really see

Re: [PERFORM] Use of subquery causes seq scan???

2004-04-20 Thread Bruno Wolff III
Please don't reply to messages to start new threads. On Tue, Apr 20, 2004 at 10:20:05 -0400, Chris Hoover [EMAIL PROTECTED] wrote: I need some help. I have a query that refuses to use the provided index and is always sequentially scanning causing me large performance headaches. Here is

Re: [JDBC] [PERFORM] is a good practice to create an index on the

2004-04-28 Thread Bruno Wolff III
On Wed, Apr 28, 2004 at 10:13:14 +0200, Edoardo Ceccarelli [EMAIL PROTECTED] wrote: do you mean that, declaring an index serial, I'd never have to deal with incrementing its primary key? good to know! That isn't what is happening. Serial is a special type. It is int plus a default rule

Re: [PERFORM] Additional select fields in a GROUP BY

2004-06-13 Thread Bruno Wolff III
On Sun, Jun 13, 2004 at 06:21:17 +0300, Vitaly Belman [EMAIL PROTECTED] wrote: Consider the following query: select t1field1, avg(t2fieild2) from t1, t2 where t1.field1 = t2.field2 group by t1field1 That works fine. But I'd really like to see more fields of t1 in this query, however

Re: [PERFORM] postgres 7.4 at 100%

2004-06-29 Thread Bruno Wolff III
On Tue, Jun 29, 2004 at 01:37:30 -0700, Chris Cheston [EMAIL PROTECTED] wrote: ok i just vacuumed it and it's taking slightly longer now to execute (only about 8 ms longer, to around 701 ms). Not using indexes for calllogs(from)... should I? The values for calllogs(from) are not unique

Re: [PERFORM] no index-usage on aggregate-functions?

2004-06-29 Thread Bruno Wolff III
On Tue, Jun 29, 2004 at 10:46:27 +0200, Harald Lau (Sector-X) [EMAIL PROTECTED] wrote: h... So, it seems that PG is not s well suited for a datawarehouse and/or performing extensive statistics/calculations/reportings on large tables, is it? If you are doing lots of selects of

Re: [PERFORM] Query performance

2004-06-29 Thread Bruno Wolff III
On Tue, Jun 29, 2004 at 12:33:51 -0500, Bill [EMAIL PROTECTED] wrote: Ok, thanks. So let me explain the query number 2 as this is the more difficult to write. So I have a list of stocks, this table contains the price of all of the stocks at the open and close date. Ok, now we have a ratio

Re: [PERFORM] Mysterious performance of query because of plsql function in where condition

2004-07-02 Thread Bruno Wolff III
On Fri, Jul 02, 2004 at 09:48:48 +0200, Peter Alberer [EMAIL PROTECTED] wrote: Postgres seems to execute the function submission_status for every row of the submissions table (~1500 rows). The query therefore takes quite a lot time, although in fact no row is returned from the assignments

Re: [PERFORM] BUG #1186: Broken Index?

2004-07-02 Thread Bruno Wolff III
On Fri, Jul 02, 2004 at 04:50:07 -0300, PostgreSQL Bugs List [EMAIL PROTECTED] wrote: The following bug has been logged online: This doesn't appear to be a bug at this point. It sounds like you have a self induced performance problem, so I am moving the discussion to pgsql-performance.

Re: [PERFORM] insert

2004-08-13 Thread Bruno Wolff III
On Fri, Aug 13, 2004 at 08:57:56 -0400, Rod Taylor [EMAIL PROTECTED] wrote: On Fri, 2004-08-13 at 08:10, Ulrich Wisser wrote: Hi, my inserts are done in one transaction, but due to some foreign key constraints and five indexes sometimes the 100 inserts will take more than 5

Re: [PERFORM] insert

2004-08-13 Thread Bruno Wolff III
On Fri, Aug 13, 2004 at 17:17:10 +0100, Matt Clark [EMAIL PROTECTED] wrote: It is likely that you are missing an index on one of those foreign key'd items. I don't think that is too likely as a foreign key reference must be a unique key which would have an index. I think you

Re: [PERFORM] Restricting Postgres

2004-11-04 Thread Bruno Wolff III
On Thu, Nov 04, 2004 at 22:37:06 +, Matt Clark [EMAIL PROTECTED] wrote: ... Yup. If you go the JS route then you can do even better by using JS to load data into JS objects in the background and manipulate the page content directly, no need for even an Iframe. Ignore the dullards

Re: [PERFORM] Restricting Postgres

2004-11-04 Thread Bruno Wolff III
On Thu, Nov 04, 2004 at 23:32:57 +, Matt Clark [EMAIL PROTECTED] wrote: I think in the future there will be a good bit of presentation login in the client... Not if Bruno has his way ;-) Sure there will, but it will be controlled by the client, perhaps taking suggestions from

Re: [PERFORM] Strange (?) Index behavior?

2004-11-05 Thread Bruno Wolff III
On Fri, Nov 05, 2004 at 09:39:16 -0500, Allen Landsidel [EMAIL PROTECTED] wrote: For some reason it's a requirement that partial wildcard searches are done on this field, such as SELECT ... WHERE field LIKE 'A%' I thought an interesting way to do this would be to simply create partial

Re: [PERFORM] INSERT question

2004-12-13 Thread Bruno Wolff III
On Mon, Dec 13, 2004 at 08:28:39 -0800, sarlav kumar [EMAIL PROTECTED] wrote: Is there a way to write the INSERT as follows? INSERT into merchant_buyer_country (merchant_id,country,enabled,group_id) values (1203, (SELECT code FROM country WHERE send IS NOT NULL OR receive IS NOT

Re: [PERFORM] Error in VACUUM FULL VERBOSE ANALYZE (not enough memory)

2004-12-17 Thread Bruno Wolff III
On Fri, Dec 17, 2004 at 14:46:57 -0500, Tom Lane [EMAIL PROTECTED] wrote: This looks like it must be a memory leak in the gist indexing code (either gist itself or tsearch2). I don't see any post-release fixes in the 7.4 branch that look like they fixed any such thing :-(, so it's

Re: [PERFORM] Similar tables, different indexes performance

2004-12-13 Thread Bruno Wolff III
On Mon, Dec 13, 2004 at 17:32:02 -0200, Alvaro Nunes Melo [EMAIL PROTECTED] wrote: Em Seg, 2004-12-13 às 16:03, Bruno Wolff III escreveu: On Mon, Dec 13, 2004 at 15:17:49 -0200, Alvaro Nunes Melo [EMAIL PROTECTED] wrote: db= SELECT COUNT(*) FROM titulo WHERE cd_pessoa = 1; count

Re: [PERFORM] Seqscan rather than Index

2004-12-17 Thread Bruno Wolff III
On Fri, Dec 17, 2004 at 22:56:27 +0100, Steinar H. Gunderson [EMAIL PROTECTED] wrote: I'm a bit unsure -- should counting ~3 million rows (no OIDs, PG 7.4, everything in cache, 32-byte rows) take ~3500ms on an Athlon 64 2800+? It doesn't seem totally out of wack. You will be limited by the

Re: [PERFORM] Postgres version change - pg_dump

2004-12-20 Thread Bruno Wolff III
On Mon, Dec 20, 2004 at 06:40:34 -0800, sarlav kumar [EMAIL PROTECTED] wrote: I would like to do a pg_dump on the test database, and restore it in the new database on Postgres 7.4.6. I would like to know if there would be any problem due to the postgres version/OS change. If so, could

Re: [PERFORM] Why so much time difference with a same query/plan?

2004-12-30 Thread Bruno Wolff III
On Sun, Dec 26, 2004 at 13:30:15 +0100, Karl Vogel [EMAIL PROTECTED] wrote: This depends on the version of Oracle you're using. Oracle 9i introduced Index Skip Scans: http://www.oracle.com/technology//products/oracle9i/daily/apr22.html I don't know whether pg has something similar?

Re: [PERFORM] TEXT field and Postgresql Perfomance

2005-01-07 Thread Bruno Wolff III
On Fri, Jan 07, 2005 at 19:36:47 -0800, Loren M. Lang [EMAIL PROTECTED] wrote: Do large TEXT or VARCHAR entries in postgresql cause any performance degradation when a query is being executed to search for data in a table where the TEXT/VARCHAR fields aren't being searched themselves? Yes in

Re: [PERFORM] Help with EXPLAIN ANALYZE runtimes

2005-01-08 Thread Bruno Wolff III
On Sun, Jan 09, 2005 at 16:45:18 +1100, Guenzl, Martin [EMAIL PROTECTED] wrote: LOL ... Excuse my ignorance but what's Karnak headear? Jonny Carson used to do sketches on the Tonight show where he was Karnak and would give answers to questions in sealed envelopes which would later be read by

Re: [PERFORM]

2005-01-20 Thread Bruno Wolff III
On Thu, Jan 20, 2005 at 11:31:29 -0500, Alex Turner [EMAIL PROTECTED] wrote: I am curious - I wasn't aware that postgresql supported partitioned tables, Could someone point me to the docs on this. Some people have been doing it using a union view. There isn't actually a partition feature.

Re: [PERFORM] column without pg_stats entry?!

2005-01-20 Thread Bruno Wolff III
On Thu, Jan 20, 2005 at 11:14:28 +0100, Bernd Heller [EMAIL PROTECTED] wrote: I wondered why the planner was making such bad assumptions about the number of rows to find and had a look at pg_stats. and there was the surprise: there is no entry in pg_stats for that column at all!! I can

Re: [PERFORM] PostgreSQL clustering VS MySQL clustering

2005-01-22 Thread Bruno Wolff III
On Sat, Jan 22, 2005 at 12:13:00 +0900, Tatsuo Ishii [EMAIL PROTECTED] wrote: Probably VACUUM works well for small to medium size tables, but not for huge ones. I'm considering about to implement on the spot salvaging dead tuples. You are probably vacuuming too often. You want to wait

Re: [PERFORM] slow count()

2005-01-27 Thread Bruno Wolff III
On Thu, Jan 27, 2005 at 21:17:56 -0800, Zavier Sheran [EMAIL PROTECTED] wrote: quote from manual: -- Unfortunately, there is no similarly trivial query that can be used to improve the performance of count() when applied to the entire table -- does count(1) also cause a sequential scan

Re: [PERFORM] Benchmark

2005-02-11 Thread Bruno Wolff III
On Fri, Feb 11, 2005 at 02:22:39 -0500, Jaime Casanova [EMAIL PROTECTED] wrote: What about the free speech rigths, in USA they are in the constitution and cannot be denied or revoked, IANAL. You can voluntarily give up your rights to free speech in the US. And like stated by Mitch just

Re: [PERFORM] is pg_autovacuum so effective ?

2005-02-28 Thread Bruno Wolff III
On Mon, Feb 28, 2005 at 16:46:34 +0100, Markus Schaber [EMAIL PROTECTED] wrote: Hi, Matthew, Matthew T. O'Connor schrieb: The version of pg_autovacuum that I submitted for 8.0 could be instructed per table but it didn't make the cut. Aside from moved out of contrib and integrated

Re: [PERFORM] View vs function

2005-03-20 Thread Bruno Wolff III
On Sun, Mar 20, 2005 at 22:39:57 -0500, Keith Worthington [EMAIL PROTECTED] wrote: Hi All, I have been reading about set returning functions. What I would like to know is is there a performance advantage in using SRFs versus querying a view. Assuming the underlying SQL is the same for

Re: [PERFORM] What about utility to calculate planner cost constants?

2005-03-22 Thread Bruno Wolff III
On Tue, Mar 22, 2005 at 08:09:40 -0500, Christopher Browne [EMAIL PROTECTED] wrote: Are you certain it's a linear system? I'm not. If it was a matter of minimizing a linear expression subject to some set of linear equations, then we could model this as a Linear Program for which there

Re: [PERFORM] Reading recommendations

2005-03-30 Thread Bruno Wolff III
On Wed, Mar 30, 2005 at 16:39:47 -, Mohan, Ross [EMAIL PROTECTED] wrote: VOIP over BitTorrent? Plain VOIP shouldn't be a problem. And if you want to do tricky things you can use Asterisk on both ends. Asterisk is open source (GPL, duel licensed from Digium) and runs on low powered linux

Re: [PERFORM] date - range

2005-04-02 Thread Bruno Wolff III
On Sat, Apr 02, 2005 at 00:01:31 -0700, Michael Fuhr [EMAIL PROTECTED] wrote: On Fri, Apr 01, 2005 at 09:59:44PM -0800, Mischa wrote: select ... where first_date = today and last_date = today Whatever index we create system always does a sequential scan (which I can

Re: [PERFORM] Delete query takes exorbitant amount of time

2005-04-03 Thread Bruno Wolff III
On Tue, Mar 29, 2005 at 01:48:48 -0700, Karim A Nassar [EMAIL PROTECTED] wrote: For this FK check, there only need be one referring id to invalidate the delete. ISTM that for any delete with a FK reference, the index could always be used to search for a single value in the referring table

Re: [PERFORM] coalesce alternative

2005-04-03 Thread Bruno Wolff III
On Tue, Mar 29, 2005 at 14:21:13 +0300, ALÝ ÇELÝK [EMAIL PROTECTED] wrote: I have used coalesce function for null fields but coalesce is too slow. I need fast alternative for coalesce It is unlikely that coalesce is your problem. People might be able to provide some help if you provide

Re: [PERFORM] Recognizing range constraints (was Re: Plan for relatively simple query seems to be very inefficient)

2005-04-07 Thread Bruno Wolff III
On Wed, Apr 06, 2005 at 18:09:37 -0400, Tom Lane [EMAIL PROTECTED] wrote: Can anyone suggest a more general rule? Do we need for example to consider whether the relation membership is the same in two clauses that might be opposite sides of a range restriction? It seems like a.x

Re: [PERFORM] Question on REINDEX

2005-04-18 Thread Bruno Wolff III
On Mon, Apr 18, 2005 at 12:21:42 -0700, Bill Chandler [EMAIL PROTECTED] wrote: Running PostgreSQL 7.4.2 on Solaris. 1) When is it necessary to run REINDEX or drop/create an index? All I could really find in the docs is: In some situations it is worthwhile to rebuild indexes

Re: [PERFORM] Question on vacuumdb

2005-04-18 Thread Bruno Wolff III
On Mon, Apr 18, 2005 at 12:27:08 -0700, Bill Chandler [EMAIL PROTECTED] wrote: All, If I run the command vacuumdb mydb I understand that it does some disk space recovery (but not as much as vacuumdb --full mydb). You are better off not using vacuum full unless some unusual event has

Re: [PERFORM] COPY vs INSERT

2005-05-06 Thread Bruno Wolff III
On Fri, May 06, 2005 at 01:51:29 -0500, Jim C. Nasby [EMAIL PROTECTED] wrote: On Wed, May 04, 2005 at 10:22:56PM -0400, Tom Lane wrote: Also, there is a whole lot of one-time-per-statement overhead that can be amortized across many rows instead of only one. Stuff like opening the target

Re: [PERFORM] Partitioning / Clustering

2005-05-10 Thread Bruno Wolff III
On Tue, May 10, 2005 at 08:02:50 -0700, Adam Haberlach [EMAIL PROTECTED] wrote: With all the Opteron v. Xeon around here, and talk of $30,000 machines, perhaps it would be worth exploring the option of buying 10 cheapass machines for $300 each. At the moment, that $300 buys you, from

Re: [PERFORM] Select performance vs. mssql

2005-05-24 Thread Bruno Wolff III
On Tue, May 24, 2005 at 08:36:36 -0700, mark durrant [EMAIL PROTECTED] wrote: --MSSQL's ability to hit the index only and not having to go to the table itself results in a _big_ performance/efficiency gain. If someone who's in development wants to pass this along, it would be a nice

Re: [PERFORM] Can anyone explain this: duplicate dbs.

2005-05-24 Thread Bruno Wolff III
On Tue, May 24, 2005 at 21:39:15 -0500, John A Meinel [EMAIL PROTECTED] wrote: By the way, I think doing: CREATE DATABASE tempdb WITH TEMPLATE = originaldb; Is a much faster way of doing dump and load. I *think* it would recreate indexes, etc. If it just does a copy it may not show the

Re: [PERFORM] Index on a NULL-value

2005-05-30 Thread Bruno Wolff III
On Tue, May 31, 2005 at 11:02:07 +0800, Tobias Brox [EMAIL PROTECTED] wrote: I read in the manual today: Indexes are not used for IS NULL clauses by default. The best way to use indexes in such cases is to create a partial index using an IS NULL predicate. This is from the

Re: [PERFORM] Index on a NULL-value

2005-05-30 Thread Bruno Wolff III
On Tue, May 31, 2005 at 11:21:20 +0800, Tobias Brox [EMAIL PROTECTED] wrote: [Tobias Brox - Tue at 11:02:07AM +0800] test=# explain select * from mock where b is NULL; QUERY PLAN

Re: [PERFORM] Index on a NULL-value

2005-05-30 Thread Bruno Wolff III
On Tue, May 31, 2005 at 11:31:58 +0800, Tobias Brox [EMAIL PROTECTED] wrote: [Tobias Brox] test=# set enable_seqscan=off; [Bruno Wolff III - Mon at 10:16:53PM -0500] It isn't surprising that an index wasn't used since a sequential scan is going to be faster in your test case

Re: [PERFORM] BUG #1697: Select getting slower on continously updating data

2005-06-03 Thread Bruno Wolff III
On Fri, Jun 03, 2005 at 00:09:00 -0700, Bahadur Singh [EMAIL PROTECTED] wrote: Many thanks for this tip ! But is this good idea to analyse/vacuuming the database tables while updates are taking place.. Since, I update continuously say (100,000 ) times or more the same data set. This is

Re: [PERFORM] full outer performance problem

2005-06-08 Thread Bruno Wolff III
On Wed, Jun 08, 2005 at 11:37:40 +0200, Kim Bisgaard [EMAIL PROTECTED] wrote: Hi, I'm having problems with the query optimizer and FULL OUTER JOIN on PostgreSQL 7.4. I cannot get it to use my indexes with full outer joins. I might be naive, but I think that it should be possible? I

Re: [PERFORM] Help with rewriting query

2005-06-08 Thread Bruno Wolff III
On Wed, Jun 08, 2005 at 15:48:27 -0700, Junaili Lie [EMAIL PROTECTED] wrote: Hi, The suggested query below took forever when I tried it. In addition, as suggested by Tobias, I also tried to create index on food(p_id, id), but still no goal (same query plan). Here is the explain: TEST1=#

Re: [PERFORM] Index ot being used

2005-06-12 Thread Bruno Wolff III
On Sun, Jun 12, 2005 at 10:12:27 -0400, Madison Kelly [EMAIL PROTECTED] wrote: Indexes: file_info_7_display_idx btree (file_type, file_parent_dir, file_name) Here is my full query: tle-bu= EXPLAIN ANALYZE SELECT file_name, file_parent_dir, file_type FROM file_info_7 WHERE

Re: [PERFORM] Index ot being used

2005-06-12 Thread Bruno Wolff III
On Sun, Jun 12, 2005 at 18:52:05 -0400, Madison Kelly [EMAIL PROTECTED] wrote: After sending that email I kept plucking away and in the course of doing so decided that I didn't need to return the 'file_type' column. Other than that, it would see my query now matches what you two have

Re: [PERFORM] Index ot being used

2005-06-12 Thread Bruno Wolff III
On Sun, Jun 12, 2005 at 22:00:01 -0500, Bruno Wolff III [EMAIL PROTECTED] wrote: On Sun, Jun 12, 2005 at 18:52:05 -0400, Madison Kelly [EMAIL PROTECTED] wrote: After sending that email I kept plucking away and in the course of doing so decided that I didn't need to return

Re: [PERFORM] Index ot being used

2005-06-12 Thread Bruno Wolff III
On Sun, Jun 12, 2005 at 23:42:05 -0400, Madison Kelly [EMAIL PROTECTED] wrote: As you probably saw in my last reply, I went back to the old index and tried the query you and Tom Lane recommended. Should this not have caught the index? Probably, but there might be some other reason the

Re: [PERFORM] Index ot being used

2005-06-13 Thread Bruno Wolff III
On Mon, Jun 13, 2005 at 00:29:08 -0400, Madison Kelly [EMAIL PROTECTED] wrote: Bruno Wolff III wrote: On Sun, Jun 12, 2005 at 23:42:05 -0400, Madison Kelly [EMAIL PROTECTED] wrote: As you probably saw in my last reply, I went back to the old index and tried the query you and Tom Lane

Re: [PERFORM] Index ot being used

2005-06-13 Thread Bruno Wolff III
On Mon, Jun 13, 2005 at 09:51:57 -0500, John A Meinel [EMAIL PROTECTED] wrote: I don't know if there are specific reasons why not, other than just not being implemented yet. It might be tricky to get it correct (for instance, how do you know which columns can be added, which ones will be

Re: [PERFORM] Index ot being used

2005-06-13 Thread Bruno Wolff III
On Mon, Jun 13, 2005 at 12:22:14 -0400, Tom Lane [EMAIL PROTECTED] wrote: I don't think the use-case has been shown that justifies doing this much work to ignore useless ORDER BY clauses. The examples that have come up in the past all suggest ignoring index columns not the other way

Re: [PERFORM] Index ot being used

2005-06-13 Thread Bruno Wolff III
On Mon, Jun 13, 2005 at 11:46:46 -0500, Kevin Grittner [EMAIL PROTECTED] wrote: I agree that ignoring useless columns in an ORDER BY clause is less important than ignoring index columns where the value is fixed. There is one use case for ignoring useless ORDER BY columns that leaps to mind,

Re: [PERFORM] Index ot being used

2005-06-13 Thread Bruno Wolff III
On Mon, Jun 13, 2005 at 15:05:00 -0400, Madison Kelly [EMAIL PROTECTED] wrote: Wow! With the sequence scan off my query took less than 2sec. When I turned it back on the time jumped back up to just under 14sec. tle-bu= set enable_seqscan = off; SET tle-bu= EXPLAIN ANALYZE SELECT

Re: [PERFORM] Needed: Simplified guide to optimal memory configuration

2005-06-15 Thread Bruno Wolff III
On Wed, Jun 15, 2005 at 02:06:27 -0700, Todd Landfried [EMAIL PROTECTED] wrote: What's the problem? The sucker gets s-l-o-w on relatively simple queries. For example, simply listing all of the users online at one time takes 30-45 seconds if we're talking about 800 users. We've

Re: [PERFORM] Needed: Simplified guide to optimal memory configuration

2005-06-16 Thread Bruno Wolff III
On Thu, Jun 16, 2005 at 07:46:45 -0700, Todd Landfried [EMAIL PROTECTED] wrote: Yes, it is 7.2. Why? because an older version of our software runs on RH7.3 and that was the latest supported release of Postgresql for RH7.3 (that we can find). We're currently ported to 8, but we still

Re: [PERFORM] parameterized LIKE does not use index

2005-06-23 Thread Bruno Wolff III
On Thu, Jun 23, 2005 at 10:33:18 +0200, Kurt De Grave [EMAIL PROTECTED] wrote: Now it's tempting to dream of some mechanism that could make the database consider replanning the query automatically once it knows the parameter, or choose from a set of plans depending on the parameter. In

Re: [PERFORM] tricky query

2005-06-28 Thread Bruno Wolff III
On Tue, Jun 28, 2005 at 10:21:16 -0400, Merlin Moncure [EMAIL PROTECTED] wrote: I need a fast way (sql only preferred) to solve the following problem: I need the smallest integer that is greater than zero that is not in the column of a table. In other words, if an 'id' column has values

Re: [PERFORM] tricky query

2005-06-28 Thread Bruno Wolff III
On Tue, Jun 28, 2005 at 12:02:09 -0400, Merlin Moncure [EMAIL PROTECTED] wrote: Confirmed. Hats off to you, the above some really wicked querying. IIRC I posted the same question several months ago with no response and had given up on it. I think your solution (smallest X1 not in X) is a

Re: [PERFORM] Why the planner is not using the INDEX .

2005-07-05 Thread Bruno Wolff III
On Mon, Jul 04, 2005 at 20:29:50 -0400, David Gagnon [EMAIL PROTECTED] wrote: Thanks .. I miss that FK don't create indexed ... since Primary key implicitly does ... I'm a bit surprised of that behavior thought, since it means that if we delete a row from table A all tables (B,C,D) with

Re: [PERFORM] join and query planner

2005-07-12 Thread Bruno Wolff III
On Wed, Jul 06, 2005 at 18:54:02 -0300, Dario Pudlo [EMAIL PROTECTED] wrote: (first at all, sorry for my english) Hi. - Does left join restrict the order in which the planner must join tables? I've read about join, but i'm not sure about left join... The left join operator is not

Re: [PERFORM] slow joining very large table to smaller ones

2005-07-15 Thread Bruno Wolff III
On Thu, Jul 14, 2005 at 16:29:58 -0600, Dan Harris [EMAIL PROTECTED] wrote: Ok, I tried this one. My ssh keeps getting cut off by a router somewhere between me and the server due to inactivity timeouts, so all I know is that both the select and explain analyze are taking over an

Re: [PERFORM] BUG #1797: Problem using Limit in a function, seqscan

2005-07-29 Thread Bruno Wolff III
On Fri, Jul 29, 2005 at 13:52:45 +0100, Magno Leite [EMAIL PROTECTED] wrote: Description:Problem using Limit in a function, seqscan I looked for about this problem in BUG REPORT but I can't find. This is my problem, when I try to use limit in a function, the Postgre doesn't use my

Re: [PERFORM] Caching by Postgres

2005-08-23 Thread Bruno Wolff III
On Tue, Aug 23, 2005 at 10:10:45 -0700, gokulnathbabu manoharan [EMAIL PROTECTED] wrote: Hi all, I like to know the caching policies of Postgresql. What parameter in the postgresql.conf affects the cache size used by the Postgresql? As far as I have searched my knowledge of the

Re: [PERFORM] How does the planner execute unions?

2005-08-26 Thread Bruno Wolff III
On Fri, Aug 26, 2005 at 16:14:18 -0400, Chris Hoover [EMAIL PROTECTED] wrote: Hopefully a quick question. In 7.3.4, how does the planner execute a query with union alls in it? Does it execute the unions serially, or does it launch a thread for each union (or maybe something else

Re: [PERFORM] Need indexes on empty tables for good performance ?

2005-08-27 Thread Bruno Wolff III
On Tue, Aug 23, 2005 at 13:41:32 +1000, Lenard, Rohan (Rohan) [EMAIL PROTECTED] wrote: I've read that indexes aren't used for COUNT(*) and I've noticed (7.3.x) with EXPLAIN that indexes never seem to be used on empty tables - is there any reason to have indexes on empty tables, or will

Re: [PERFORM] RAID Configuration Sugestion

2005-08-30 Thread Bruno Wolff III
On Tue, Aug 30, 2005 at 09:37:17 -0300, Alvaro Nunes Melo [EMAIL PROTECTED] wrote: The server is a Dual Xeon 3.0 with 2 GB RAM and two SCSI disks. Our main doubt is what is the best configuration for the disks. We are thinking about use them in a RAID-0 array. Is this the best option?

Re: [PERFORM] RAID Configuration Sugestion

2005-08-30 Thread Bruno Wolff III
Please keep replies copied to the list so that others may contribute to and learn from the discussion. On Tue, Aug 30, 2005 at 10:15:13 -0300, Alvaro Nunes Melo [EMAIL PROTECTED] wrote: Hello Bruno, Bruno Wolff III wrote: On Tue, Aug 30, 2005 at 09:37:17 -0300, Alvaro Nunes Melo [EMAIL

Re: [PERFORM] Slow update

2005-09-12 Thread Bruno Wolff III
On Mon, Sep 12, 2005 at 10:14:25 +0100, Hilary Forbes [EMAIL PROTECTED] wrote: Hello everyone I must be doing something very wrong here so help please! I have two tables tableA has 300,000 recs tableB has 20,000 recs I need to set the value of a field in table A to a value in table B

  1   2   >