[GENERAL] Understanding Execution Plans
I'm in the process of migrating a web application from a dedicated server to VPS Hosting (Slicehost). During the test phase I've spotted a huge performance advantage for the old dedicated server for some queries and I need some help interpreting the execution plans. Plan 1 - Dedicated Server Athlon 64 5000 - Debian 5.0 - 4GB Ram - 150 GB off the shelf Sata HD --- Limit (cost=16574.23..16574.28 rows=20 width=119) (actual time=466.140..466.158 rows=3 loops=1) -> Sort (cost=16574.23..16574.29 rows=24 width=119) (actual time=466.135..466.141 rows=3 loops=1) Sort Key: c.total_achievement_points Sort Method: quicksort Memory: 25kB -> Nested Loop Left Join (cost=86.99..16573.68 rows=24 width=119) (actual time=139.903..466.064 rows=3 loops=1) -> Nested Loop Left Join (cost=86.99..16414.84 rows=24 width=108) (actual time=139.865..465.957 rows=3 loops=1) Join Filter: (c.class_id = classes.id) -> Nested Loop Left Join (cost=86.99..16385.44 rows=24 width=86) (actual time=139.846..465.773 rows=3 loops=1) -> Nested Loop Left Join (cost=86.99..16186.44 rows=24 width=73) (actual time=139.826..448.932 rows=3 loops=1) Join Filter: (c.race_id = races.id) -> Nested Loop Left Join (cost=86.99..16157.04 rows=24 width=60) (actual time=139.775..448.750 rows=3 loops=1) Join Filter: (c.faction_id = factions.id) -> Bitmap Heap Scan on characters c (cost=86.99..16128.72 rows=24 width=36) (actual time=139.721..448.574 rows=3 loops=1) Recheck Cond: (realm_id = 227) Filter: ((total_achievement_points > 0) AND (level = 80)) -> Bitmap Index Scan on characters_realm_id (cost=0.00..86.98 rows=4597 width=0) (actual time=26.076..26.076 rows=2028 loops=1) Index Cond: (realm_id = 227) -> Seq Scan on faction_categories factions (cost=0.00..1.08 rows=8 width=28) (actual time=0.008..0.024 rows=8 loops=3) -> Seq Scan on races (cost=0.00..1.10 rows=10 width=17) (actual time=0.004..0.025 rows=10 loops=3) -> Index Scan using guilds_pkey on guilds g (cost=0.00..8.28 rows=1 width=17) (actual time=5.598..5.599 rows=1 loops=3) Index Cond: (c.guild_id = g.id) -> Seq Scan on classes (cost=0.00..1.10 rows=10 width=26) (actual time=0.005..0.027 rows=10 loops=3) -> Index Scan using realms_pkey on realms r (cost=0.00..6.61 rows=1 width=15) (actual time=0.018..0.022 rows=1 loops=3) Index Cond: ((r.id = 227) AND (c.realm_id = r.id)) Total runtime: 466.829 ms (25 rows) Plan 2 - Slicehost VPS 512 - Quadcore Opteron Xen VPS - Debian 5.0 - 512MB RAM - Raid 10 Storage on Host --- Limit (cost=17088.31..17088.36 rows=20 width=119) (actual time=5620.050..5620.050 rows=3 loops=1) -> Sort (cost=17088.31..17088.37 rows=24 width=119) (actual time=5620.050..5620.050 rows=3 loops=1) Sort Key: c.total_achievement_points Sort Method: quicksort Memory: 25kB -> Nested Loop Left Join (cost=92.10..17087.76 rows=24 width=119) (actual time=2016.018..5620.050 rows=3 loops=1) -> Nested Loop Left Join (cost=92.10..16888.77 rows=24 width=106) (actual time=2016.018..5588.049 rows=3 loops=1) -> Nested Loop Left Join (cost=92.10..16729.92 rows=24 width=95) (actual time=2016.018..5588.049 rows=3 loops=1) Join Filter: (c.class_id = classes.id) -> Nested Loop Left Join (cost=92.10..16700.52 rows=24 width=73) (actual time=2016.018..5588.049 rows=3 loops=1) Join Filter: (c.race_id = races.id) -> Nested Loop Left Join (cost=92.10..16671.12 rows=24 width=60) (actual time=2016.018..5588.049 rows=3 loops=1) Join Filter: (c.faction_id = factions.id) -> Bitmap Heap Scan on characters c (cost=92.10..16642.80 rows=24 width=36) (actual time=2016.018..5588.049 rows=3 loops=1) Recheck Cond: (realm_id = 227) Filter: ((total_achievement_points > 0) AND (level = 80)) -> Bitmap Index Scan on characters_realm_id (cost=0.00..92.09 rows=4743 width=0) (actual time=76.001..76.001 rows=2033 loops=1) Index Cond: (realm_id = 227) -> Seq Scan on faction_categories
[GENERAL] Indexing problem with OFFSET LIMIT
Hello I have problem in my applications and don't know how to fix it. This is the table and one of the indexes: CREATE TABLE foo ( id serial NOT NULL, foo_name character varying(100), realm_id integer ... and about 50 other columns ) CREATE INDEX idx_foo_name_realm ON foo USING btree (realm_id, foo_name); Table foo contains about 8 Million Rows. The problem: Consider this query: SELECT * FROM foo WHERE realm_id = 228 order by foo_name LIMIT 200 OFFSET 15000 And it's execution plan: "Limit (cost=57527.13..58294.16 rows=200 width=575) (actual time=182.302..184.971 rows=200 loops=1)" " -> Index Scan using idx_foo_name_realm on foo (cost=0.00..62159.98 rows=16208 width=575) (actual time=0.085..166.861 rows=15200 loops=1)" "Index Cond: (realm_id = 228)" "Total runtime: 185.591 ms" And now look at this: SELECT * FROM foo WHERE realm_id = 228 order by foo_name LIMIT 200 OFFSET 15999 "Limit (cost=59601.92..59602.42 rows=200 width=575) (actual time=1069.759..1072.310 rows=200 loops=1)" " -> Sort (cost=59561.92..59602.44 rows=16208 width=575) (actual time=929.948..1052.620 rows=16199 loops=1)" "Sort Key: foo_name" "Sort Method: external merge Disk: 8984kB" "-> Bitmap Heap Scan on foo (cost=306.69..54270.62 rows=16208 width=575) (actual time=9.612..235.902 rows=21788 loops=1)" " Recheck Cond: (realm_id = 228)" " -> Bitmap Index Scan on foo_realm_id (cost=0.00..302.64 rows=16208 width=0) (actual time=8.733..8.733 rows=21810 loops=1)" "Index Cond: (realm_id = 228)" "Total runtime: 1084.706 ms" Execution time increases tenfold because postgres stopped using the index. Can anybody explain to me what's going on and what can be done? Is this a memory problem?
[GENERAL] Update taking forever
The below statement is now running for 18 hours on a table with ~8 Million Rows, no triggers no fancy stuff. The database is otherwise performing very well and the server is a development server that's currently idle except for the update statement. Any suggestions why it takes so long to update a couple million rows? update characters set last_update = null Note: 'last_update' is a timestamp column but the column does not seem to influence the time it takes to complete the update. -- Oliver
[GENERAL] A couple PostgreSQL 8.3 related Fulltext-Search questions
This is my table: CREATE TABLE item_names ( item_name character varying(255) NOT NULL, culture_id integer NOT NULL, item_id integer NOT NULL, ft_idx_config regconfig, CONSTRAINT pk_item_names PRIMARY KEY (item_id, culture_id) ) Basically the table stores strings with varying language (actually en, de, fr, es) in the item_name column and implements the language association using the culture_id column. I would like to run fulltext queries against item_name + culture_id. The ft_idx_config shall be used to provide the appropriate ts_vector config for culture_id. Question 1: The manual mentions ( http://www.postgresql.org/docs/8.3/interactive/textsearch-tables.html) that there's an alternative method to create a GIN index. "It is possible to set up more complex expression indexes wherein the configuration name is specified by another column, e.g.: CREATE INDEX pgweb_idx ON pgweb USING gin(to_tsvector(config_name, body)); A little further down it is also mentioned that indexes can even concatenate columns. Unfortunately I do not seem to be able combine both forms into: CREATE INDEX ft_idx_item_name ON item_names USING gin(to_tsvector(ft_idx_config, item_name || culture_id)); Which, unless I am mistaken, I would need to execute a fulltext query for a specific language. Is there a way to solve this or do I need to change my table layout? Question 2: When trying to insert rows into this table (using IBatis): INSERT INTO item_names (item_id, item_name, ft_idx_config, culture_id) VALUES(#item_id#, #item_name#, #ft_idx_config#, #culture_id# ) I'm receiving this error: ERROR: column "ft_idx_config" is of type regconfig but expression is of type character varying Do I have to cast the parameter?
[GENERAL] TSearch2 Migration Guide from 8.2 to 8.3
Hi I run a site with several MediaWiki installations all running on PostgreSQL 8.2.5 utilizing TSearch2. Is there something like a Migration Guide from 8.2to 8.3 for tsearch2 users? Cheers Oliver
