pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Created Index is not used 4+ messages / 3 participants [nested] [flat]
* Created Index is not used @ 2006-02-23 12:35 Kjeld Peters <kjeld.peters@profiling-company.de> 2006-02-23 12:46 ` Re: Created Index is not used Markus Schaber <schabi@logix-tt.com> 2006-02-23 13:03 ` Re: Created Index is not used Ragnar <gnari@hive.is> 0 siblings, 2 replies; 4+ messages in thread From: Kjeld Peters @ 2006-02-23 12:35 UTC (permalink / raw) To: pgsql-performance Select and update statements are quite slow on a large table with more than 600,000 rows. The table consists of 11 columns (nothing special). The column "id" (int8) is primary key and has a btree index on it. The following select statement takes nearly 500ms: SELECT * FROM table WHERE id = 600000; A prepending "EXPLAIN" to the statement reveals a seq scan: EXPLAIN SELECT * FROM table WHERE id = 600000; "Seq Scan on table (cost=0.00..15946.48 rows=2 width=74)" " Filter: (id = 600000)" I tried a full vacuum and a reindex, but had no effect. Why is PostgreSQL not using the created index? Or is there any other way to improve performance on this query? The PostgreSQL installation is an out of the box installation with no further optimization. The server is running SUSE Linux 9.1, kernel 2.6.4-52-smp. (Quad Xeon 2.8GHz, 1GB RAM) SELECT version(); "PostgreSQL 7.4.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.3 (SuSE Linux)" Thanks for any hints, Kjeld ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Created Index is not used 2006-02-23 12:35 Created Index is not used Kjeld Peters <kjeld.peters@profiling-company.de> @ 2006-02-23 12:46 ` Markus Schaber <schabi@logix-tt.com> 2006-02-23 14:09 ` Re: Created Index is not used Kjeld Peters <kjeld.peters@profiling-company.de> 1 sibling, 1 reply; 4+ messages in thread From: Markus Schaber @ 2006-02-23 12:46 UTC (permalink / raw) To: Kjeld Peters <kjeld.peters@profiling-company.de>; +Cc: pgsql-performance Hi, Kjeld, Kjeld Peters wrote: > Select and update statements are quite slow on a large table with more > than 600,000 rows. The table consists of 11 columns (nothing special). > The column "id" (int8) is primary key and has a btree index on it. > > The following select statement takes nearly 500ms: > > SELECT * FROM table WHERE id = 600000; Known issue which is fixed in 8.X servers, postgreSQL sees your 600000 as int4 literal and does not grasp that the int8 index works for it. SELECT * FROM table WHERE id = 600000::int8; should do it. > SELECT version(); > "PostgreSQL 7.4.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.3 > (SuSE Linux)" Btw, you should update to 7.4.12, there are importand bug fixes and it is upgradable "in place", without dumping and reloading the database. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Created Index is not used 2006-02-23 12:35 Created Index is not used Kjeld Peters <kjeld.peters@profiling-company.de> 2006-02-23 12:46 ` Re: Created Index is not used Markus Schaber <schabi@logix-tt.com> @ 2006-02-23 14:09 ` Kjeld Peters <kjeld.peters@profiling-company.de> 0 siblings, 0 replies; 4+ messages in thread From: Kjeld Peters @ 2006-02-23 14:09 UTC (permalink / raw) To: Markus Schaber <schabi@logix-tt.com>; +Cc: pgsql-performance Hi Markus, first of all thanks for your quick reply! Markus Schaber wrote: > Kjeld Peters wrote: >>Select and update statements are quite slow on a large table with more >>than 600,000 rows. The table consists of 11 columns (nothing special). >>The column "id" (int8) is primary key and has a btree index on it. >> >>The following select statement takes nearly 500ms: >> >>SELECT * FROM table WHERE id = 600000; > > > Known issue which is fixed in 8.X servers, postgreSQL sees your 600000 > as int4 literal and does not grasp that the int8 index works for it. > > SELECT * FROM table WHERE id = 600000::int8; > > should do it. After I appended "::int8" to the query, selecting the table takes only 40-50ms. That's a great performance boost! > Btw, you should update to 7.4.12, there are importand bug fixes and it > is upgradable "in place", without dumping and reloading the database. I guess I'll test an upgrade to version 8.1. Thanks again for your and Ragnar's help! Kjeld ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Created Index is not used 2006-02-23 12:35 Created Index is not used Kjeld Peters <kjeld.peters@profiling-company.de> @ 2006-02-23 13:03 ` Ragnar <gnari@hive.is> 1 sibling, 0 replies; 4+ messages in thread From: Ragnar @ 2006-02-23 13:03 UTC (permalink / raw) To: Kjeld Peters <kjeld.peters@profiling-company.de>; +Cc: pgsql-performance On fim, 2006-02-23 at 13:35 +0100, Kjeld Peters wrote: > Select and update statements are quite slow on a large table with more > than 600,000 rows. The table consists of 11 columns (nothing special). > The column "id" (int8) is primary key and has a btree index on it. > > The following select statement takes nearly 500ms: > > SELECT * FROM table WHERE id = 600000; > > A prepending "EXPLAIN" to the statement reveals a seq scan: > > EXPLAIN SELECT * FROM table WHERE id = 600000; > > "Seq Scan on table (cost=0.00..15946.48 rows=2 width=74)" > " Filter: (id = 600000)" > I tried a full vacuum and a reindex, but had no effect. Why is > PostgreSQL not using the created index? try one of: SELECT * FROM table WHERE id = '600000'; SELECT * FROM table WHERE id = 600000::int8; PostgreSQL 8+ gnari ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-02-23 14:09 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2006-02-23 12:35 Created Index is not used Kjeld Peters <kjeld.peters@profiling-company.de> 2006-02-23 12:46 ` Markus Schaber <schabi@logix-tt.com> 2006-02-23 14:09 ` Kjeld Peters <kjeld.peters@profiling-company.de> 2006-02-23 13:03 ` Ragnar <gnari@hive.is>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox