Re: [PERFORM] speed of querry?
Richard Huxton writes: > In the first, we match outer.clientnum to inner.clientnum, in the second > it's "?column10?" - are you sure the query was identical in each case. > I'm guessing the unidentified column in query 2 is the reason for the > sort a couple of lines below it, which seems to take up a large chunk of > time. The "?column10?" is because EXPLAIN isn't excessively bright about reporting references to outputs of lower plan nodes. (Gotta fix that sometime.) The real point here is that the planner thought that a scan plus sort would be faster than scanning an index that exactly matched the sort order the Merge Join needed ... and it was wrong :-( So this is just the usual sort of question of "are your stats up to date, maybe you need to increase stats targets, or else play with random_page_cost, etc" ... regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] How to improve db performance with $7K?
Kevin Brown <[EMAIL PROTECTED]> writes: > My question is: why does this (physical I/O scheduling) seem to matter > so much? > > Before you flame me for asking a terribly idiotic question, let me > provide some context. > > The operating system maintains a (sometimes large) buffer cache, with > each buffer being mapped to a "physical" (which in the case of RAID is > really a virtual) location on the disk. When the kernel needs to > flush the cache (e.g., during a sync(), or when it needs to free up > some pages), it doesn't write the pages in memory address order, it > writes them in *device* address order. And it, too, maintains a queue > of disk write requests. I think you're being misled by analyzing the write case. Consider the read case. When a user process requests a block and that read makes its way down to the driver level, the driver can't just put it aside and wait until it's convenient. It has to go ahead and issue the read right away. In the 10ms or so that it takes to seek to perform that read *nothing* gets done. If the driver receives more read or write requests it just has to sit on them and wait. 10ms is a lifetime for a computer. In that time dozens of other processes could have been scheduled and issued reads of their own. If any of those requests would have lied on the intervening tracks the drive missed a chance to execute them. Worse, it actually has to backtrack to get to them meaning another long seek. The same thing would happen if you had lots of processes issuing lots of small fsynced writes all over the place. Postgres doesn't really do that though. It sort of does with the WAL logs, but that shouldn't cause a lot of seeking. Perhaps it would mean that having your WAL share a spindle with other parts of the OS would have a bigger penalty on IDE drives than on SCSI drives though? -- greg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] How to improve db performance with $7K?
Tom Lane wrote: > Greg Stark <[EMAIL PROTECTED]> writes: > > In any case the issue with the IDE protocol is that fundamentally you > > can only have a single command pending. SCSI can have many commands > > pending. > > That's the bottom line: the SCSI protocol was designed (twenty years ago!) > to allow the drive to do physical I/O scheduling, because the CPU can > issue multiple commands before the drive has to report completion of the > first one. IDE isn't designed to do that. I understand that the latest > revisions to the IDE/ATA specs allow the drive to do this sort of thing, > but support for it is far from widespread. My question is: why does this (physical I/O scheduling) seem to matter so much? Before you flame me for asking a terribly idiotic question, let me provide some context. The operating system maintains a (sometimes large) buffer cache, with each buffer being mapped to a "physical" (which in the case of RAID is really a virtual) location on the disk. When the kernel needs to flush the cache (e.g., during a sync(), or when it needs to free up some pages), it doesn't write the pages in memory address order, it writes them in *device* address order. And it, too, maintains a queue of disk write requests. Now, unless some of the blocks on the disk are remapped behind the scenes such that an ordered list of blocks in the kernel translates to an out of order list on the target disk (which should be rare, since such remapping usually happens only when the target block is bad), how can the fact that the disk controller doesn't do tagged queuing *possibly* make any real difference unless the kernel's disk scheduling algorithm is suboptimal? In fact, if the kernel's scheduling algorithm is close to optimal, wouldn't the disk queuing mechanism *reduce* the overall efficiency of disk writes? After all, the kernel's queue is likely to be much larger than the disk controller's, and the kernel has knowledge of things like the filesystem layout that the disk controller and disks do not have. If the controller is only able to execute a subset of the write commands that the kernel has in its queue, at the very least the controller may end up leaving the head(s) in a suboptimal position relative to the next set of commands that it hasn't received yet, unless it simply writes the blocks in the order it receives it, right (admittedly, this is somewhat trivially dealt with by having the controller exclude the first and last blocks in the request from its internal sort). I can see how you might configure the RAID controller so that the kernel's scheduling algorithm will screw things up horribly. For instance, if the controller has several RAID volumes configured in such a way that the volumes share spindles, the kernel isn't likely to know about that (since each volume appears as its own device), so writes to multiple volumes can cause head movement where the kernel might be treating the volumes as completely independent. But that just means that you can't be dumb about how you configure your RAID setup. So what gives? Given the above, why is SCSI so much more efficient than plain, dumb SATA? And why wouldn't you be much better off with a set of dumb controllers in conjunction with (kernel-level) software RAID? -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] [NOVICE] Many connections lingering
Hi, This looks very interesting. I'll give it a better look and see if the performance penalties pgpool brings are not substantial in which case this program could be very helpful, Thanks for the hint, Slavisa On 4/14/05, Richard Huxton wrote: > Slavisa Garic wrote: > > This is a serious problem for me as there are multiple users using our > > software on our server and I would want to avoid having connections > > open for a long time. In the scenario mentioned below I haven't > > explained the magnitute of the communications happening between Agents > > and DBServer. There could possibly be 100 or more Agents per > > experiment, per user running on remote machines at the same time, > > hence we need short transactions/pgsql connections. Agents need a > > reliable connection because failure to connect could mean a loss of > > computation results that were gathered over long periods of time. > > Plenty of others have discussed the technical reasons why you are seeing > these connection issues. If you find it difficult to change your way of > working, you might find the pgpool connection-pooling project useful: >http://pgpool.projects.postgresql.org/ > > HTH > -- >Richard Huxton >Archonet Ltd > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] [NOVICE] Many connections lingering
HI Mark, My DBServer module already serves as a broker. At the moment it opens a new connection for every incoming Agent connection. I did it this way because I wanted to leave synchronisation to PGSQL. I might have to modify it a bit and use a shared, single connection for all agents. I guess that is not a bad option I just have to ensure that the code is not below par :), Also thank for the postgresql.conf hint, that limit was pretty low on our server so this might help a bit, Regards, Slavisa On 4/14/05, Mark Lewis <[EMAIL PROTECTED]> wrote: > If there are potentially hundreds of clients at a time, then you may be > running into the maximum connection limit. > > In postgresql.conf, there is a max_connections setting which IIRC > defaults to 100. If you try to open more concurrent connections to the > backend than that, you will get a connection refused. > > If your DB is fairly gnarly and your performance needs are minimal it > should be safe to increase max_connections. An alternative approach > would be to add some kind of database broker program. Instead of each > agent connecting directly to the database, they could pass their data to > a broker, which could then implement connection pooling. > > -- Mark Lewis > > On Tue, 2005-04-12 at 22:09, Slavisa Garic wrote: > > This is a serious problem for me as there are multiple users using our > > software on our server and I would want to avoid having connections > > open for a long time. In the scenario mentioned below I haven't > > explained the magnitute of the communications happening between Agents > > and DBServer. There could possibly be 100 or more Agents per > > experiment, per user running on remote machines at the same time, > > hence we need short transactions/pgsql connections. Agents need a > > reliable connection because failure to connect could mean a loss of > > computation results that were gathered over long periods of time. > > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Compressing WAL
On Sun, 2005-04-10 at 21:12 -0400, Bruce Momjian wrote: > 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. Well it was discussed 2-3 years ago as part of the PITR preamble. You may be surprised to read that over... A summary of thoughts to date on this are: xlog.c XLogInsert places backup blocks into the wal buffers before insertion, so is the right place to do this. It would be possible to do this before any LWlocks are taken, so would not not necessarily impair scalability. Currently XLogInsert is a severe CPU bottleneck around the CRC calculation, as identified recently by Tom. Digging further, the code used seems to cause processor stalls on Intel CPUs, possibly responsible for much of the CPU time. Discussions to move to a 32-bit CRC would also be effected by this because of the byte-by-byte nature of the algorithm, whatever the length of the generating polynomial. PostgreSQL's CRC algorithm is the fastest BSD code available. Until improvement is made there, I would not investigate compression further. Some input from hardware tuning specialists is required... The current LZW compression code uses a 4096 byte lookback size, so that would need to be modified to extend across a whole block. An alternative, suggested originally by Tom and rediscovered by me because I just don't read everybody's fine words in history, is to simply take out the freespace in the middle of every heap block that consists of zeros. Any solution in this area must take into account the variability of the size of freespace in database blocks. Some databases have mostly full blocks, others vary. There would also be considerable variation in compressability of blocks, especially since some blocks (e.g. TOAST) are likely to already be compressed. There'd need to be some testing done to see exactly the point where the costs of compression produce realisable benefits. So any solution must be able to cope with both compressed blocks and non-compressed blocks. My current thinking is that this could be achieved by using the spare fourth bit of the BkpBlocks portion of the XLog structure, so that either all included BkpBlocks are compressed or none of them are, and hope that allows benefit to shine through. Not thought about heap/index issues. It is possible that an XLogWriter process could be used to assist in the CRC and compression calculations also, an a similar process used to assist decompression for recovery, in time. I regret I do not currently have time to pursue further. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Compressing WAL
On Sun, Apr 10, 2005 at 09:12:41PM -0400, Bruce Momjian wrote: > 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. Could you clarify that? Maybe I'm being naive, but it seems like you could just put a compression routine between the log writer and the filesystem. > Is this a TODO? ISTM it's at least worth hacking something together and doing some performance testing... -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[PERFORM] Strange serialization problem
I have a performance problem; I'd like any suggestions on where to continue
investigation.
A set of insert-only processes seems to serialize itself. :-(
The processes appear to be blocked on disk IO, and probably the table drive,
rather than the pg_xlog drive.
Each process is inserting a block of 10K rows into a table.
I'm guessing they are "serialized" because one process by itself takes 15-20
secs; running ten processes in parallel averages 100-150 secs (each), with
elapsed (wall) time of 150-200 secs.
Polling pg_locks shows each process has (been granted) only the locks you would
expect. I RARELY see an Exclusive lock on an index, and then only on one index
at a time.
A sample from pg_locks:
TABLE/INDEX GRANTED PID MODE
m_reason t 7340 AccessShare
messaget 7340 AccessShare
messaget 7340 RowExclusive
pk_message t 7340 AccessShare
tmp_messaget 7340 AccessShare
("m_reason" is a one-row lookup table; see INSERT cmd below).
--
The query plan is quite reasonable (see below).
On a side note, this is the first app I've had to deal with that is sweet to
pg_xlog, but hammers the drive bearing the base table (3x the traffic).
"log_executor_stats" for a sample insert look reasonable (except the "elapsed"!)
! system usage stats:
! 308.591728 elapsed 3.48 user 1.27 system sec
! [4.00 user 1.39 sys total]
! 0/0 [0/0] filesystem blocks in/out
! 18212/15 [19002/418] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [0/0] voluntary/involuntary context switches
! buffer usage stats:
! Shared blocks: 9675 read, 8781 written, buffer hit rate = 97.66%
! Local blocks:504 read, 64 written, buffer hit rate = 0.00%
! Direct blocks: 0 read, 0 written
Summarized "ps" output for the above backend process, sampled every 5 secs,
shows it is 94% in the 'D' state, 3% in the 'S' state.
== BACKGROUND ==
**SOFTWARE
- PG 7.4.6, RedHat 8.
--
**HARDWARE
Xeon 2x2 2.4GHz 2GB RAM
4 x 73GB SCSI; pg_xlog and base on separate drives.
--
**APPLICATION
Six machines post batches of 10K messages to the PG db server.
Machine #nn generates its ID keys as "nn001"::bigint etc.
Each process runs:
- "COPY tmp_message FROM STDIN" loads its own one-use TEMP table.
- " INSERT INTO message
SELECT tmp.* FROM tmp_message AS tmp
JOIN m_reason ON m_reason.name = tmp.reason
LEFT JOIN message USING (ID) WHERE message.ID is null
(check required because crash recovery logic requires idempotent insert)
"DROP TABLE tmp_message" --- call me paranoid, this is 7.4
The COPY step time is almost constant when #processes varies from 1 to 10.
--
**POSTGRES
pg_autovacuum is running with default parameters.
Non-default GUC values:
checkpoint_segments= 512
default_statistics_target = 200
effective_cache_size = 50
log_min_duration_statement = 1000
max_fsm_pages = 100
max_fsm_relations = 1000
random_page_cost = 1
shared_buffers = 1
sort_mem = 16384
stats_block_level = true
stats_command_string = true
stats_row_level= true
vacuum_mem = 65536
wal_buffers= 2000
Wal_buffers and checkpoint_segments look outrageous,
but were tuned for another process, that posts batches of 1 6KB rows
in a single insert.
--
TABLE/INDEX STATISTICS
--
MACHINE STATISTICS
ps gives the backend process as >98% in (D) state, with <1% CPU.
A "top" snapshot:
CPU states: cpuusernice systemirq softirq iowaitidle
total2.0%0.0%0.8% 0.0% 0.0% 96.9%0.0%
cpu002.5%0.0%1.9% 0.0% 0.0% 95.4%0.0%
cpu011.7%0.0%0.1% 0.0% 0.3% 97.6%0.0%
cpu020.5%0.0%0.7% 0.0% 0.0% 98.6%0.0%
cpu033.1%0.0%0.5% 0.0% 0.0% 96.2%0.0%
Mem: 2061552k av, 2041752k used, 19800k free, 0k shrd, 21020k buff
iostat reports that the $PGDATA/base drive is being worked but not overworked.
The pg_xlog drive is underworked:
KBPS TPS KBPS TPS KBPS TPS KBPS TPS
12:30 1 276316 31 8 3336 269
12:40 5 3 115122 5 5 2705 320
^pg_xlog^ ^base^
The base drive has run as much as 10MBPS, 5K TPS.
--
EXPLAIN ANALYZE output:
The plan is eminently reasonable. But there's no visible relationship
between the top
[PERFORM] Problem with slow query (caused by improper nestloop?)
Someone (twanger) sent me here from the IRC channel with the following: I have a query that normally takes 0.150 seconds, but after an insert can take 14 seconds. Here's the scenario: Run this query: select * from cad_part left join smart_part using (cannon_part_id) where cad_import_id = 91 order by cad_part_reference_letter, cad_part_id The result is returned in about 150ms. Then I run my import operation which adds 1 new cad_import row, about 30 new cad_part rows, and about 100 new cad_line rows (which aren't involved in the above query). In this case, the new cad_import row has a PK of cad_import_id = 92. When I run the query again (only the where clause changed): select * from cad_part left join smart_part using (cannon_part_id) where cad_import_id = 92 order by cad_part_reference_letter, cad_part_id it takes about 14 seconds (and has a different plan). I can repeat the first query (id=91) and it still executes in 150ms and then repeat the second query and in still takes ~14 seconds. I've found two things that fix this. First, if I run analyze, the second query will take 150ms. Second, if I set enable_nestloop to false the second query will use that same plan that the first does and complete in 150ms. I've posted a bunch of details on my website including the size of the tables (all pretty small), both query plans, and some of the schema. http://tom-mack.com/query_details.html I also just redid the query without the final order by clause with the same results. So I guess my question is, am I doing something wrong? did I miss an index or something? is this a bug (a 100x hit for not running analyze seems a little severe)? should I just run "analyze cad_part" after my inserts to that table? Thanks, --Tom ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] PLM pulling from CVS nightly for testing in STP
On Wed, Apr 13, 2005 at 11:35:36AM -0700, Josh Berkus wrote: > Mark, > > > Just wanted everyone to know what we're pulling CVS HEAD nightly so it > > can be tested in STP now. Let me know if you have any questions. > > Way cool.How do I find the PLM number? How are you nameing these? The naming convention I'm using is postgresql-MMDD, for example postgresql-20050413, for the anonymous cvs export from today (April 13). I have a cronjob that'll do the export at 1AM PST8PDT. The search page for the PLM numbers is here: https://www.osdl.org/plm-cgi/plm?module=search or you can use the stpbot on linuxnet.mit.edu#osdl. Mark ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] speed of querry?
are you sure the query was identical in each case. I just ran a second time same results ensuring that the query is the same. Not sure why it is doing a column10 thing. Any ideas what to look for? Both data bases are a restore from the same backup file. One is running redhat the other XP, I believe both are the same version of postgres except for the different platform (8.0.1 I am pretty sure). I just spent the morning with Dell hoping for some explanation from them. They said I had to have the database on the same type of OS and hardware for them to think the issue was hardware. They are escalating to the software group. I did a default Redhat install so it very well may be an issue with my lack of knowledge on Linux. He did mention by default the Perc4 do cache, so I may need to visit the data center to set the percs to not cache. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] PLM pulling from CVS nightly for testing in STP
Mark, > Just wanted everyone to know what we're pulling CVS HEAD nightly so it > can be tested in STP now. Let me know if you have any questions. Way cool.How do I find the PLM number? How are you nameing these? -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(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] PLM pulling from CVS nightly for testing in STP
Hi all, Just wanted everyone to know what we're pulling CVS HEAD nightly so it can be tested in STP now. Let me know if you have any questions. Tests are not automatically run yet, but I hope to remedy that shortly. For those not familiar with STP and PLM, here are a couple of links: STP http://www.osdl.org/stp/ PLM http://www.osdl.org/plm-cgi/plm Mark ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] [NOVICE] Many connections lingering
Slavisa Garic wrote: This is a serious problem for me as there are multiple users using our software on our server and I would want to avoid having connections open for a long time. In the scenario mentioned below I haven't explained the magnitute of the communications happening between Agents and DBServer. There could possibly be 100 or more Agents per experiment, per user running on remote machines at the same time, hence we need short transactions/pgsql connections. Agents need a reliable connection because failure to connect could mean a loss of computation results that were gathered over long periods of time. Plenty of others have discussed the technical reasons why you are seeing these connection issues. If you find it difficult to change your way of working, you might find the pgpool connection-pooling project useful: http://pgpool.projects.postgresql.org/ HTH -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] speed of querry?
Joel Fradkin wrote:
I must be missing something important, because I am just not seeing why this
query is slower on a 4 processor 8 gig machine running redhat AS4.
Well, the 4 processors aren't going to help with a single query.
However, assuming the configurations for both machines are comparable,
you shouldn't be seeing a doubling in query-time.
I have, however, spotted something very strange towards the bottom of
each explain:
Machine 1 my desktop:
"-> Merge Right Join (cost=0.00..52366.50 rows=190710 width=75)
(actual time=16.000..1973.000 rows=177041 loops=1)"
" Merge Cond: ((("outer".clientnum)::text =
("inner".clientnum)::text) AND ("outer".id = "inner".jobtitleid))"
Test Linux machine:
"-> Merge Right Join (cost=24825.80..27512.71 rows=176015
width=75) (actual time=8743.848..9750.775 rows=177041 loops=1)"
" Merge Cond: ((("outer".clientnum)::text =
"inner"."?column10?") AND ("outer".id = "inner".jobtitleid))"
In the first, we match outer.clientnum to inner.clientnum, in the second
it's "?column10?" - are you sure the query was identical in each case.
I'm guessing the unidentified column in query 2 is the reason for the
sort a couple of lines below it, which seems to take up a large chunk of
time.
--
Richard Huxton
Archonet Ltd
---(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] [NOVICE] Many connections lingering
On Apr 13, 2005, at 1:09 AM, Slavisa Garic wrote: This is not a Windows server. Both server and client are the same machine (done for testing purposes) and it is a Fedora RC2 machine. This also happens on debian server and client in which case they were two separate machines. There are thousands (2+) of these waiting around and each one of them dissapears after 50ish seconds. I tried psql command line and monitored that connection in netstats. After I did a graceful exit (\quit) the connection changed to TIME_WAIT and it was sitting there for around 50 seconds. I thought I could do what you suggested with having one connection and making each query a full BEGIN/QUERY/COMMIT transaction but I thought I could avoid that :). If you do a bit of searching on TIME_WAIT you'll find this is a common TCP/IP related problem, but the behavior is within the specs of the protocol. I don't know how to do it on Linux, but you should be able to change TIME_WAIT to a shorter value. For the archives, here is a pointer on changing TIME_WAIT on Windows: http://www.winguides.com/registry/display.php/878/ John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] [NOVICE] Many connections lingering
If there are potentially hundreds of clients at a time, then you may be running into the maximum connection limit. In postgresql.conf, there is a max_connections setting which IIRC defaults to 100. If you try to open more concurrent connections to the backend than that, you will get a connection refused. If your DB is fairly gnarly and your performance needs are minimal it should be safe to increase max_connections. An alternative approach would be to add some kind of database broker program. Instead of each agent connecting directly to the database, they could pass their data to a broker, which could then implement connection pooling. -- Mark Lewis On Tue, 2005-04-12 at 22:09, Slavisa Garic wrote: > This is a serious problem for me as there are multiple users using our > software on our server and I would want to avoid having connections > open for a long time. In the scenario mentioned below I haven't > explained the magnitute of the communications happening between Agents > and DBServer. There could possibly be 100 or more Agents per > experiment, per user running on remote machines at the same time, > hence we need short transactions/pgsql connections. Agents need a > reliable connection because failure to connect could mean a loss of > computation results that were gathered over long periods of time. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Foreign keys and partial indexes
Nichlas =?iso-8859-1?Q?L=F6fdahl?= <[EMAIL PROTECTED]> writes:
> I have a partial index (btree(col) WHERE col > 0) on table2 ('col' contains
> alot of NULL-values).
> There's also a foreign key on the column pointing to the primary key of
> table1 (ON UPDATE CASCADE ON DELETE SET NULL). During update/delete, it seems
> like it cannot use the partial index to find corresponding rows matching the
> foreign key (doing a full seqscan instead)?
> Is there any special reason for not letting the planner use the partial index
> when appropriate?
It doesn't know it's appropriate. There's nothing constraining the FK
to be positive, after all.
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[PERFORM] Foreign keys and partial indexes
Hello!
I have a partial index (btree(col) WHERE col > 0) on table2 ('col' contains
alot of NULL-values).
There's also a foreign key on the column pointing to the primary key of table1
(ON UPDATE CASCADE ON DELETE SET NULL). During update/delete, it seems like it
cannot use the partial index to find corresponding rows matching the foreign
key (doing a full seqscan instead)?
Is there any special reason for not letting the planner use the partial index
when appropriate?
\d table1
Table "public.table1"
Column | Type | Modifiers
+-+---
id | integer | not null
text | text|
Indexes:
"table1_pkey" primary key, btree (id)
\d table2
Table "public.table2"
Column | Type | Modifiers
+-+---
id | integer | not null
col| integer |
value | integer |
Indexes:
"table2_pkey" primary key, btree (id)
CREATE INDEX col_part_key ON table2 USING btree(col) WHERE col > 0;
ANALYZE table2;
EXPLAIN ANALYZE DELETE FROM table2 WHERE col=1;
QUERY PLAN
-
Index Scan using col_part_key on table2 (cost=0.00..6.01 rows=6 width=6)
(actual time=0.592..1.324 rows=8 loops=1)
Index Cond: (col = 1)
Total runtime: 4.904 ms
Delete manually WITHOUT foreign key:
test=> begin work;
BEGIN
Time: 0.808 ms
test=> explain analyze delete from table1 where id=1;
QUERY PLAN
Index Scan using table1_pkey on table1 (cost=0.00..3.01 rows=2 width=6)
(actual time=0.312..0.324 rows=1 loops=1)
Index Cond: (id = 1)
Total runtime: 0.623 ms
(3 rows)
Time: 3.912 ms
test=> explain analyze delete from table2 where col=1;
QUERY PLAN
---
Index Scan using col_part_key on table2 (cost=0.00..14.70 rows=36 width=6)
(actual time=0.338..0.557 rows=8 loops=1)
Index Cond: (col = 1)
Total runtime: 0.881 ms
(3 rows)
Time: 3.802 ms
test=> rollback;
ROLLBACK
Delete WITH foreign key:
test=> ALTER TABLE table2 ADD CONSTRAINT col_fkey FOREIGN KEY (col) REFERENCES
table1(id) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE
Time: 3783.009 ms
test=> begin work;
BEGIN
Time: 1.509 ms
test=> explain analyze delete from table1 where id=1;
rollback;
QUERY PLAN
Index Scan using table1_pkey on table1 (cost=0.00..3.01 rows=2 width=6)
(actual time=0.769..0.781 rows=1 loops=1)
Index Cond: (id = 1)
Total runtime: 1.027 ms
(3 rows)
Time: 3458.585 ms
test=> rollback;
ROLLBACK
Time: 1.506 ms
/Nichlas
---(end of broadcast)---
TIP 8: explain analyze is your friend
[PERFORM] speed of querry?
I must be missing something important, because I am just not seeing why this
query is slower on a 4 processor 8 gig machine running redhat AS4.
The SQL:
explain analyze SELECT a.clientnum, a.associateid, a.associatenum,
a.lastname, a.firstname, jt.value AS jobtitle, l.name AS "location",
l.locationid AS mainlocationid, l.divisionid, l.regionid, l.districtid,
(a.lastname::text || ', '::text) || a.firstname::text AS assocname,
a.isactive, a.isdeleted
FROM tblassociate a
left JOIN tbljobtitle jt ON a.jobtitleid = jt.id AND jt.clientnum::text =
a.clientnum::text AND 1 = jt.presentationid
JOIN tbllocation l ON a.locationid = l.locationid AND l.clientnum::text =
a.clientnum::text
where a.clientnum = 'SAKS';
Machine 1 my desktop:
"Merge Join (cost=74970.51..75975.46 rows=8244 width=113) (actual
time=5141.000..6363.000 rows=160593 loops=1)"
" Merge Cond: ("outer".locationid = "inner".locationid)"
" -> Sort (cost=656.22..657.11 rows=354 width=49) (actual
time=16.000..16.000 rows=441 loops=1)"
"Sort Key: l.locationid"
"-> Index Scan using ix_location on tbllocation l
(cost=0.00..641.23 rows=354 width=49) (actual time=0.000..0.000 rows=441
loops=1)"
" Index Cond: ('SAKS'::text = (clientnum)::text)"
" -> Sort (cost=74314.29..74791.06 rows=190710 width=75) (actual
time=5125.000..5316.000 rows=160594 loops=1)"
"Sort Key: a.locationid"
"-> Merge Right Join (cost=0.00..52366.50 rows=190710 width=75)
(actual time=16.000..1973.000 rows=177041 loops=1)"
" Merge Cond: ((("outer".clientnum)::text =
("inner".clientnum)::text) AND ("outer".id = "inner".jobtitleid))"
" -> Index Scan using ix_tbljobtitle_id on tbljobtitle jt
(cost=0.00..244.75 rows=6622 width=37) (actual time=0.000..16.000 rows=5690
loops=1)"
"Filter: (1 = presentationid)"
" -> Index Scan using ix_tblassoc_jobtitleid on tblassociate a
(cost=0.00..50523.83 rows=190710 width=53) (actual time=0.000..643.000
rows=177041 loops=1)"
"Index Cond: ((clientnum)::text = 'SAKS'::text)"
"Total runtime: 6719.000 ms"
Test Linux machine:
"Merge Join (cost=48126.04..49173.57 rows=15409 width=113) (actual
time=11832.165..12678.025 rows=160593 loops=1)"
" Merge Cond: ("outer".locationid = "inner".locationid)"
" -> Sort (cost=807.64..808.75 rows=443 width=49) (actual
time=2.418..2.692 rows=441 loops=1)"
"Sort Key: l.locationid"
"-> Index Scan using ix_location on tbllocation l
(cost=0.00..788.17 rows=443 width=49) (actual time=0.036..1.677 rows=441
loops=1)"
" Index Cond: ('SAKS'::text = (clientnum)::text)"
" -> Sort (cost=47318.40..47758.44 rows=176015 width=75) (actual
time=11829.660..12002.746 rows=160594 loops=1)"
"Sort Key: a.locationid"
"-> Merge Right Join (cost=24825.80..27512.71 rows=176015
width=75) (actual time=8743.848..9750.775 rows=177041 loops=1)"
" Merge Cond: ((("outer".clientnum)::text =
"inner"."?column10?") AND ("outer".id = "inner".jobtitleid))"
" -> Index Scan using ix_tbljobtitle_id on tbljobtitle jt
(cost=0.00..239.76 rows=6604 width=37) (actual time=0.016..11.323 rows=5690
loops=1)"
"Filter: (1 = presentationid)"
" -> Sort (cost=24825.80..25265.84 rows=176015 width=53)
(actual time=8729.320..8945.292 rows=177041 loops=1)"
"Sort Key: (a.clientnum)::text, a.jobtitleid"
"-> Index Scan using ix_associate_clientnum on
tblassociate a (cost=0.00..9490.20 rows=176015 width=53) (actual
time=0.036..1071.867 rows=177041 loops=1)"
" Index Cond: ((clientnum)::text = 'SAKS'::text)"
"Total runtime: 12802.019 ms"
I tried to remove the left outer thinking it would speed it up, and it used
a seq search on tblassoc and ran 2 times slower.
Joel Fradkin
Wazagua, Inc.
2520 Trailmate Dr
Sarasota, Florida 34243
Tel. 941-753-7111 ext 305
[EMAIL PROTECTED]
www.wazagua.com
Powered by Wazagua
Providing you with the latest Web-based technology & advanced tools.
C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc
This email message is for the use of the intended recipient(s) and may
contain confidential and privileged information. Any unauthorized review,
use, disclosure or distribution is prohibited. If you are not the intended
recipient, please contact the sender by reply email and delete and destroy
all copies of the original message, including attachments.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] performance hit for replication
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of > Matthew Nuzum > Sent: 12 April 2005 17:25 > To: pgsql-performance@postgresql.org > Subject: [PERFORM] performance hit for replication > > So, my question is this: My server currently works great, > performance wise. > I need to add fail-over capability, but I'm afraid that introducing a > stressful task such as replication will hurt my server's > performance. Is > there any foundation to my fears? I don't need to replicate > the archived log > data because I can easily restore that in a separate step > from the nightly > backup if disaster occurs. Also, my database load is largely > selects. My > application works great with PostgreSQL 7.3 and 7.4, but I'm > currently using > 7.3. If it's possible to upgrade to 8.0 then perhaps you could make use of PITR and continuously ship log files to your standby machine. http://www.postgresql.org/docs/8.0/interactive/backup-online.html I can't help further with this as I've yet to give it a go myself, but others here may have tried it. Regards, Dave. ---(end of broadcast)--- TIP 8: explain analyze is your friend
