============================================================================
POSTGRESQL BUG REPORT
============================================================================
System Configuration --------------------- Architecture (example: Intel Pentium) : Intel Pentium III
Operating System (example: Linux 2.0.26 ELF) : FreeBSD 4.7
PostgreSQL version (example: PostgreSQL-7.3.2): PostgreSQL-7.3.2
Compiler used (example: gcc 2.95.2) : gcc 2.95.4
Please enter a FULL description of your problem: ------------------------------------------------
Table "public.server"
Column | Type | Modifiers
----------------+------------------+--------------------------------------------------
rec_id | bigint | not null default nextval('next_server_id'::text)
....
Indexes: server_pkey primary key btree (rec_id),
...
Filed rec_id was upgraded from serial to serial8 by ALTER TABLE ... commands. After this database was vacuumed, reindexer and analyzed.
After this performance get low dramaticaly. This due to foe queryes with WHERE on filed rec_id with value without quotas do not use index server_pkey.
this query do not use server_pkey index and it too slow.
search=# explain analyze select * from server where parent=12906;
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Seq Scan on server (cost=0.00..136.11 rows=1 width=91) (actual time=2039.21..2039.21 rows=0 loops=1)
Filter: (parent = 12906)
Total runtime: 2039.62 msec
(3 rows)
but this query use server_pkey and it fast
search=# explain analyze select * from server where parent='12906';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Index Scan using srv_parent on server (cost=0.00..6.04 rows=1 width=91) (actual time=199.96..199.96 rows=0 loops=1)
Index Cond: (parent = 12906::bigint)
Total runtime: 200.12 msec
(3 rows)
-- Maxim Zakharov http://www.maxime.net.ru/ Sochi, Russia http://sochi.org.ru/
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
