Re: [PERFORM] performance - triggers, row existence etc.
[EMAIL PROTECTED] wrote: Hello, I'm just in the middle of performance tunning of our database running on PostgreSQL, and I've several questions (I've searched the online docs, but without success). 1) When I first use the EXPLAIN ANALYZE command, the time is much larger than in case of subsequent invocations of EXPLAIN ANALYZE. I suppose the plan prepared during the first invocation is cached somewhere, but I'm not sure where and for how long. This is actually true for any command. If you just use \timing and not explain analyze, you will see that the first time is usually significantly longer than the rest. It's because the tables you are using are being cached in RAM (by the OS & by postgres). It's not a planning difference, it's a bulk data cache difference. When and how long is dependent on how much RAM you have, and how much of the database you are using. I suppose the execution plans are connection specific, but I'm not sure whether this holds for the sql queries inside the triggers too. I've done some testing but the things are somehow more difficult thanks to persistent links (the commands will be executed from PHP). Connection specific If you were doing PREPARE myquery AS SELECT ...; Then myquery would only exist for that connection. And cursors & temp tables are only for the given connection. But otherwise I don't think the connection matters. 2) Is there some (performance) difference between BEFORE and AFTER triggers? I believe there's no measurable difference. I don't know that there is a performance difference, but there is a semantic one. If you are trying to (potentially) prevent the row from being inserted you must do that BEFORE, since the row doesn't exist yet. If you are trying to update a foreign key reference to the new object, you must do that AFTER, so that the row exists to reference. 3) Vast majority of SQL commands inside the trigger checks whether there exists a row that suits some conditions (same IP, visitor ID etc.) Currently I do this by SELECT INTO tmp id FROM ... JOIN ... WHERE ... LIMIT 1 IF NOT FOUND THEN END IF; and so on. I believe this is fast and low-cost solution (compared to the COUNT(*) way I've used before), but is there some even better (faster) way to check row existence? SELECT ... WHERE EXISTS ...; I'm not sure what you are trying to do, but this makes a good joined command. SELECT what_I_want FROM table WHERE EXISTS (SELECT what_I_need FROM othertable); In general, though, SELECT WHERE LIMIT 1 is about as fast as you can get. Thanks t.v. John =:-> signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Compressing WAL
Jim C. Nasby wrote: > Maybe better for -hackers, but here it goes anyway... > > Has anyone looked at compressing WAL's before writing to disk? On a > system generating a lot of WAL it seems there might be some gains to be > had WAL data could be compressed before going to disk, since today's > machines are generally more I/O bound than CPU bound. And unlike the > base tables, you generally don't need to read the WAL, so you don't > really need to worry about not being able to quickly scan through the > data without decompressing it. I have never heard anyone talk about it, but it seems useful. I think compressing the page images written on first page modification since checkpoint would be a big win. Is this a TODO? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Functionscan estimates
People: (HACKERS: Please read this entire thread at http://archives.postgresql.org/pgsql-performance/2005-04/msg00179.php Sorry for crossing this over.) > > The larger point is that writing an estimator for an SRF is frequently a > > task about as difficult as writing the SRF itself > > True, although I think this doesn't necessarily kill the idea. If > writing an estimator for a given SRF is too difficult, the user is no > worse off than they are today. Hopefully there would be a fairly large > class of SRFs for which writing an estimator would be relatively simple, > and result in improved planner behavior. For that matter, even supplying an estimate constant would be a vast improvement over current functionality. I would suggest, in fact, that we allow the use of either a constant number, or an estimator function, in that column. Among other things, this would allow implementing the constant number right now and the use of an estimating function later, in case we can do the one but not the other for 8.1. To be more sophisticated about the estimator function, it could take a subset of the main functions arguments, based on $1 numbering, for example: CREATE FUNCTION some_func ( INT, TEXT, TEXT, INT, INT ) ... ALTER FUNCTION some_func WITH ESTIMATOR some_func_est( $4, $5 ) This would make writing estimators which would work for several functions easier. Estimators would be a special type of functions which would take any params and RETURN ESTIMATOR, which would be implicitly castable from some general numeric type (like INT or FLOAT). > > I don't foresee a whole lot of use of an estimator hook designed as > > proposed here. In particular, if the API is such that we can only > > use the estimator when all the function arguments are plan-time > > constants, it's not going to be very helpful. Actually, 95% of the time I use SRFs they are accepting constants and not row references. And I use a lot of SRFs. > > Yes :( One approach might be to break the function's domain into pieces > and have the estimator function calculate the estimated result set size > for each piece. So, given a trivial function like: > > foo(int): > if $1 < 10 then produce 100 rows > else produce 1 rows > > If the planner has encoded the distribution of input tuples to the > function as a histogram, it could invoke the SRF's estimator function > for the boundary values of each histogram bucket, and use that to get an > idea of the function's likely result set size at runtime. > > And yes, the idea as sketched is totally unworkable :) For one thing, > the difficulty of doing this grows rapidly as the number of arguments to > the function increases. But perhaps there is some variant of this idea > that might work... > > Another thought is that the estimator could provide information on the > cost of evaluating the function, the number of tuples produced by the > function, and even the distribution of those tuples. Another possibility would be to support default values for all estimator functions and have functions called in row context passed DEFAULT, thus leaving it up to the estimator writer to supply median values for context cases. Or to simply take the "first" values and use those. While any of these possibilites aren't ideal, they are an improvement over the current "flat 1000" estimate. As I said, even the ability to set a per-function flat constant estimate would be an improvement. > BTW, why is this on -performance? It should be on -hackers. 'cause I spend more time reading -performance, and I started the thread. Crossed over now. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] 4 way JOIN using aliases
Keith, > Thanks to all on the NOVICE list that gave me help I now have a query > running that returns the results I am after. :-) Now of course I want it > to run faster. Currently it clocks in at ~160ms. I have checked over the > indexes and I belive that the tables are indexed properly. The largest > table, tbl_item, only has 2000 rows. Is it possible to reduce the time of > this query further? Probably not, no.For a 7-way join including 2 LEFT JOINs on the unrestricted contents of all tables, 160ms is pretty darned good. If these tables were large, you'd be looking at a much longer estimation time. The only real way to speed it up would be to find a way to eliminate the left joins. Also, PostgreSQL 8.0 might optimize this query a little better. The only thing I can see to tweak is that the estimate on the number of rows in tbl_item is wrong; probably you need to ANALYZE tbl_item. But I doubt that will make a difference in execution time. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] 4 way JOIN using aliases
Keith Worthington wrote: -> Seq Scan on tbl_current (cost=0.00..1775.57 rows=76457 width=31) (actual time=22.870..25.024 rows=605 loops=1) This rowcount is way off -- have you run ANALYZE recently? -Neil ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PERFORM] Server crashing
My server is crashing on a delete statement. Here's the error message in the log file: LOCATION: ShutdownXLOG, xlog.c:3090 LOG: 0: database system is shut down LOCATION: ShutdownXLOG, xlog.c:3104 LOG: 0: database system was shut down at 2005-04-10 21:54:34 CDT LOCATION: StartupXLOG, xlog.c:2596 LOG: 0: checkpoint record is at C/665D45E0 LOCATION: StartupXLOG, xlog.c:2628 LOG: 0: redo record is at C/665D45E0; undo record is at 0/0; shutdown TRUE LOCATION: StartupXLOG, xlog.c:2653 LOG: 0: next transaction ID: 109177; next OID: 92547340 LOCATION: StartupXLOG, xlog.c:2656 LOG: 0: database system is ready LOCATION: StartupXLOG, xlog.c:2946 LOG: 0: recycled transaction log file "000C0063" LOCATION: MoveOfflineLogs, xlog.c:1656 LOG: 0: recycled transaction log file "000C0064" LOCATION: MoveOfflineLogs, xlog.c:1656 LOG: 0: recycled transaction log file "000C0065" LOCATION: MoveOfflineLogs, xlog.c:1656 WARNING: 25P01: there is no transaction in progress LOCATION: EndTransactionBlock, xact.c:1607 WARNING: 25P01: there is no transaction in progress LOCATION: EndTransactionBlock, xact.c:1607 ERROR: 42601: syntax error at end of input at character 77 LOCATION: yyerror, scan.l:565 WARNING: 25P01: there is no transaction in progress LOCATION: EndTransactionBlock, xact.c:1607 ERROR: 42601: syntax error at end of input at character 77 LOCATION: yyerror, scan.l:565 WARNING: 25P01: there is no transaction in progress LOCATION: EndTransactionBlock, xact.c:1607 WARNING: 25001: there is already a transaction in progress LOCATION: BeginTransactionBlock, xact.c:1545 ERROR: 42601: syntax error at end of input at character 77 LOCATION: yyerror, scan.l:565 WARNING: 25001: there is already a transaction in progress LOCATION: BeginTransactionBlock, xact.c:1545 ERROR: 42601: syntax error at end of input at character 77 LOCATION: yyerror, scan.l:565 LOG: 0: received fast shutdown request LOCATION: pmdie, postmaster.c:1736 LOG: 0: aborting any active transactions LOCATION: pmdie, postmaster.c:1743 FATAL: 57P01: terminating connection due to administrator command LOCATION: ProcessInterrupts, postgres.c:1955 FATAL: 57P01: terminating connection due to administrator command LOCATION: ProcessInterrupts, postgres.c:1955 FATAL: 57P01: terminating connection due to administrator command LOCATION: ProcessInterrupts, postgres.c:1955 FATAL: 57P01: terminating connection due to administrator command LOCATION: ProcessInterrupts, postgres.c:1955 LOG: 0: shutting down LOCATION: ShutdownXLOG, xlog.c:3090 LOG: 0: database system is shut down LOCATION: ShutdownXLOG, xlog.c:3104 I just turned off SQL command logging, stopped and started the process and now this command which worked just fine before is causing the DB to crash. I'm running Postgres 7.4.7 on Solaris 9 with PostGIS 0.9.1. The data I'm deleting is the parent table with many inherited child tables. Any ideas? -Don -- Donald Drake President Drake Consulting http://www.drakeconsult.com/ http://www.MailLaunder.com/ http://www.mobilemeridian.com/ 312-560-1574 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] Never ending delete story
Hi!!!
We are running PostgreSQL server version 7.4.6 on RedHat 9 (Shrike) on
single Pentium 4 (2.66 GHz) box with SCSI disc and 512 MB RAM.
Our database contains several tables (small size) and one special table
with ~100 records (it contains log entries from system activity).We
decided that its time to do a little clean-up and it's still running
(for about 12 hours) and it seems that it won't stop :((
Here schema of largest table:
Table "public.activities"
Column |Type | Modifiers
---+-+---
act_id| bigint | not null
act_type | character varying(32) | not null
act_activity_date | timestamp without time zone | not null
act_synch_date| timestamp without time zone |
act_state | character varying(32) |
act_mcn_id| bigint |
act_mcn_alarm | character varying(16) |
act_cmd_id| bigint |
act_ctr_id| bigint |
act_emp_id| bigint |
act_parent_id | bigint |
act_rpt_id| bigint |
Indexes:
"activities_pkey" primary key, btree (act_id)
"activities_act_cmd_id" btree (act_cmd_id)
"activities_act_ctr_id" btree (act_ctr_id)
"activities_act_state_idx" btree (act_state)
"activities_act_type_idx" btree (act_type)
Foreign-key constraints:
"fk7a1b3bed494acc46" FOREIGN KEY (act_ctr_id) REFERENCES
controllers(ctr_id)
"fk7a1b3bed4c50f03f" FOREIGN KEY (act_emp_id) REFERENCES
employees(emp_id)
"fk7a1b3bed48e1ca8d" FOREIGN KEY (act_cmd_id) REFERENCES
commands(cmd_id)
"fk7a1b3bed5969e16f" FOREIGN KEY (act_mcn_id) REFERENCES
machines(mcn_id)
"fk7a1b3bedf3fd6e40" FOREIGN KEY (act_parent_id) REFERENCES
activities(act_id)
"fk7a1b3bed62ac0851" FOREIGN KEY (act_rpt_id) REFERENCES
and our killer delete:
mrt-vend2-jpalka=# explain delete from activities where
act_type='controller-activity' and act_ctr_id in (select ctr_id from
controllers where ctr_opr_id in (1,2));
QUERY PLAN
Merge IN Join (cost=9.87..17834.97 rows=84933 width=6)
Merge Cond: ("outer".act_ctr_id = "inner".ctr_id)
-> Index Scan using activities_act_ctr_id on activities
(cost=0.00..34087.59 rows=402627 width=14)
Filter: ((act_type)::text = 'controller-activity'::text)
-> Sort (cost=9.87..10.09 rows=89 width=8)
Sort Key: controllers.ctr_id
-> Seq Scan on controllers (cost=0.00..6.99 rows=89 width=8)
Filter: ((ctr_opr_id = 1) OR (ctr_opr_id = 2))
(8 rows)
reports(rpt_id)
Table controllers contains about 200 records.Is it problem with large
number of foreign keys in activities table?
Can you help me?
Thanks,
Jaroslaw Palka
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] [sfpug] DATA directory on network attached storage
On Fri, Apr 08, 2005 at 10:01:55AM -0700, Jeff Frost wrote: > We are currently considering the possibility of creating a warm standby > machine utilizing heartbeat and a network attached storage device for the > DATA directory. The idea being that the warm standby machine has its > postmaster stopped. When heartbeat detects the death of the master server, > the postmaster is started up on the warm standby using the shared DATA > directory. Other than the obvious problems of both postmasters > inadvertently attempting access at the same time, I'm curious to know if > anyone has tried any similar setups and what the experiences have been. > Specifically is the performance of gigE good enough to allow postgres to > perform under load with an NFS mounted DATA dir? Are there other problems > I haven't thought about? Any input would be greatly appreciated. We (Zapatec Inc) have been running lots of Pg dbs off of a Network Appliance fileserver (NFS TCPv3) with FreeBSD client machines for several years now with no problems AFAICT other than insufficient bandwidth between servers and the fileserver (for one application, www.fastbuzz.com, 100baseTX (over a private switched network) was insufficient, but IDE-UDMA was fine, so GigE would have worked too, but we couldn't justify purchasing a new GigE adapter for our Netapp). We have the same setup as you would like, allowing for warm standby(s), however we haven't had to use them at all. We have not, AFAICT, had any problems with the traffic over NFS as far as reliability -- I'm sure there is a performance penalty, but the reliability and scalability gains more than offset that. FWIW, if I were to do this anew, I would probably opt for iSCSI over GigE with a NetApp. Adi ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Recognizing range constraints (was Re: Plan for relatively simple query seems to be very inefficient)
Quoting Tom Lane <[EMAIL PROTECTED]>: > Mischa <[EMAIL PROTECTED]> writes: > > Quoting Tom Lane <[EMAIL PROTECTED]>: > >> WHERE a.x > b.y AND a.x < 42 > > > Out of curiosity, will the planner induce "b.y < 42" out of this? > > No. There's some smarts about transitive equality, but none about > transitive inequalities. Offhand I'm not sure if it'd be useful to add > such. The transitive-equality code pulls its weight [...] > but I'm less able to think of common use-cases for transitive > inequality ... Thanks. My apologies for not just going and looking at the code first. Equality-transitives: yes, worth their weight in gold. Inequality-transitivies: I see in OLAP queries (usually ranges), or in queries against big UNION ALL views, where const false inequalities are the norm. "a.x > b.y and a.x < c.z" comes up in OLAP, too, usually inside an EXISTS(...), where you are doing something analogous to finding a path. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PERFORM] DATA directory on network attached storage
We are currently considering the possibility of creating a warm standby machine utilizing heartbeat and a network attached storage device for the DATA directory. The idea being that the warm standby machine has its postmaster stopped. When heartbeat detects the death of the master server, the postmaster is started up on the warm standby using the shared DATA directory. Other than the obvious problems of both postmasters inadvertently attempting access at the same time, I'm curious to know if anyone has tried any similar setups and what the experiences have been. Specifically is the performance of gigE good enough to allow postgres to perform under load with an NFS mounted DATA dir? Are there other problems I haven't thought about? Any input would be greatly appreciated. Thanks! -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[PERFORM] help on explain analyse
hi, I am using psql 7.1.3 I didn't find option analyse in explain command.. how to get time taken by SQL procedure/query? regards, stp.. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[PERFORM] 8.0.1 much slower than 7.4.2?
Hi, I have just upgraded our db from 7.4.2 to 8.0.1 and we are doing some testing. For some reason, we have discovered that our application performs much slower on 8.0.1. My initial reaction was to turn on log_min_duration_statement to see what's happening. However, log_min_duration_statement does not work for JDBC clients in 8.0.1. As a result, I modified log_statement to all. Without my application doing anything, I see statements below being executed non-stop. Who is triggering these statemetns? Is this normal? What am I doing wrong? I am using Fedora Core 1 - Kernel: 2.4.22-1.2174.nptl Please help. Thanks. 2005-04-04 18:05:00 CST PARSELOG: statement: SELECT attnotnull FROM pg_catalog.pg_attribute WHERE attrelid = $1 AND attnum = $2 2005-04-04 18:05:00 CST PARSELOG: statement: SELECT def.adsrc FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid ) LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and a.attnum = $2 AND def.adsrc L IKE '%nextval(%' 2005-04-04 18:05:00 CST PARSELOG: statement: SELECT attnotnull FROM pg_catalog.pg_attribute WHERE attrelid = $1 AND attnum = $2 2005-04-04 18:05:00 CST PARSELOG: statement: SELECT def.adsrc FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid ) LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and a.attnum = $2 AND def.adsrc L IKE '%nextval(%' 2005-04-04 18:05:00 CST PARSELOG: statement: SELECT attnotnull FROM pg_catalog.pg_attribute WHERE attrelid = $1 AND attnum = $2 2005-04-04 18:05:00 CST PARSELOG: statement: SELECT def.adsrc FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid ) LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and a.attnum = $2 AND def.adsrc L IKE '%nextval(%' 2005-04-04 18:05:00 CST PARSELOG: statement: SELECT attnotnull FROM pg_catalog.pg_attribute WHERE attrelid = $1 AND attnum = $2 2005-04-04 18:05:00 CST PARSELOG: statement: SELECT def.adsrc FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid ) LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and a.attnum = $2 AND def.adsrc L IKE '%nextval(%' 2005-04-04 18:05:00 CST PARSELOG: statement: SELECT attnotnull FROM pg_catalog.pg_attribute WHERE attrelid = $1 AND attnum = $2 2005-04-04 18:05:00 CST PARSELOG: statement: SELECT def.adsrc FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid ) LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and a.attnum = $2 AND def.adsrc L IKE '%nextval(%' 2005-04-04 18:05:00 CST PARSELOG: statement: SELECT attnotnull FROM pg_catalog.pg_attribute WHERE attrelid = $1 AND attnum = $2 2005-04-04 18:05:00 CST PARSELOG: statement: SELECT def.adsrc FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid ) LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and a.attnum = $2 AND def.adsrc L IKE '%nextval(%' 2005-04-04 18:05:00 CST PARSELOG: statement: SELECT attnotnull FROM pg_catalog.pg_attribute WHERE attrelid = $1 AND attnum = $2 2005-04-04 18:05:00 CST PARSELOG: statement: SELECT def.adsrc FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid ) LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and a.attnum = $2 AND def.adsrc L IKE '%nextval(%' 2005-04-04 18:05:00 CST PARSELOG: statement: SELECT attnotnull FROM pg_catalog.pg_attribute WHERE attrelid = $1 AND attnum = $2 2005-04-04 18:05:00 CST PARSELOG: statement: SELECT def.adsrc FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid ) LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and a.attnum = $2 AND def.adsrc L IKE '%nextval(%' 2005-04-04 18:05:00 CST PARSELOG: statement: SELECT attnotnull FROM pg_catalog.pg_attribute WHERE attrelid = $1 AND attnum = $2 2005-04-04 18:05:00 CST PARSELOG: statement: SELECT def.adsrc FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid ) LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and a.attnum = $2 AND def.adsrc L IKE '%nextval(%' 2005-04-04 18:05:00 CST PARSELOG: statement: SELECT attnotnull FROM pg_catalog.pg_attribute WHERE attrelid = $1 AND attnum = $2 2005-04-04 18:05:00 CST PARSELOG: statement: SELECT def.adsrc FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid ) LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and a.attnum = $2 AND def.adsrc L IKE '%nextval(%' 2005-04-04 18:05:00 CST PARSELOG
Re: [PERFORM] Query Optimizer Failure / Possible Bug
Mhh. I have no clue about the internals of PostgreSQL and query planing,
but to me as user this should really be a thing the optimizer has to
work out..
On 03.04.2005 10:01, PFC wrote:
Noticed this problem,too.
You can always make the calculation you want done once inside a set
returning function so it'll behave like a table, but that's ugly.
On Mon, 28 Mar 2005 16:14:44 +0200, Hannes Dorbath
<[EMAIL PROTECTED]> wrote:
hm, a few days and not a single reply :|
any more information needed? test data? simplified test case? anything?
thanks
Hannes Dorbath wrote:
The query and the corresponding EXPLAIN is at
http://hannes.imos.net/query.txt
I'd like to use the column q.replaced_serials for multiple calculations
in the SELECT clause, but every time it is referenced there in some way
the whole query in the FROM clause returning q is executed again.
This doesn't make sense to me at all and eats performance.
If this wasn't clear enough, for every
q.replaced_serials AS some_column
in the SELECT clause there is new block of
---
-> Aggregate (cost=884.23..884.23 rows=1 width=0)
-> Nested Loop (cost=0.00..884.23 rows=1 width=0)
-> Index Scan using ix_rma_ticket_serials_replace on
rma_ticket_serials rts (cost=0.00..122.35
rows=190 width=4)
Index Cond: ("replace" = false)
-> Index Scan using pk_serials on serials s
(cost=0.00..3.51 rows=1 width=4)
Index Cond: (s.serial_id = "outer".serial_id)
Filter: ((article_no = $0) AND (delivery_id = $1))
---
in the EXPLAIN result.
For those who wonder why I do this FROM (SELECT...). I was
searching for
a way to use the result of an subselect for multiple calculations in the
SELECT clause and return that calculation results as individual columns.
I tested a bit further and found out that PG behaves the same in case q
is a view. This makes me wonder how efficient the optimizer can work
with views - or even worse - nested views.
Tested and reproduced on PG 7.4.1 linux and 8.0.0 win32.
Thanks in advance,
Hannes Dorbath
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [PERFORM] Compressing WAL
""Jim C. Nasby"" <[EMAIL PROTECTED]> writes > Has anyone looked at compressing WAL's before writing to disk? On a > system generating a lot of WAL it seems there might be some gains to be > had WAL data could be compressed before going to disk, since today's > machines are generally more I/O bound than CPU bound. And unlike the > base tables, you generally don't need to read the WAL, so you don't > really need to worry about not being able to quickly scan through the > data without decompressing it. > -- The problem is where you put the compression code? If you put it inside XLogInsert lock or XLogWrite lock, which will hold the lock too long? Or anywhere else? Regards, Qingqing ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Postgresql vs SQLserver for this application ?
Well, quite honestly, if you need this performance (5000 ins / sec) and features (clustering, replication) - you should be looking at DB2 or Oracle. That is not to say that PG can not do the job, or that its not a great database, but the reason that DB2 and Oracle are still in wide use is because they answer the exact question you asked. -Barry [EMAIL PROTECTED] wrote: hi all. We are designing a quite big application that requires a high-performance database backend. The rates we need to obtain are at least 5000 inserts per second and 15 selects per second for one connection. There should only be 3 or 4 simultaneous connections. I think our main concern is to deal with the constant flow of data coming from the inserts that must be available for selection as fast as possible. (kind of real time access ...) As a consequence, the database should rapidly increase up to more than one hundred gigs. We still have to determine how and when we shoud backup old data to prevent the application from a performance drop. We intend to develop some kind of real-time partionning on our main table keep the flows up. At first, we were planning to use SQL Server as it has features that in my opinion could help us a lot : - replication - clustering Recently we started to study Postgresql as a solution for our project : - it also has replication - Postgis module can handle geographic datatypes (which would facilitate our developments) - We do have a strong knowledge on Postgresql administration (we use it for production processes) - it is free (!) and we could save money for hardware purchase. Is SQL server clustering a real asset ? How reliable are Postgresql replication tools ? Should I trust Postgresql performance for this kind of needs ? My question is a bit fuzzy but any advices are most welcome... hardware,tuning or design tips as well :)) Thanks a lot. Benjamin. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] Is Indexed View Supported in psql 7.1.3??
hi, I like to know whether Indexed View supported in psql 7.1.3.? Is there any performance analysis tool for psql.? Please! update me for the same. regards, stp. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] help on explain analyse
S.Thanga Prakash wrote: hi, I am using psql 7.1.3 I didn't find option analyse in explain command.. how to get time taken by SQL procedure/query? regards, stp.. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings I don't believe it was added until 7.2. It is highly recommended that you upgrade. Performance and stability have both been improved tremendously between 7.1 and 8.0. John =:-> signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Is Indexed View Supported in psql 7.1.3??
I like to know whether Indexed View supported in psql 7.1.3.? No... Is there any performance analysis tool for psql.? No, we keep telling you to upgrade to newer PostgreSQL. Then you can use EXPLAIN ANALYZE. Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Is Indexed View Supported in psql 7.1.3??
stp, I cannot help you with the first point, but as far as performance analysis, I share with you what I've been using. 1) pgbench -- which comes with PostgreSQL 2) OSDB (http://osdb.sourceforge.net/) 3) pg_autotune (http://pgfoundry.org/projects/pgautotune/) 4) PQA (http://pgfoundry.org/projects/pqa/) You did not mention how your database is being used/going to be used. If its already in production, use PQA, but I personally have not implemented yet since seemed to be to take a performance hit of 15-25% when running it. Your mileage may vary. I use pgbench for quick tests and OSDB for more disk thrash testing. I am new to this; maybe someone else may be able to speak from more experience. Regards. Steve Poe ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Server crashing
Don Drake <[EMAIL PROTECTED]> writes: > My server is crashing on a delete statement. > Here's the error message in the log file: > LOG: 0: received fast shutdown request > LOCATION: pmdie, postmaster.c:1736 That says that something sent the postmaster a SIGINT signal. I think it's highly unlikely that the DELETE statement did it. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Never ending delete story
=?UTF-8?B?SmFyb3PFgmF3IFBhxYJrYQ==?= <[EMAIL PROTECTED]> writes: > We are running PostgreSQL server version 7.4.6 on RedHat 9 (Shrike) on > single Pentium 4 (2.66 GHz) box with SCSI disc and 512 MB RAM. > Our database contains several tables (small size) and one special table > with ~100 records (it contains log entries from system activity).We > decided that its time to do a little clean-up and it's still running > (for about 12 hours) and it seems that it won't stop :(( Do you have any foreign keys linking *to* (not from) this table? If so, they probably need indexes on the far end. Also check for datatype discrepancies. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
