Re: [PERFORM] PostgreSQL vs. MySQL

2003-07-04 Thread Stephan Szabo
On Fri, 4 Jul 2003, Brian Tarbox wrote: I don't think Brian has any interest in being helped. I suspect he'd made up his mind already. With all due respect Tom, I don't think I'm the one demonstrating a closed mind. Rather than trying to figure out whats going on in my head, how about

Re: [PERFORM] Efficiency of timestamps

2003-07-08 Thread Stephan Szabo
On Tue, 8 Jul 2003, Martin Foster wrote: As I keep looking through code to see where I can make things more efficient, I noticed that in some cases timestamps seem horribly inefficient. This leads to very long run times for certain queries. Here is an example: -- USING TIMESTAMPS TO

Re: [PERFORM] Efficiency of timestamps

2003-07-08 Thread Stephan Szabo
On Tue, 8 Jul 2003, Martin Foster wrote: Stephan Szabo wrote: I think you might get better results with some kind of multi-column index. It's using the index to avoid a sort it looks like, but it's not helping to find the conditions. I can't remember the correct ordering, but maybe

Re: [PERFORM] Yet another slow join query..

2003-07-18 Thread Stephan Szabo
On Fri, 18 Jul 2003, Rajesh Kumar Mallah wrote: Hi All, data_bank.updated_profiles and public.city_master are small tables with 21790 and 49303 records repectively. both have indexes on the join column. in first one on (city,source) and in second one on (city) The query below does not

Re: [PERFORM] Views With Unions

2003-07-31 Thread Stephan Szabo
On Thu, 31 Jul 2003, Christopher Browne wrote: select * from log_table where request_time between 'june 11 2003' and 'june 12 2003'; returns a plan: Subquery Scan log_table (cost=0.00..10950.26 rows=177126 width=314) - Append

Re: [PERFORM] Odd performance results - more info

2003-08-14 Thread Stephan Szabo
On Tue, 5 Aug 2003, Medora Schauer wrote: I hope this piques someones curiosity. I'd really like to know what is going on here... I think you're getting caught by the typing of constants preventing index scans. UPDATE shot_record SET trace_count = %d \ WHERE

Re: [PERFORM] Query too slow

2003-08-25 Thread Stephan Szabo
On Mon, 25 Aug 2003, Rhaoni Chiu Pereira wrote: Hi List, As I said before, I'm not a DBA yet , but I'm learning ... and I already have a PostgreSQL running, so I have to ask some help... I got a SQL as folows : ... Looking at the explain: It's choosing lots of nested loops

Re: [PERFORM] Query too slow

2003-08-26 Thread Stephan Szabo
On Tue, 26 Aug 2003, Ang Chin Han wrote: Stephan Szabo wrote: Looking at the explain: Veering aside a bit, since we usually pinpoint performance problems by looking at EXPLAIN ANALYZE's differences between the planner's estimation and actual execution's stats, what's involved in parsing

Re: [PERFORM] Simple queries take forever to run

2003-08-28 Thread Stephan Szabo
On Thu, 28 Aug 2003, Michael Guerin wrote: Stephan Szabo wrote: On Wed, 27 Aug 2003, Michael Guerin wrote: I'm running into some performance problems trying to execute simple queries. postgresql version 7.3.3 .conf params changed from defaults. shared_buffers = 64000 sort_mem

Re: [PERFORM] Simple queries take forever to run

2003-08-28 Thread Stephan Szabo
On Thu, 28 Aug 2003, Michael Guerin wrote: Stephan Szabo wrote: On Thu, 28 Aug 2003, Michael Guerin wrote: Stephan Szabo wrote: On Wed, 27 Aug 2003, Michael Guerin wrote: I'm running into some performance problems trying to execute simple queries. postgresql

Re: [PERFORM] performance of foreign key constraints

