Re: [PERFORM] Picking out the most recent row using a time stamp column
On Feb 24, 2011, at 14:55, Dave Crooke wrote: Is there a more elegant way to write this, perhaps using PG-specific extensions? SELECT DISTINCT ON (data.id_key) data.id_key, data.time_stamp, data.value FROM data ORDER BY data.id_key, data.time_stamp DESC; Michael Glaesemann grzm seespotcode net -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [HACKERS] [PERFORM] Slow count(*) again...
On Feb 3, 2011, at 17:08, Josh Berkus wrote: On 2/3/11 1:18 PM, Chris Browne wrote: mladen.gog...@vmsinfo.com (Mladen Gogala) writes: I must say that this purist attitude is extremely surprising to me. All the major DB vendors support optimizer hints, I don't think that's actually accurate. Can you give me a list of DBMSes which support hints other than Oracle? 1 minute of Googling shows results for: db2: http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc.admin/p9li375.htm informix: http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/0502fan/0502fan.html sybase: http://searchenterpriselinux.techtarget.com/answer/Query-hints-to-override-optimizer mysql: http://dev.mysql.com/doc/refman/5.0/en/index-hints.html I haven't read much of the rest of this thread, so others may have brought these up before. Michael Glaesemann grzm seespotcode net -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Getting a random row
On Oct 13, 2009, at 11:19 , Grzegorz Jaśkiewicz wrote: On Tue, Oct 13, 2009 at 4:17 PM, Shaul Dar shaul...@gmail.com wrote: Also PG does not have a concept of an auto-increment pseudo-column like Oracle's rownum. Any suggestions? not true - it has sequences, and pseudo type serial. Rtfm!. AIUI, rownum applies numbering to output rows in a SELECT statement, rather than some actual column of the table, which is likely what the OP is getting at. http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html Michael Glaesemann grzm seespotcode net -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] How to post Performance Questions
On Sep 14, 2009, at 16:55 , Josh Berkus wrote: Users, Please read the following two documents before posting your performance query here: http://wiki.postgresql.org/wiki/Guide_to_reporting_problems http://wiki.postgresql.org/wiki/SlowQueryQuestions This will help other users to troubleshoot your problems far more rapidly. Can something similar be added to the footer of (at least) the performance list? Michael Glaesemann grzm seespotcode net -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Re: Query Optimization with Krusk al’s Algorithm
On May 10, 2008, at 1:31 PM, Rauan Maemirov wrote: I also would like to hear from them. But seems like the thread is loosed in tonn of other threads. It's also the middle of a commit fest, when a lot of the developers are focussed on processing the current patches in the queue, rather than actively exploring new, potential features. Michael Glaesemann grzm seespotcode net -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] EXPLAIN ANALYZE time calculations
I'd like to get confirmation that I'm correctly understanding the times given in EXPLAIN ANALYZE. Taking the example given in the Using Explain section of the docs, http://www.postgresql.org/docs/current/static/using-explain EXPLAIN ANALYZE SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 100 AND t1.unique2 = t2.unique2; QUERY PLAN -- Nested Loop (cost=2.37..553.11 rows=106 width=488) (actual time=1.392..12.700 rows=100 loops=1) - Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244) (actual time=0.878..2.367 rows=100 loops=1) Recheck Cond: (unique1 100) - Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0) (actual time=0.546..0.546 rows=100 loops=1) Index Cond: (unique1 100) - Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.00..3.01 rows=1 width=244) (actual time=0.067..0.078 rows=1 loops=100) Index Cond: (outer.unique2 = t2.unique2) Total runtime: 14.452 ms I'm interested in figuring out what percentage of the total runtime is spent in each node. Here are my calculations. Nested loop: actual time: 12.700 ms/loop * 1 loop = 12.700 ms percent of total runtime: 88% percent spent in subnodes: 16% + 54% = 70% percent spent in node: 18% Bitmap Heap Scan on tenk1: actual time: 2.367 ms/loop * 1 loop = 2.367 ms percent of total runtime: 16% time spent in subnodes: 4% time spent in node: 12% Bitmap Heap Scan on tenk1_unique1: actual time: 0.546 ms/loop * 1 loop = 0.546 ms: 4% time spent in subnodes: 0% time spent in node: 4% Index Scan total time: actual time: 0.078 ms/loop * 100 loops = 7.80 ms percent of total runtime: 54% percent spent in subnodes: 0% percent spent in node: 54% executor overhead: 14.452 ms - 12.700 ms = 1.752 ms: 12% Is this correct? Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] EXPLAIN ANALYZE time calculations
On Dec 2, 2007, at 19:56 , Tom Lane wrote: IOW the actual time to get in and out of a node is going to be a shade more than is reported. Thanks, Tom. Should be close enough for jazz. When I was first going over the Using Explain section, I stumbled a bit on the startup time/total time/loops bit (which is why explain- analyze.info times and percentages are currently miscalculated). I took startup time to be the time to return the first row *of the first loop*. But it's actually the average startup time to return the first row *in each loop*, right? Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Improving Query
On Oct 30, 2007, at 7:18 , Ketema wrote: here is the execution plan: I've put this online here: http://explain-analyze.info/query_plans/1259-ketema-2007-10-30 I have attached an erd of the tables used in this query. If it is stripped out it can be viewed here: http://www.ketema.net/ provision_list_tables_erd.jpg My concern is with the sort step that takes 15 seconds by itself: - Sort (cost=1235567017.53..1238002161.29 rows=974057502 width=290) (actual time=16576.997..16577.513 rows=3366 loops=1) What jumps out at me is the huge difference in estimated and returned rows, and the huge cost estimates. Have you analyzed recently? Do you have enable_seqscan disabled? It appears so, due to the high cost here: - Seq Scan on order_details (cost=1.0..10012.45 rows=35 width=199) (actual time=0.001..0.001 rows=0 loops=1) http://explain-analyze.info/query_plans/1259-ketema-2007-10-30#node-3594 What does it look like with seqscan enabled? 2)Create Views of the counts and the sub select...is this any faster as the view is executed at run time anyway? Views aren't materialized: it's like inlining the definition of the view itself in the query. 3)Create actual tables of the sub select and aggregates...How would this be maintained to ensure it was always accurate? One way would be to update the summaries using triggers. Hopefully you won't need to do this after analyzing and perhaps tweaking your server configuration. Unfortunately I don't have the time to look at the query plan in more detail, but I suspect there's a better way to get the results you're looking for. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Optimising in queries
On Aug 22, 2007, at 5:58 , Russell Smith wrote: Stephen Davies wrote: select count(rdate),rdate from reading where sensor_id in (1137,1138,1139,1140) group by rdate order by rdate desc limit 1; It would have been helpful to see the table definition here. I can say up front that array processing in postgres is SLOW. Um, what array processing are you seeing here? IN (a, b, b) is not an array construct. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Optimising in queries
[Please don't top post as it makes the discussion more difficult to follow.] On Aug 22, 2007, at 18:30 , Stephen Davies wrote: I have always thought of array processing as the thing that vector processors such as Cray and ETA do/did. (I've always heard that referred to as vector processing.) While superficially equivalent, I have always believed that IN (a,b,c) executed faster than =a or =b or =c. Am I wrong for PostgreSQL? Depending on the numbers of the IN list and other statistcs, I believe PostgreSQL will rewrite z in IN (a, b, ...) into either (z = a) OR (z = b) OR ... or somehow add it to the join list, so performance will vary. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Autovacuum is running forever
On Aug 21, 2007, at 16:26 , Sachchida Ojha wrote: In my OLTP system, we are inserting, updating and deleting the data every second. Autovacuum started and never finished slowing down the whole system. There's the possibility that your autovacuum settings aren't aggressive enough for your system, so it's never able to catch up. Without knowing details it's hard to say for certain. What are your autovacuum settings and other details about the load on your system? Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] insert vs select into performance
On Jul 17, 2007, at 14:38 , Thomas Finneid wrote: I was doing some testing on insert compared to select into. I inserted 100 000 rows (with 8 column values) into a table, which took 14 seconds, compared to a select into, which took 0.8 seconds. (fyi, the inserts where batched, autocommit was turned off and it all happend on the local machine) Now I am wondering why the select into is that much faster? It would be helpful if you included the actual queries you're using, as there are a number of variables: 1) If there are any constraints on the original table, the INSERT will be checking those constraints. AIUI, SELECT INTO does not generate any table constraints. 2a) Are you using INSERT INTO foo (foo1, foo2, foo2) SELECT foo1, foo2, foo3 FROM pre_foo or individual inserts for each row? The former would be faster than the latter. 2b) If you are doing individual inserts, are you wrapping them in a transaction? The latter would be faster. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] insert vs select into performance
On Jul 17, 2007, at 15:50 , Thomas Finneid wrote: Michael Glaesemann wrote: 2a) Are you using INSERT INTO foo (foo1, foo2, foo2) SELECT foo1, foo2, foo3 FROM pre_foo or individual inserts for each row? The former would be faster than the latter. performed with JDBC insert into ciu_data_type (id, loc_id, value3, value5, value8, value9, value10, value11 ) values (?,?,?,?,?,?,?,?) As they're individual inserts, I think what you're seeing is overhead from calling this statement 100,000 times, not just on the server but also the overhead through JDBC. For comparison, try CREATE TABLE ciu_data_type_copy LIKE ciu_data_type; INSERT INTO ciu_data_type_copy (id, loc_id, value3, value5, value8, value9, value10, value11) SELECT id, loc_id, value3, value5, value8, value9, value10, value11 FROM ciu_data_type; I think this would be more comparable to what you're seeing. I havent done this test in a stored function yet, nor have I tried it with a C client so far, so there is the chance that it is java/ jdbc that makes the insert so slow. I'll get to that test soon if there is any chance my theory makes sence. Just testing in psql with \timing should be fairly easy. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Advice about how to delete
On Jul 6, 2007, at 9:42 , Arnau wrote: I have the following scenario, I have users and groups where a user can belong to n groups, and a group can have n users. A user must belogn at least to a group. So when I delete a group I must check that there isn't any orphan. To do this I have something like that: IF v_count = 1 THEN DELETE FROM users WHERE user_id = result.user_id; v_deleted = v_deleted + 1; END IF; Am I right in reading that you're deleting any users that would be orphans? If so, you can just delete the orphans after rather than delete them beforehand (untested): -- delete user_group DELETE FROM user_groups WHERE user_group_id = p_group_id; -- delete users that don't belong to any group DELETE FROM users WHERE user_id IN ( SELECT user_id LEFT JOIN user_groups WHERE group_id IS NULL); This should execute pretty quickly. You don't need to loop over any results. Remember, SQL is a set-based language, so if you can pose your question in a set-based way, you can probably find a pretty good, efficient solution. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Slow join query
On Jun 22, 2007, at 13:32 , Tom Tamulewicz wrote: ( p.void_flag IS NULL OR p.void_flag = false ) Just a note: you can rewrite (a IS NULL or a = false) as (a IS NOT TRUE). Shouldn't affect performance, but might make your query easier to read. What's the EXPLAIN ANALYZE output for this query? When the query runs, the hard drive lights up for the duration. (I'm confused by this as 'top' reports only 24k of swap in use). My SUSE 9 test machine has 512 Meg of RAM with 300 Meg used by a Java app. Postmaster reports 56 Meg under top and has a 52 Meg segment under ipcs. I've played with the cache size, shared buffers, and OS shmmax with little change in the query performance. Q: Would this query benefit from using a view between these two tables? I doubt it, as views are just pre-parsed queries: no data is materialized for the view. Q: Any idea why the reported swap usage is so low, yet the query slams the drive? Is postgres not caching this data? If I run the query with the same arguments, it comes right back the second time. If I change the args and re-run, it goes back to the hard drive and takes 30-50 seconds. How much is cached depends on shared_buffers, I believe. If the result is still cached, that'd explain why running the query with the same arguments returns so quickly. You might see some improvement using a prepared query, as the server shouldn't have to reparse and replan the query. Of course, if you change the arguments, it can't use the result that's cached from the previous run. Take this all with an appropriate amount of salt. I'm learning about this, too. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Slow join query
[Please don't top post as it makes the discussion more difficult to follow.] On Jun 22, 2007, at 16:25 , Tom Tamulewicz wrote: The explain is as follows... EXPLAIN ANALYZE, please. (And for convenience, it helps if you include the query :) ) Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Slow indexscan
On Jun 20, 2007, at 9:02 , Mikko Partio wrote: Relevant facts: pg version 7.3.4 (yeah very old, we are upgrading asap) There have been many performance improvements—not to mention security and data-eating bug fixes—since then. Upgrading should be one of your highest priorities. And it may even fix the issue at hand! Index Scan using tbl_20070601_pkey on tbl_20070601 t1 (cost=0.00..365.13rows=13 width=137) (actual time= 120.83..10752.64 rows=539 loops=1) Something appears a bit off with your index, or at least the statistics Postgres is using to estimate it. It's estimating that the query will return 13 rows, but you're actually returning 539. Maybe there's some corruption in the index which is leading to both the performance issue you're seeing and the statistics issues. Have you tried REINDEX? Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] test / live environment, major performance difference
On Jun 12, 2007, at 8:32 , Christo Du Preez wrote: I have noticed that my server never uses indexing. No matter what I do. As an example I took a table with about 650 rows, having a parentid field with an index on parentid. EXPLAIN ANALYZE SELECT * FROM layertype where parentid = 300; The planner weighs the cost of the different access methods and choses the one that it believes is lowest in cost. An index scan is not always faster than a sequential scan. With so few rows, it's probably faster for the server to read the whole table rather than reading the index and looking up the corresponding row. If you want to test this, you can set enable_seqscan to false and try running your query again. http://www.postgresql.org/docs/8.2/interactive/runtime-config- query.html#RUNTIME-CONFIG-QUERY-ENABLE Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Weird 8.2.4 performance
On Jun 6, 2007, at 18:27 , Kurt Overberg wrote: select id from taskinstance where taskid in (select id from task where campaignid = 75); Now, I know this could (and should) be rewritten to not use the WHERE x IN () style, but this is actually a sub-query to a larger query. Granted, it won't explain why this particular query is slower in 8.2, but it shouldn't be to hard to drop in something like SELECT id FROM taskinstance NATURAL JOIN ( SELECT id AS taskid, campaignid FROM tasks) t WHERE campaignid = 75 AIUI, the planner can sometimes rewrite IN as a join, but I don't know whether or not that's is happening in this case. I'm guessing not as I see nested loops in the plans. (I'm a novice at reading plans, so take this with at least a teaspoon of salt. :) ) if I run the query again, it gets successively faster (50,000ms- 6000ms-27ms). Is this normal? If I change the campaignid from 75 to another number, it jumps back to 50,000ms, which leads me to believe that postgresql is somehow caching the results of the query and not figuring out a better way to run the query. As the query is repeated, the associated rows are probably already in memory, leading to the speedups you're seeing. -- 8.2 Recheck Cond: (taskinstance.taskid = task.id) - Bitmap Index Scan on taskid_taskinstance_key (cost=0.00..20.57 rows=556 width=0) (actual time=54.709..54.709 rows=196 loops=9) Index Cond: (taskinstance.taskid = task.id) -- 8.0 - Index Scan using taskid_taskinstance_key on taskinstance (cost=0.00..2152.28 rows=563 width=8) (actual time=0.012..0.832 rows=145 loops=11) Index Cond: (taskinstance.taskid = outer.id) I see that the row estimates in both of the query plans are off a little. Perhaps increasing the statistics would help? Also, you can see that 8.2 is using bitmap scans, which aren't available in 8.0. Perhaps try setting enable_bitmapscan off and running the query again to see if there's a performance difference. The weird thing is that on 8.2, I don't see any sequential scans taking place, it seems to be properly using the indexes. As an aside, whether the planner decides to use a sequential scan or an index has more to do with the particular query: indexes are not a guaranteed performance win. Hope this helps a bit. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Seq Scan
On Jun 1, 2007, at 11:48 , Tyler Durden wrote: I'm having some problems in performance in a simple select count(id) from Unrestricted count() (i.e., no WHERE clause) will perform a sequential scan. If you're looking for faster ways to store table row count information, please search the archives, as this has been discussed many times before. # explain select (id) from table_name; -[ RECORD 1 ] QUERY PLAN | Seq Scan on table_name (cost=0.00..8601.30 rows=266730 width=4) The query returns the id column value for each row in the table. The fastest way to do this is visiting every row., i.e., a sequential scan. Using an index would require (1) looking in the index and (2) looking up the corresponding row. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Very slow left outer join
On May 29, 2007, at 19:16 , Tyrrill, Ed wrote: - Hash Join (cost=361299.50..1054312.92 rows=34805 width=8) (actual time=1446.861..368723.597 rows=2789 loops=1) Hash Cond: (outer.record_id = inner.record_id) - Seq Scan on backupobjects (cost=0.00..429929.79 rows=13136779 width=8) (actual time=5.165..359168.216 rows=13136779 loops=1) - Hash (cost=360207.21..360207.21 rows=436915 width=8) (actual time=820.979..820.979 rows=2789 loops=1) - Bitmap Heap Scan on backup_location (cost=3831.20..360207.21 rows=436915 width=8) (actual time=797.463..818.269 rows=2789 loops=1) Recheck Cond: (backup_id = 1071) - Bitmap Index Scan on backup_location_bid (cost=0.00..3831.20 rows=436915 width=0) (actual time=59.592..59.592 rows=2789 loops=1) Off the cuff, when was the last time you vacuumed or ran ANALYZE? Your row estimates look off by a couple orders of magnitude. With up- to-date statistics the planner might do a better job. As for any other improvements, I'll leave that to those that know more than I. :) Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Vacuum v/s Autovacuum
On Jan 18, 2007, at 22:24 , Gauri Kanekar wrote: is autovacuum similar to vacuum full analyse verbose. http://www.postgresql.org/docs/8.2/interactive/routine- vacuuming.html#AUTOVACUUM Apparently, no FULL, no VERBOSE (which is only really useful if you want to see the results, not for routine maintenance). Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Version Change
On Jan 18, 2007, at 13:43 , Gauri Kanekar wrote: Can anybody suggest some comprehensive test for version change from 8.1.3 to 8.2 http://www.postgresql.org/docs/8.2/interactive/release.html Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Version Change
On Jan 18, 2007, at 13:56 , Michael Glaesemann wrote: On Jan 18, 2007, at 13:43 , Gauri Kanekar wrote: Can anybody suggest some comprehensive test for version change from 8.1.3 to 8.2 http://www.postgresql.org/docs/8.2/interactive/release.html Sorry, I misread your request as a list of version changes. You could parse the result of SELECT version(); to test what version the server is, if that's what you're asking. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Version Change
Please reply to the list so that others may contribute and benefit from the discussion. On Jan 18, 2007, at 14:19 , Gauri Kanekar wrote: i want some comprehensive tests, to identify wheather shifiting from 8.1.3 to 8.2 will be advantageous. I think it depends on your installation and use for PostgreSQL. PostgreSQL is used for many different types of projects which have different needs. I don't think it would be possible to put together some sort of comprehensive test suite that would answer your question. What I can recommend is benchmark common and performance critical tasks for your current 8.1.3 installation and then compare the results with those same benchmarks run against 8.2. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] New to PostgreSQL, performance considerations
On Dec 14, 2006, at 14:44 , Tom Lane wrote: The pgbench app itself becomes the bottleneck at high transaction rates. Awhile back I rewrote it to improve its ability to issue commands concurrently, but then desisted from submitting the changes --- if we change the app like that, future numbers would be incomparable to past ones, which sort of defeats the purpose of a benchmark no? At the same time, if the current pgbench isn't the tool we want to use, is this kind of backward comparison going to hinder any move to improve it? It sounds like there's quite a bit of room for improvement in pg_bench, and in my opinion we should move forward to make an improved tool, one that measures what we want to measure. And while comparison with past results might not be possible, there remains the possibility of rerunning the improved pgbench on previous systems, I should think. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Index ignored on column containing mostly 0 values
On Oct 31, 2006, at 13:04 , Leif Mortenson wrote: Hello, I have been having a problem with the following query ignoring an index on the foos.bar column. SELECT c.id FROM foos c, bars r WHERE r.id != 0 AND r.modified_time '2006-10-20 10:00:00.000' AND r.modified_time = '2006-10-30 15:20:00.000' AND c.bar = r.id snip / Having a column containing large numbers of null or 0 values seems fairly common. Is there way to tell Postgres to create an index of all values with meaning. Ie all non-0 values? None that I could find. Try create index foo_non_zero_bar_index on foos(bar) where bar 0; Take a look on the docs on partial indexes for more information. http://www.postgresql.org/docs/current/interactive/indexes-partial.html Hope this helps. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] [Fwd: Delivery Status Notification (Failure)]
On Jul 12, 2006, at 11:39 , Craig A. James wrote: I can't find an address to complain about the mailing list itself, so apologies but I'm posting directly to this list. Every time I post to this group, I get returned mails about OTHER subscribers' invalid accounts, like the one below. Is this when you're replying to a post or creating a new post? If the former, and you're using reply-to-all, you'll be sending one message to the list and another directly to the poster of the message you're responding to. The directly sent message is outside of the list entirely, so any returned mail is also outside of the list. I've seen this happen occasionally myself. Could this be what you're seeing? AFAICT, such messages sent to the list *do* get filtered out. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Performance of DOMAINs
since I'd like to have/create a truly case-insensitive text type (ITEXT anyone?). I haven't seen it mentioned in this thread yet, but have you looked at citext? http://gborg.postgresql.org/project/citext/projdisplay.php I don't have any experience with it, but perhaps it can do what you're looking for. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] UNSUBSCRIBE
On May 10, 2006, at 14:42 , Tom Lane wrote: Chris [EMAIL PROTECTED] writes: Maybe :) The php-general list has To unsubscribe, visit: http://www.php.net/unsub.php at the bottom of every email, and there are still random unsubscribe requests.. That will *always* happen. Just human nature and the numbers of subscribers. However, a one-liner that either points to the webpage for unsubscribing (probably easiest) or a brief description on how to unsubscribe (To unsubscribe, send an email to [EMAIL PROTECTED] with body unsub pgsql-performance (without quotes)) may intercept a few more. Is there a way to configure Majordomo to make even easier to unsubscribe? Just sending to pgsql- [EMAIL PROTECTED] or some such? I've seen other mailing lists that do this. Requiring a specific command (what's the command? in the subject or the body?) is one more place a person can make a mistake. (I've recently switched mail accounts and unsubbed/ subbed from the lists I'm on. This latter style does make it a lot easier.) (And are there mail readers out there that can pick those subscribe/ unsubscribe headers from the list emails? Now *that'd* be sweet.) Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] PostgreSQL VACCUM killing CPU
I have got such problem. Im running Postgresql 7.3.2 on Linux 2.6.13. Also, you should seriously consider upgrading. 8.1.3 is the current PostgreSQL release. If you must remain on 7.3, at least upgrade to 7.3.14, which contains *many* bugfixes. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] PostgreSQL VACCUM killing CPU
I have got such problem. Im running Postgresql 7.3.2 on Linux 2.6.13. Also, you should seriously consider upgrading. 8.1.3 is the current PostgreSQL release. If you must remain on 7.3, at least upgrade to 7.3.14, which contains many bugfixes. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] FOREIGN KEYS vs PERFORMANCE
On Apr 12, 2006, at 4:13 , Rodrigo Sakai wrote: I think this is an old question, but I want to know if it really is well worth to not create some foreign keys an deal with the referential integrity at application-level? If I had to choose between one or the other, I'd leave all referential integrity in the database and deal with the errors thrown when referential integrity is violated in the application. PostgreSQL is designed to handle these kinds of issues. Anything you code in your application is more likely to contain bugs or miss corner cases that would allow referential integrity to be violated. PostgreSQL has been pounded on for years by a great many users and developers, making the likelihood of bugs still remaining much smaller. Of course, you can add some referential integrity checks in your application code, but those should be in addition to your database- level checks. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] nested query on last n rows of huge table
On Feb 24, 2006, at 23:13 , jcfischer wrote: Is there a way to limit the expensive query to only those last 1000 (or whatever) results? I have tried to nest SELECTS but my SQL-fu is to limited to get anything through the SQL processor :-) The basics of a subquery are: SELECT expensive query FROM ( SELECT * FROM table ORDER eventtime DESC LIMIT 1000 ) as most_recent_1000 Don't know enough about the other parts, but hopefully this can get you started. :) Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] indexes on primary and foreign keys
On Jan 12, 2006, at 9:36 , K C Lau wrote: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index cities_pkey for table cities Is there a way to suppress this notice when I create tables in a script? Set[1] your log_min_messages to WARNING or higher[2]. [1](http://www.postgresql.org/docs/current/interactive/sql-set.html) [2](http://www.postgresql.org/docs/current/interactive/runtime-config- logging.html#RUNTIME-CONFIG-LOGGING-WHEN) Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Improving Inner Join Performance
On Jan 6, 2006, at 18:21 , Andy wrote: Yes I have indexes an all join fields. The tables have around 30 columns each and around 100k rows. The database is vacuumed every hour. Just to chime in, VACUUM != VACUUM ANALYZE. ANALYZE is what updates database statistics and affects query planning. VACUUM alone does not do this. Do you have an index on report.id_order ? Try creating an index for it if not and run a vacuum analyze on the table to see if it gets rid of the sequence scan in the plan. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] What is the max number of database I can create in an instance of pgsql?
On Nov 19, 2005, at 12:24 , anon permutation wrote: However, what is the max number of database I can create before performance goes down? Assuming each database is performing well alone, how would putting 10-20 of them together in one instance affect postgres? In terms of getting a new server for this project, how do I gauge how powerful of a server should I get? I'm sure those wiser than me will chime in with specifics. I think you should be think of usage not in terms of number of databases but in terms of connections rates, database size (numbers of tables and tuples) and the types of queries that will be run. While there may be a little overhead in from having a number of databases in the cluster, I think this is probably going to be insignificant in comparison to these other factors. A better idea of what the usage will guide you in choosing your hardware. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Checking = with timestamp field is slow
On Nov 5, 2004, at 4:16 PM, Antony Paul wrote: where today::date = '2004-11-05'; This is the only condition in the query. There is a btree index on the column today. Is there any way to optimise it. I'm sure others out there have better ideas, but you might want to try where current_date = date '2004-11-05' Might not make a difference at all, but perhaps PostgreSQL is coercing both values to timestamp or some other type as you're only providing a string to compare to a date. Then again, it might make no difference at all. My 1 cent. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables
On Sep 15, 2004, at 8:32 AM, Simon Riggs wrote: The partitions are just tables, so no need for other management tools. Oracle treats the partitions as sub-tables, so you need a range of commands to add, swap etc the partitions of the main table. I guess a set of tools that emulates that functionality would be generically a good thing, if you can see a way to do that. Oracle partitions were restricted in only allowing a single load statement into a single partition at any time, whereas multiple COPY statements can access a single partition table on PostgreSQL. How does this compare to DB2 partitioning? Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] NUMERIC x VARCHAR
On Aug 11, 2004, at 4:27 PM, Er Galvão Abbott wrote: It will. As I've said I wont be storing any symbols. It won't store leading zeros, however. This may or may not be an issue for you. test=# create table tel (name_id integer not null, tel_numeric numeric(15) not null, tel_varchar varchar(15) not null); CREATE TABLE test=# insert into tel (name_id, tel_numeric, tel_varchar) values (1,012345678911234, '012345678911234'); INSERT 17153 1 test=# select * from tel; name_id | tel_numeric | tel_varchar -++- 1 | 12345678911234 | 012345678911234 (1 row) I would do as another poster suggested: create a telephone number domain as text with the check constraints you desire. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
