Re: [PERFORM] pg_trgm performance
On Sat, Feb 24, 2007 at 02:04:36AM +0100, Guillaume Smet wrote: Could you post EXPLAIN ANALYZE for both queries (after 2 or 3 runs)? GIST version, short: amarok=# explain analyze select count(*) from tags where title % 'foo'; QUERY PLAN -- Aggregate (cost=147.84..147.85 rows=1 width=0) (actual time=16.873..16.875 rows=1 loops=1) - Bitmap Heap Scan on tags (cost=4.59..147.74 rows=41 width=0) (actual time=16.828..16.850 rows=7 loops=1) Recheck Cond: (title % 'foo'::text) - Bitmap Index Scan on trgm_idx (cost=0.00..4.58 rows=41 width=0) (actual time=16.818..16.818 rows=7 loops=1) Index Cond: (title % 'foo'::text) Total runtime: 16.935 ms (6 rows) GiN version, short: amarok=# explain analyze select count(*) from tags where title % 'foo'; QUERY PLAN --- Aggregate (cost=151.89..151.90 rows=1 width=0) (actual time=30.197..30.199 rows=1 loops=1) - Bitmap Heap Scan on tags (cost=8.64..151.79 rows=41 width=0) (actual time=5.555..30.157 rows=7 loops=1) Filter: (title % 'foo'::text) - Bitmap Index Scan on trgm_idx (cost=0.00..8.63 rows=41 width=0) (actual time=2.857..2.857 rows= loops=1) Index Cond: (title % 'foo'::text) Total runtime: 30.292 ms (6 rows) GIST version, medium: amarok=# explain analyze select count(*) from tags where title % 'chestnuts roasting on an 0pen fire'; QUERY PLAN Aggregate (cost=147.84..147.85 rows=1 width=0) (actual time=216.149..216.151 rows=1 loops=1) - Bitmap Heap Scan on tags (cost=4.59..147.74 rows=41 width=0) (actual time=216.135..216.137 rows=1 loops=1) Recheck Cond: (title % 'chestnuts roasting on an 0pen fire'::text) - Bitmap Index Scan on trgm_idx (cost=0.00..4.58 rows=41 width=0) (actual time=216.124..216.124 rows=1 loops=1) Index Cond: (title % 'chestnuts roasting on an 0pen fire'::text) Total runtime: 216.214 ms (6 rows) amarok=# explain analyze select count(*) from tags where title % 'chestnuts roasting on an 0pen fire'; QUERY PLAN - Aggregate (cost=151.89..151.90 rows=1 width=0) (actual time=156.310..156.312 rows=1 loops=1) - Bitmap Heap Scan on tags (cost=8.64..151.79 rows=41 width=0) (actual time=156.205..156.299 rows=1 loops=1) Filter: (title % 'chestnuts roasting on an 0pen fire'::text) - Bitmap Index Scan on trgm_idx (cost=0.00..8.63 rows=41 width=0) (actual time=155.748..155.748 rows=36 loops=1) Index Cond: (title % 'chestnuts roasting on an 0pen fire'::text) Total runtime: 156.376 ms (6 rows) GIST version, long: amarok=# explain analyze select count(*) from tags where title % 'Donaueschingen (Peter Kruders Donaudampfschifffahrtsgesellschaftskapitänskajütenremix)'; ; QUERY PLAN -- Aggregate (cost=147.84..147.85 rows=1 width=0) (actual time=597.115..597.117 rows=1 loops=1) - Bitmap Heap Scan on tags (cost=4.59..147.74 rows=41 width=0) (actual time=597.102..597.104 rows=1 loops=1) Recheck Cond: (title % 'Donaueschingen (Peter Kruders Donaudampfschifffahrtsgesellschaftskapitänskajütenremix)'::text) - Bitmap Index Scan on trgm_idx (cost=0.00..4.58 rows=41 width=0) (actual time=597.093..597.093 rows=1 loops=1) Index Cond: (title % 'Donaueschingen (Peter Kruders Donaudampfschifffahrtsgesellschaftskapitänskajütenremix)'::text) Total runtime: 597.173 ms (6 rows) GiN version, long: amarok=# explain analyze select count(*) from tags where title % 'Donaueschingen (Peter Kruders Donaudampfschifffahrtsgesellschaftskapitänskajütenremix)'; ; QUERY PLAN
Re: [PERFORM] which Xeon processors don't have the context switching problem
Geoffrey wrote: Guillaume Smet wrote: On 2/23/07, Geoffrey [EMAIL PROTECTED] wrote: As I've heard. We're headed for 8 as soon as possible, but until we get our code ready, we're on 7.4.16. You should move to at least 8.1 and possibly 8.2. It's not a good idea to upgrade only to 8 IMHO. When I said 8, I meant whatever the latest greatest 8 is. Right now, that looks like 8.2.3. No. The latest version of 8.2 is 8.2.3, there is also 8.1 which is at 8.1.8 and 8.0 which is at 8.0.12. They are all different *major* releases. IMO, nobody should be running anything less than 8.1.8. Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(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
[PERFORM] Two hard drives --- what to do with them?
Say that I have a dual-core processor (AMD64), with, say, 2GB of memory to run PostgreSQL 8.2.3 on Fedora Core X. I have the option to put two hard disks (SATA2, most likely); I'm wondering what would be the optimal configuration from the point of view of performance. I do have the option to configure it in RAID-0, but I'm sort of reluctant; I think there's the possibility that having two filesystems that can be accessed truly simultaneously can be more beneficial. The question is: does PostgreSQL have separate, independent areas that require storage such that performance would be noticeably boosted if the multiple storage operations could be done simultaneously? Notice that even with RAID-0, the twice the performance may turn into an illusion --- if the system requires access from distant areas of the disk (distant as in many tracks apart), then the back-and-forth travelling of the heads would take precedence over the doubled access speed ... Though maybe it depends on whether accesses are in small chunks (in which case the cache of the hard disks could take precedence). Coming back to the option of two independent disks --- the thing is: if it turns out that two independent disks are a better option, how should I configure the system and the mount points? And how would I configure PostgreSQL to take advantage of that? Advice, anyone? Thanks, Carlos -- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Two hard drives --- what to do with them?
Carlos Moreno [EMAIL PROTECTED] writes: The question is: does PostgreSQL have separate, independent areas that require storage such that performance would be noticeably boosted if the multiple storage operations could be done simultaneously? The standard advice in this area is to put pg_xlog on a separate spindle; although that probably is only important for update-intensive applications. You did not tell us anything about your application... regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Two hard drives --- what to do with them?
On Feb 25, 2007, at 04:39 , Carlos Moreno wrote: I do have the option to configure it in RAID-0, but I'm sort of reluctant; I think there's the possibility that having two filesystems that can be accessed truly simultaneously can be more beneficial. The question is: does PostgreSQL have separate, independent areas that require storage such that performance would be noticeably boosted if the multiple storage operations could be done simultaneously? Putting the WAL (aka pg_xlog) on a separate disk will take some load off your main database disk. See http://www.varlena.com/GeneralBits/ Tidbits/perf.html for this. It is also possible to put individual tables and/or indexes on separate disks by using tablespaces: For example, an index which is very heavily used can be placed on a very fast, highly available disk, such as an expensive solid state device. At the same time a table storing archived data which is rarely used or not performance critical could be stored on a less expensive, slower disk system. (http://www.postgresql.org/docs/8.2/interactive/manage-ag- tablespaces.html) In both cases, the performance benefits tend to be relative to the amount of write activity you experience, and the latter solution assumes you know where the hotspots are. If you have two tables that see continuous, intense write activity, for example, putting each on a separate disk Alexander. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