2003-08-28 Thread Stephan Szabo
On Thu, 28 Aug 2003, teknokrat wrote: Stephan Szabo wrote: On Thu, 28 Aug 2003, teknokrat wrote: I have a table that has about 20 foreign key constraints on it. I think this is a bit excessive and am considering removing them ( they are all related to the same table and I don't think

Re: [PERFORM] bad estimates

2003-08-29 Thread Stephan Szabo
On Fri, 29 Aug 2003, Ken Geis wrote: Ken Geis wrote: I went through the code (7.4 beta2) that estimates the cost of an index scan path. What I need to be sure of is that when running a query in pgsql that uses only the columns that are in an index, the underlying table need not be

Re: [PERFORM] Inconsistent performance

2003-09-15 Thread Stephan Szabo
On Mon, 15 Sep 2003, Joseph Bove wrote: I am working with a decent sized database on an extremely powerful machine. The specs follow: OS: RedHat Linux 9.0 PG Version 7.3 Memory 1 gig CPU Quad Processor -

Re: [PERFORM] Inconsistent performance

2003-09-15 Thread Stephan Szabo
On Mon, 15 Sep 2003, Joseph Bove wrote: Stephan, Actually, it's inconsistent with the exact same command. I've now replicated the problem by doing the following command: select count (*) from table; The table in question has 88899 rows. The response time is anywhere from 1 second to 12

Re: [PERFORM] Index/Foreign Key Question

2003-10-10 Thread Stephan Szabo
On Fri, 10 Oct 2003, David Busby wrote: - Original Message - From: Ron Johnson On Fri, 2003-10-10 at 16:04, David Busby wrote: List, I'm creating this multi company POS database. My inventory table looks like (all items are unique):

Re: [PERFORM] Another weird one with an UPDATE

2003-10-12 Thread Stephan Szabo
On Sun, 12 Oct 2003, David Griffiths wrote: [snip] I think you want something like: UPDATE user_account SET last_name = 'abc' WHERE EXISTS (SELECT 1 FROM commercial_entity ce, commercial_service cs WHERE user_account.user_account_id = ce.user_account_id AND ce.commercial_entity_id

Re: [PERFORM] Adding foreign key performance

2003-10-28 Thread Stephan Szabo
On Tue, 28 Oct 2003, Jeff wrote: On Tue, 28 Oct 2003 09:16:45 -0500 Jeff [EMAIL PROTECTED] wrote: 7.3.4: 328912ms [cpu pegged] 7.4b4: 298383ms [cpu pegged] Just loaded up delicious 7.4b5 and wow... sort_mem 8192: 137038ms [lots of tmp file activity] sort_mem 256000: 83109ms Hmm,

Re: [PERFORM] TEXT column and indexing

2003-11-19 Thread Stephan Szabo
On Wed, 19 Nov 2003, Ian Barwick wrote: I have this table: db= \d object_property_value Table db.object_property_value Column | Type | Modifiers ---++

Re: [PERFORM] Update performance ... is 200,000 updates per hour

2003-12-02 Thread Stephan Szabo
On Tue, 2 Dec 2003, Erik Norvelle wrote: ** My question has to do with whether or not I am getting maximal speed out of PostgreSQL, or whether I need to perform further optimizations. I am currently getting about 200,000 updates per hour, and updating the entire 10 million rows thus requires

Re: [PERFORM] [ADMIN] Index not used. WHY?

2003-12-05 Thread Stephan Szabo
On Fri, 5 Dec 2003, Andrei Bintintan wrote: There are around 700 rows in this table. If I set enable_seqscan=off then the index is used and I also used Vacuum Analyze recently. I find it strange because the number of values of id_user and id_modull are somehow in the same distribution and

Re: [PERFORM]

2003-12-10 Thread Stephan Szabo
On Wed, 10 Dec 2003 [EMAIL PROTECTED] wrote: I have some problems on performance using postgresql v. 7.3.2 running on Linux RedHat 9. An update involving several rows (about 50) on a table having 280 tuples takes in the order of 6 minutes. It is more than it takes on other plataforms

Re: [PERFORM] [GENERAL] update slows down in pl/pgsql function

2003-12-16 Thread Stephan Szabo
On Tue, 16 Dec 2003, Jenny Zhang wrote: I have stored procedure written in pl/pgsql which takes about 13 seconds to finish. I was able to identify that the slowness is caused by one update SQL: UPDATE shopping_cart SET sc_sub_total=sc_subtotal, sc_date=now() WHERE sc_id=sc_id; Umm, is

Re: [PERFORM] Nested loop performance

2003-12-16 Thread Stephan Szabo
On Tue, 16 Dec 2003, Nick Fankhauser wrote: Is there a more efficient means than a nested loop to handle such a join? Would a different method be chosen if there was exactly one row in actor_summary for every row in actor? As a question, what does explain analyze give you if you set

Re: [PERFORM] is it possible to get the optimizer to use indexes

2003-12-18 Thread Stephan Szabo
On Thu, 18 Dec 2003, Dave Cramer wrote: after vacuum verbose analyze, I still get explain select * from isppm where item_upc_cd like '06038301234'; QUERY PLAN --- Seq Scan on isppm

Re: [PERFORM] deferred foreign keys

2004-01-05 Thread Stephan Szabo
On Mon, 5 Jan 2004, Bruno Wolff III wrote: 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

Re: [PERFORM] deferred foreign keys

2004-01-05 Thread Stephan Szabo
On Mon, 5 Jan 2004, Vivek Khera wrote: On Jan 5, 2004, at 1:57 PM, Stephan Szabo wrote: But, if he's updating the fk table but not the keyed column, it should no longer be doing the check and grabbing the locks. If he's seeing it grab the row locks still a full test case would

Re: [PERFORM] deferred foreign keys

2004-01-05 Thread Stephan Szabo
On Mon, 5 Jan 2004, Rod Taylor wrote: On Mon, 2004-01-05 at 14:48, Stephan Szabo wrote: On Mon, 5 Jan 2004, Vivek Khera wrote: On Jan 5, 2004, at 1:57 PM, Stephan Szabo wrote: But, if he's updating the fk table but not the keyed column, it should no longer be doing

Re: [PERFORM] Slow query problem

2004-01-09 Thread Stephan Szabo
On Fri, 9 Jan 2004, Richard Huxton wrote: On Friday 09 January 2004 08:57, Dennis Björklund wrote: On Fri, 9 Jan 2004, Richard Huxton wrote: select invheadref, invprodref, sum(units) from invtran group by invheadref, invprodref For the above query, shouldn't you have one

Re: [PERFORM] subquery and table join, index not use for table

2004-01-16 Thread Stephan Szabo
On Wed, 14 Jan 2004, CoL wrote: [plan1] - Seq Scan on menutable b (cost=0.00..13.01 rows=38 width=22) (actual time=0.02..0.38 rows=38 loops=1) [plan2] - Index Scan using menutable_pkey on menutable b (cost=0.00..29.36 rows=38 width=22) (actual time=0.02..0.12 rows=38 loops=1)

Re: [PERFORM] Join optimisation Quandry

2004-01-16 Thread Stephan Szabo
On Sat, 17 Jan 2004, Ceri Storey wrote: On Fri, Jan 16, 2004 at 10:17:50AM -0800, Stephan Szabo wrote: As a starting point, we're likely to need the exact query, explain analyze output for the query and version information. Okay, from top to bottom: SELECT p1.chan_name, p1.prog_start

Re: [PERFORM] Trigger question

2004-01-21 Thread Stephan Szabo
On Wed, 21 Jan 2004, Harald Fuchs wrote: In article [EMAIL PROTECTED], Richard Huxton [EMAIL PROTECTED] writes: On Tuesday 20 January 2004 16:42, Tom Lane wrote: Harald Fuchs [EMAIL PROTECTED] writes: Why? If the underlying table has a primary key, finding corresponding pairs is

Re: [PERFORM] query optimization differs between view and explicit

2004-01-29 Thread Stephan Szabo
On Thu, 29 Jan 2004, Reece Hart wrote: I have a large query which I would like to place in a view. The explicit query is sufficiently fast, but the same query as a view is much slower and uses a different plan. I would appreciate an explanation of why this is, and, more importantly

Re: [PERFORM] Increase performance of a UNION query that thakes

2004-02-06 Thread Stephan Szabo
On Fri, 6 Feb 2004, Bruno BAGUETTE wrote: I was thinking that a index on lower(substr(l_name, 1, 1)) and another index on lower(substr(org_name, 1, 1)) should gives better performances. When I've to create theses two indexes, it seems like this is not allowed : levure= CREATE INDEX

Re: RE : [PERFORM] Increase performance of a UNION query that thakes

2004-02-06 Thread Stephan Szabo
On Fri, 6 Feb 2004, Bruno BAGUETTE wrote: In addition to what Tom said, the row estimates look suspiciously default. You mention vacuuming, but do you ever analyze the tables? I run VACUUM FULL ANALYZE with the postgres user on all the PostgreSQL databases on the server, twice a day,

Re: [PERFORM] Forcing filter/join order?

2004-02-18 Thread Stephan Szabo
On Wed, 18 Feb 2004, Josh Berkus wrote: The planner can't, or doesn't want to, use an index on (event_date, (event_date + duration)) where the first column is an ascending sort and the second a descending sort.So I've coded a workaround that's quite inelegant but does get the correct

Re: [PERFORM] Forcing filter/join order?

2004-02-18 Thread Stephan Szabo
On Wed, 18 Feb 2004, Josh Berkus wrote: Stephan, Can you give more information? I know that I'm not exactly certain what the situation is from the above and the original query/explain piece. Believe me, if I posted the query it wouldn't help.Heck, I'd have trouble following it

Re: [PERFORM] cacheable stored functions?

2004-02-20 Thread Stephan Szabo
On Fri, 20 Feb 2004, Bill Moran wrote: I'm converting a SQL application to PostgreSQL. The majority of the logic in this application is in the stored functions in the database. Somewhere, I saw a reference to WITH (iscachable) for stored functions, looking again, I'm unable to find any

Re: [PERFORM] Sorting when LEFT JOINING to 2 same tables, even

2004-03-11 Thread Stephan Szabo
On Thu, 11 Mar 2004, Octavio Alvarez wrote: Hello to everybody. I ask your help for a severe problem when doing a query that LEFT JOINs one table to another ON a field, and then LEFT JOINs again to another instance of a table ON another field which stores the same entity, but with

Re: [PERFORM] PostgreSQL Disk Usage and Page Size

2004-03-17 Thread Stephan Szabo
On Wed, 17 Mar 2004, Seum-Lim Gan wrote: we have a question about the pagesize in PostgreSQL: Using different pagesizes: 4K, 8K, 16K, 32K, when we store different record sizes such as in the following example: CREATE TABLE TEST_1 ( F1 VARCHAR(10), F2 VARCHAR(5) ); CREATE TABLE TEST_2 (

Re: [PERFORM] PostgreSQL Disk Usage and Page Size

2004-03-18 Thread Stephan Szabo
On Thu, 18 Mar 2004, Saleh, Amgad H (Amgad) wrote: Stephan / Stephen We know about the overhead and do understand the math you've provided. This is not the question we're asking. We've just provided the table definitions as examples. The real question was, even with the 52 56 (assuming

Re: [PERFORM] PostgreSQL Disk Usage and Page Size

2004-03-18 Thread Stephan Szabo
On Thu, 18 Mar 2004, Saleh, Amgad H (Amgad) wrote: Stephan: In each table we're storing the max. string length. For example: for TEST_1, we're storing 'abcdefghjk' and 'lmnop' for TEST_2, we're storing 'abcdefghjk' and 'lmnopqrstu' for TEST_3, we're storing 'abcdefghjk' and

Re: [PERFORM] two seperate queries run faster than queries ORed

2004-03-22 Thread Stephan Szabo
On Mon, 22 Mar 2004, Joseph Shraibman wrote: Tom Lane wrote: Joseph Shraibman [EMAIL PROTECTED] writes: No, pkey is not the primary key in this case. The number of entries in u that have pkey 260 and not boolfield is 344706. ... and every one of those rows *must* be included in the

Re: [PERFORM] Optimisation of INTERSECT expressions

2004-03-23 Thread Stephan Szabo
On Tue, 23 Mar 2004, Phil Endecott wrote: Dear PostgresQL Experts, I am trying to get to the bottom of some efficiency problems and hope that you can help. The difficulty seems to be with INTERSECT expressions. I have a query of the form select A from T where C1 intersect select A

Re: [PERFORM] Optimisation of INTERSECT expressions

2004-03-23 Thread Stephan Szabo
On Tue, 23 Mar 2004, Stephan Szabo wrote: On Tue, 23 Mar 2004, Phil Endecott wrote: Dear PostgresQL Experts, I am trying to get to the bottom of some efficiency problems and hope that you can help. The difficulty seems to be with INTERSECT expressions. I have a query of the form

Re: [PERFORM] column size too large, is this a bug?

2004-03-25 Thread Stephan Szabo
On Thu, 25 Mar 2004, Qing Zhao wrote: select _level_ as l, nextval('seq_pk_bom_detail') as bom_detail, prior nextval('seq_pk_bom_detail') as parent_subassembly, parent_part_number, customer_part_number, /* mfr_name, mfr_part, description,*/ commodity,

Re: [PERFORM] atrocious update performance

2004-04-05 Thread Stephan Szabo
On Mon, 5 Apr 2004, Rosser Schwarz wrote: while you weren't looking, Kevin Barnard wrote: Have you added indexes for the custid column for tables account.acct accunt.orgacct and note? They were indexed in the original case, yes. There was no need to index them in today's test case, as

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

2004-04-12 Thread Stephan Szabo
On Mon, 12 Apr 2004, Jeremy Dunn wrote: explain analyze select count(*) from xxx where cid=6223341; Aggregate (cost=74384.19..74384.19 rows=1 width=0) (actual time=11614.89..11614.89 rows=1 loops=1) - Index Scan using xxx_cid on emailrcpts (cost=0.00..74329.26 rows=21974

Re: [PERFORM] slow seqscan

2004-04-21 Thread Stephan Szabo
On Wed, 21 Apr 2004, Edoardo Ceccarelli wrote: What happens if you go: CREATE INDEX annuncio400_rubric_testo_idx ON annuncio400(rubric, LOWER(testo)); or even just: CREATE INDEX annuncio400_rubric_testo_idx ON annuncio400(LOWER(testo)); I wasn't able to make this 2 field

Re: R: [PERFORM] Query plan on identical tables differs . Why ?

2004-05-13 Thread Stephan Szabo
On Thu, 13 May 2004, Fabio Panizzutti wrote: I don't understand why the planner chose a different query plan on identical tables with same indexes . Because it's more than table structure that affects the choice made by the planner. In addition the statistics about the values that are there

Re: [PERFORM] index usage

2004-04-26 Thread Stephan Szabo
On Fri, 23 Apr 2004 [EMAIL PROTECTED] wrote: I have a query which I think should be using an index all of the time but postgres only uses the index part of the time. The index (ticket_crm_map_crm_id_suppid) has the where clause column (crm_id) listed first followed by the selected column

Re: [PERFORM] Slow response of PostgreSQL

2004-06-03 Thread Stephan Szabo
On Tue, 17 Feb 2004, Saleem Burhani Baloch wrote: select count(*), sum(vl_ex_stax) , sum(qty) , unit from inv_detail group by unit; on both databases. PostgreSQL Machine ** P-III 600Mhz (Dell Precision 220) 256 MB Ram (RD Ram) 40 GB Baracuda Ext2 File System. RedHat 7.2

Re: [PERFORM] *very* inefficient choice made by the planner (regarding

2004-06-10 Thread Stephan Szabo
On Thu, 10 Jun 2004, Frank van Vugt wrote: Could anybody explain why the planner is doing what it is doing? What could I do to make it easier to choose a better plan? You might try raising sort_mem to see if it chooses a better plan. I think it may be guessing that the hash won't fit and

Re: [PERFORM] *very* inefficient choice made by the planner (regarding

2004-06-10 Thread Stephan Szabo
On Thu, 10 Jun 2004, Jean-Luc Lachance wrote: I agree, but it should be a simple rewrite. No? It's NULLs inside the subselect that are the issue. select 1 in (select a from foo) select exists ( select 1 from foo where a=1) If foo.a contains a row with NULL but no rows containing a 1, the

Re: [PERFORM] *very* inefficient choice made by the planner (regarding

2004-06-18 Thread Stephan Szabo
On Fri, 18 Jun 2004, [iso-8859-1] SZUCS Gábor wrote: Dear Gurus, - Original Message - From: Stephan Szabo [EMAIL PROTECTED] Sent: Thursday, June 10, 2004 7:14 PM On Thu, 10 Jun 2004, Stephan Szabo wrote: On Thu, 10 Jun 2004, Jean-Luc Lachance wrote: I agree

Re: [PERFORM] query plan wierdness?

2004-07-07 Thread Stephan Szabo
On Wed, 7 Jul 2004, Joel McGraw wrote: However, this query performs a sequence scan on the table, ignoring the call_idx13 index (the only difference is the addition of the aspid field in the order by clause): elon2=# explain analyse select * from call where aspid='123C' and OpenedDateTime

Re: [PERFORM] Unbearably slow cascading deletes

2004-07-20 Thread Stephan Szabo
On Tue, 20 Jul 2004 [EMAIL PROTECTED] wrote: I have (among other things) a parent table with 200 records and a child table with 20MM or more. I set up referential integrity on the FK with ON DELETE CASCADE. It appears that when a DELETE is done on the parent table, the child table deletion

Re: [PERFORM] Unbearably slow cascading deletes

2004-07-20 Thread Stephan Szabo
On Tue, 20 Jul 2004, Stephan Szabo wrote: On Tue, 20 Jul 2004 [EMAIL PROTECTED] wrote: I have (among other things) a parent table with 200 records and a child table with 20MM or more. I set up referential integrity on the FK with ON DELETE CASCADE. It appears that when a DELETE

Re: [PERFORM] Timestamp-based indexing

2004-07-26 Thread Stephan Szabo
On Mon, 26 Jul 2004, Harmon S. Nine wrote: However, we can't get the planner to do an timestamp-based index scan. Anyone know what to do? I'd wonder if the type conversion is causing you problems. CURRENT_TIMESTAMP - INTERVAL '10 minutes' is a timestamp with time zone while the column is

Re: [PERFORM] best way to fetch next/prev record based on index

2004-07-27 Thread Stephan Szabo
On Tue, 27 Jul 2004, Merlin Moncure wrote: Greg Stark wrote: do it for multi-column keys. It seems it would be nice if some syntax similar to (a,b,c) (a1,b1,c1) worked for this. Hum. It would seem my intuition matches the SQL92 spec and Postgres gets this wrong. [...] Even

Re: [PERFORM] Optimizer refuses to hash join

2004-07-29 Thread Stephan Szabo
On Tue, 27 Jul 2004, Stan Bielski wrote: I having a great deal of difficulty getting postgres to do a hash join. Even if I disable nestloop and mergejoin in postgres.conf, the optimizer still refuses to select hash join. This behavior is killing my performance. Postgres version is 7.3.2 and

Re: [PERFORM] NOT IN query takes forever

2004-08-03 Thread Stephan Szabo
On Tue, 3 Aug 2004, Marius Andreiana wrote: I just vacuum analyse'd the database. Trying to run this query: EXPLAIN ANALYSE select * FROM trans WHERE query_id NOT IN (select query_id FROM query) but it will remain like that forever (cancelled after 30 min). My postgresql.conf is the

Re: [PERFORM] seqscan instead of index scan

2004-08-30 Thread Stephan Szabo
On Mon, 30 Aug 2004, Martin Sarsale wrote: On Mon, 2004-08-30 at 15:06, Merlin Moncure wrote: create function is_somethingable (ctype, dtype) returns boolean as Thanks, but I would prefer a simpler solution. I would like to know why this uses a seqscan instead of an index scan: create

Re: [PERFORM] spurious function execution in prepared statements.

2004-09-30 Thread Stephan Szabo
On Thu, 30 Sep 2004, Merlin Moncure wrote: OK, I have a situation that might be a performance problem, a bug, or an unavoidable consequence of using prepared statements. The short version is that I am getting function executions for rows not returned in a result set when they are in a

Re: [PERFORM] create index with substr function

2004-10-20 Thread Stephan Szabo
On Thu, 21 Oct 2004, Ray wrote: Hi All, I have a table in my postgres: Table: doc Column |Type | Modifiers ---+-+--- doc_id | bigint | not null comp_grp_id | bigint

Re: [PERFORM] Simple machine-killing query!

2004-10-21 Thread Stephan Szabo
On Thu, 21 Oct 2004, Victor Ciurus wrote: Hi all, I'm writing this because I've reached the limit of my imagination and patience! So here is it... 2 tables: 1 containing 27 million variable lenght, alpha-numeric records (strings) in 1 (one) field. (10 - 145 char lenght per record) 1

Re: [PERFORM] Insertion puzzles

2004-11-13 Thread Stephan Szabo
On Sat, 13 Nov 2004, vivek singh wrote: I am new to this group and postgresql. I am working on a project which uses postgresql and project is time critical. We did all optimization in our project but postgresql seems to be a bottle-neck. To solve this we run the database operations in a

Re: [PERFORM] index use

2004-11-19 Thread Stephan Szabo
On Fri, 19 Nov 2004, Arshavir Grigorian wrote: Hi, I have a query that when run on similar tables in 2 different databases either uses the index on the column (primary key) in the where clause or does a full table scan. The structure of the tables is the same, except that the table where

Re: [PERFORM] lock problem

2004-12-04 Thread Stephan Szabo
On Sat, 4 Dec 2004, sarlav kumar wrote: Thanks for the information on replication tools!! Now, I have a question regarding locking tables and updating tables that have a relationship to the locked table. I opened up two pgsql windows logged in using same userid. Let's say I lock a table

Re: [PERFORM] index scan of whole table, can't see why

2005-01-20 Thread Stephan Szabo
On Wed, 19 Jan 2005, Dan Langille wrote: Hi folks, Running on 7.4.2, recently vacuum analysed the three tables in question. The query plan in question changes dramatically when a WHERE clause changes from ports.broken to ports.deprecated. I don't see why. Well, I do see why: a sequential

Re: [PERFORM] index scan of whole table, can't see why

2005-01-20 Thread Stephan Szabo
On Thu, 20 Jan 2005, Dan Langille wrote: On 20 Jan 2005 at 6:14, Stephan Szabo wrote: On Wed, 19 Jan 2005, Dan Langille wrote: Hi folks, Running on 7.4.2, recently vacuum analysed the three tables in question. The query plan in question changes dramatically when a WHERE

Re: [PERFORM] How to boost performance of ilike queries ?

2005-01-25 Thread Stephan Szabo
On Tue, 25 Jan 2005, Antony Paul wrote: Creating an index and using lower(column) does not change the explain plan estimates. It seems that it is not using index for like or ilike queries irrespective of whether it have a pattern matching character in it or not. (using PostgreSQL 7.3.3) I

Re: [PERFORM] String matching

2005-02-14 Thread Stephan Szabo
On Mon, 14 Feb 2005, Ivan Voras wrote: Stephan Szabo wrote: You can also create an index using a typename_pattern_ops operator class which should be usable even with other collations. Could you give me an example for this, or point me to the relevant documentation? Basically, you could

Re: [PERFORM] IS NULL vs IS NOT NULL

2005-02-25 Thread Stephan Szabo
On Fri, 25 Feb 2005, Vig, Sandor (G/FI-2) wrote: Hi, Another funny thing: I have a query which runs on (Linux) PostgreSQL 7.4.x under 10 sec. I tried to run it on (Windows) PostgreSQL 8.0 yesterday. It didn't finished at all! (I shoot it down after 10 minutes) I made various tests and I

Re: [PERFORM] adding 'limit' leads to very slow query

2005-03-07 Thread Stephan Szabo
On Mon, 7 Mar 2005, Michael McFarland wrote: I'm trying to understand why a particular query is slow, and it seems like the optimizer is choosing a strange plan. See this summary: * I have a large table, with an index on the primary key 'id' and on a field 'foo'. select count(*) from

Re: [PERFORM] adding 'limit' leads to very slow query

2005-03-14 Thread Stephan Szabo
On Wed, 9 Mar 2005, Michael McFarland wrote: I continue to be stumped by this. You are right that I should have listed the estimates provided by explain... basically for the select where bar = 41, it's estimating there will be 40,000 rows instead of 7, out of what's actuallly 5 million

Re: [PERFORM] Performance problem on delete from for 10k rows. May

2005-03-16 Thread Stephan Szabo
On Wed, 16 Mar 2005, David Gagnon wrote: Hi I rerun the example with the debug info turned on in postgresl. As you can see all dependent tables (that as foreign key on table IC) are emptied before the DELETE FROM IC statement is issued. For what I understand the performance problem seem to

Re: [PERFORM] Performance problem on delete from for 10k rows. May

2005-03-16 Thread Stephan Szabo
On Wed, 16 Mar 2005, David Gagnon wrote: Stephan Szabo wrote: On Wed, 16 Mar 2005, David Gagnon wrote: Hi I rerun the example with the debug info turned on in postgresl. As you can see all dependent tables (that as foreign key on table IC) are emptied before the DELETE FROM

Re: [PERFORM] clear function cache (WAS: SQL function inlining)

2005-03-24 Thread Stephan Szabo
On Thu, 24 Mar 2005, Enrico Weigelt wrote: * Alvaro Herrera [EMAIL PROTECTED] wrote: On Thu, Mar 24, 2005 at 02:32:48PM +0100, Enrico Weigelt wrote: BTW: is it possible to explicitly clear the cache for immutable functions ? What cache? There is no caching of function results.

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

2005-03-25 Thread Stephan Szabo
On Fri, 25 Mar 2005, Simon Riggs wrote: On Fri, 2005-03-25 at 10:17 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: I vote to make this an ERROR in 8.1 - I see little benefit in allowing this situation to continue. Other than spec compliance, you mean? SQL99 says

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

2005-03-25 Thread Stephan Szabo
On Fri, 25 Mar 2005, Simon Riggs wrote: On Fri, 2005-03-25 at 13:47 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: On Fri, 2005-03-25 at 10:17 -0500, Tom Lane wrote: Other than spec compliance, you mean? SQL99 says ... The declared type of each referencing column

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

2005-03-26 Thread Stephan Szabo
On Fri, 25 Mar 2005, Karim Nassar wrote: On Fri, 2005-03-25 at 15:10 +, Simon Riggs wrote: Karim: Did this happen? If not, can you drop and re-create and confirm that you get the WARNING? If not, we have problems. No. Nor do I think that I should. SERIAL is shortcut for INTEGER, no? I

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

2005-03-26 Thread Stephan Szabo
On Sat, 26 Mar 2005, Karim Nassar wrote: On Sat, 2005-03-26 at 07:55 -0800, Stephan Szabo wrote: That seems like it should be okay, hmm, what does something like: PREPARE test(int) AS SELECT 1 from measurement where id_int_sensor_meas_type = $1 FOR UPDATE; EXPLAIN ANALYZE EXECUTE TEST

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

2005-03-27 Thread Stephan Szabo
On Sat, 26 Mar 2005, Karim Nassar wrote: On Sat, 2005-03-26 at 15:18 -0800, Stephan Szabo wrote: On Sat, 26 Mar 2005, Karim Nassar wrote: On Sat, 2005-03-26 at 07:55 -0800, Stephan Szabo wrote: That seems like it should be okay, hmm, what does something like: PREPARE test(int

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

2005-03-28 Thread Stephan Szabo
On Mon, 28 Mar 2005, Karim A Nassar wrote: On Mon, 28 Mar 2005, Simon Riggs wrote: run the EXPLAIN after doing SET enable_seqscan = off The results I previously supplied were searching for a non-existent value, so I have provided output for both cases. *** *** Searching for

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

2005-03-29 Thread Stephan Szabo
On Tue, 29 Mar 2005, Simon Riggs wrote: On Tue, 2005-03-29 at 01:48 -0700, Karim A Nassar wrote: Each value has 1/13th of the table, which is too many rows per value to make an IndexScan an efficient way of deleting rows from the table. But, the original question was that the delete

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

2005-03-29 Thread Stephan Szabo
On Tue, 29 Mar 2005, Stephan Szabo wrote: On Tue, 29 Mar 2005, Simon Riggs wrote: On Tue, 2005-03-29 at 01:48 -0700, Karim A Nassar wrote: Each value has 1/13th of the table, which is too many rows per value to make an IndexScan an efficient way of deleting rows from the table

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

2005-03-29 Thread Stephan Szabo
On Tue, 29 Mar 2005, Simon Riggs wrote: On Tue, 2005-03-29 at 10:31 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: If action is NO ACTION or RESTRICT then we need to SELECT at most 1 row that matches the criteria which means we can use LIMIT 1 If action is

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

2005-03-29 Thread Stephan Szabo
On Tue, 29 Mar 2005, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: If there were some way to pass a limit into SPI_prepare that was treated similarly to a LIMIT clause for planning purposes but didn't actually change the output plan to only return that number of rows, we could

Re: [PERFORM] Foreign key slows down copy/insert

2005-04-14 Thread Stephan Szabo
On Thu, 14 Apr 2005, Richard van den Berg wrote: Hello Chris, Thanks for your answers. Christopher Kings-Lynne wrote: Deferring makes no difference to FK checking speed... But why then is the speed acceptable if I copy and then manually add the FK? Is the check done by the FK so much

Re: [PERFORM] Foreign key slows down copy/insert

2005-04-14 Thread Stephan Szabo
On Thu, 14 Apr 2005, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: ... At some point, if we can work out how to do all the semantics properly, it'd probably be possible to replace the insert type check with a per-statement check which would be somewhere in between. That requires

Re: FW: [PERFORM] speed of querry?

2005-04-18 Thread Stephan Szabo
On Mon, 18 Apr 2005, Joel Fradkin wrote: Another odd thing is when I tried turning off merge joins on the XP desktop It took 32 secs to run compared to the 6 secs it was taking. On the Linux (4proc box) it is now running in 3 secs with the mergejoins turned off. Unfortunately it takes over

Re: [PERFORM] index not used

2005-04-21 Thread Stephan Szabo
On Thu, 21 Apr 2005, Enrico Weigelt wrote: I'm doing a simple lookup in a small table by an unique id, and I'm wondering, why explains tells me seqscan is used instead the key. The table looks like: idbigint primary key, a varchar, b varchar, c

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

2005-07-05 Thread Stephan Szabo
On Mon, 4 Jul 2005, David Gagnon 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 FK pointing to this table (A) must be

Re: [PERFORM] join and query planner

2005-07-06 Thread Stephan Szabo
On Wed, 6 Jul 2005, Dario 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... Yes. Reordering the outer joins can change the results in some cases which would

Re: [PERFORM] Improving performance of a query

2005-09-06 Thread Stephan Szabo
On Tue, 6 Sep 2005, Merlin Moncure wrote: Carlos wrote: SELECT * FROM SSIRRA where (YEAR = 2004 and CUSTOMER = 04 and CODE = 00 and PART = 00) or (YEAR = 2004 and CUSTOMER = 04 and CODE 00) or (YEAR = 2004 and CUSTOMER 04) or (YEAR 2004) [snip] ah, the

Re: [PERFORM] How can this be?

2005-09-19 Thread Stephan Szabo
On Fri, 16 Sep 2005, Martin Nickel wrote: Hello all, Mostly Postgres makes sense to me. But now and then it does something that boggles my brain. Take the statements below. I have a table (agent) with 5300 rows. The primary key is agent_id. I can do SELECT agent_id FROM agent and it

Re: [PERFORM] Sort performance on large tables

2005-11-08 Thread Stephan Szabo
On Tue, 8 Nov 2005, Luke Lonergan wrote: SELECT tlid, min(ogc_fid) FROM completechain GROUP BY tlid ORDER BY tlid; Even with this, it was more than a magnitude faster than Postgresql. Which makes me think I have somehow misconfigured postgresql (see the relevant parts of

Re: [PERFORM] Sort performance on large tables

2005-11-08 Thread Stephan Szabo
On Tue, 8 Nov 2005, Luke Lonergan wrote: Stephan, On 11/8/05 9:38 AM, Stephan Szabo [EMAIL PROTECTED] wrote: Just as we find with a similar comparison (with a popular commercial, proprietary database :-) Though some might suggest you increase work_mem or other tuning suggestions

Re: [PERFORM] Outer Join performance in PostgreSQL

2005-11-09 Thread Stephan Szabo
On Wed, 9 Nov 2005, Ashok Agrawal wrote: I noticed outer join is very very slow in postgresql as compared to Oracle. SELECT a.dln_code, a.company_name, to_char(a.certificate_date,'DD-MON-'), to_char(a.certificate_type_id, '99'), COALESCE(b.certificate_type_description,'None') ,

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-27 Thread Stephan Szabo
On Sun, 27 Nov 2005, Luke Lonergan wrote: Has anyone done the math.on the original post? 5TB takes how long to scan once? If you want to wait less than a couple of days just for a seq scan, you'd better be in the multi-gb per second range. Err, I get about 31 megabytes/second to do 5TB in

  1   2   >