'Interesting' prepared statement slowdown on large table join

Hello everyone,

I have the following scenario:
There's a web service that updates some information in two tables,
every 5 minutes.
In order to do this it will issue a select on the tables, get some
data, think about it, and then update it if necessary.

Sometimes - about once every two weeks, I think, it will start using
an extremely inefficient plan where it will loop on many results from
the large table instead of getting the few results from small table and
looping on those.
The difference in performance is devastating - from 18 ms to 10-20
seconds, and of course drags everything down.
The situation will usually not resolve itself - but it will resolve
after i run "ANALYZE party; ANALYZE big_table" about... 3-5 times.
Interesting.

When the problem is occuring, it is completely reproducible using
local psql - thus probably not a connector issue.
I have tried to reconnect and to re-prepare the statement to allow it
to choose a new plan after the 'first' analyze, but it didn't help.
I have tried to increase ANALYZE statistics target on party_id (as the
join field) on both tables to 300, but it doesn't appear to help (not
even with the frequency of incidents).

The select is as follows:
prepare ps(varchar,varchar,varchar) as select party.party_id from party,
big_table where external_id = $1 and party.party_id = big_table.party_id
and attr_name = $2 and attr_value = $3;
PREPARE
execute ps('13','GroupId','testshop');
party_id
----------
659178

The query will always return exactly one row.

I hope this is enough information to start a discussion on how to avoid
this. The only reliable solution we've come up with so far is to split
selects and do the join in Java, but this seems like a very unorthodox
solution and could cause other trouble down the road.

Thank you in advance,
Andrei Prodan
Systems Administator

testdb=# select count(1) from party where external_id='13';
count
-------
4
(1 row)
testdb=# select count(1) from big_table where attr_name='GroupId';
count
---------
1025867
(1 row)

testdb=# select count(1) from big_table where attr_value='testshop';
count
--------
917704
(1 row)

Table party:
Rows: 1.8M
Table size: 163 MB
Indexes size: 465 MB

Table big_table:
- Frequently updated
Rows: 7.2M
Table size: 672 MB
Indexes size: 1731 MB

GOOD PLAN:
testdb=# explain analyze execute ps('13','GroupId','testshop');
QUERY
PLAN

------------------------------------------------------------------------
-----------------------------
--------------------------------------
Nested Loop (cost=0.00..19.11 rows=1 width=7) (actual
time=2.662..18.388 rows=1 loops=1)
-> Index Scan using partyext_id_idx on party (cost=0.00..8.47
rows=1 width=7) (actual time=2.439
..2.495 rows=4 loops=1)
Index Cond: ((external_id)::text = ($1)::text)
-> Index Scan using pk_big_table on big_table (cost=0.00..10.62
rows=1 width=7) (act ual time=3.972..3.972 rows=0 loops=4)
Index Cond: (((big_table.party_id)::text =
(party.party_id)::text) AND ((party_attribu te.attr_name)::text =
($2)::text))
Filter: ((big_table.attr_value)::text = ($3)::text) Total
runtime: 18.484 ms
(7 rows)

BAD PLAN:
testdb=# explain analyze execute ps('13','GroupId','testshop');
QUERY
PLAN
------------------------------------------------------------------------
-----------------------------------------------------------------------
Nested Loop (cost=0.00..56.83 rows=4 width=7) (actual
time=355.569..9989.681 rows=1 loops=1)
-> Index Scan using attr_name_value on big_table (cost=0.00..22.85
rows=4 width=7) (actual time=0.176..757.646 rows=914786 loops=1)
Index Cond: (((attr_name)::text = ($2)::text) AND
((attr_value)::text = ($3)::text))
-> Index Scan using pk_party on party (cost=0.00..8.48 rows=1
width=7) (actual time=0.010..0.010 rows=0 loops=914786)
Index Cond: ((party.party_id)::text =
(big_table.party_id)::text)
Filter: ((party.external_id)::text = ($1)::text) Total runtime:
9989.749 ms
(7 rows)

name |
current_setting
---------------------------------+--------------------------------------
------------------------------------------------------------------------
-----
version | PostgreSQL 8.4.4 on
x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red
Hat 4.1.2-48), 64-bit
autovacuum_analyze_scale_factor | 0.05
autovacuum_max_workers | 9
autovacuum_vacuum_scale_factor | 0.1
checkpoint_segments | 30
effective_cache_size | 6GB
effective_io_concurrency | 6
lc_collate | en_US.UTF-8
lc_ctype | en_US.UTF-8
listen_addresses | *
log_autovacuum_min_duration | 1s
log_checkpoints | on
log_destination | stderr
log_directory | /home.san/pg_log
log_line_prefix | %r PID:%p - %t - Tx: %v %l -
log_lock_waits | on
log_min_duration_statement | 1s
logging_collector | on
maintenance_work_mem | 512MB
max_connections | 1000
max_stack_depth | 2MB
server_encoding | UTF8
shared_buffers | 3GB
TimeZone | Europe/Berlin
vacuum_cost_delay | 100ms
vacuum_cost_limit | 200
vacuum_cost_page_dirty | 40
vacuum_cost_page_miss | 20
wal_buffers | 2MB
work_mem | 8MB
(30 rows)

CREATE DATABASE testdb
WITH OWNER = testuser
ENCODING = 'UTF8'
TABLESPACE = pg_default
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
CONNECTION LIMIT = -1;

-- Table: party

-- DROP TABLE party;

CREATE TABLE party
(
party_id character varying(255) NOT NULL,
party_type_id character varying(20),
external_id character varying(30),
preferred_currency_uom_id character varying(20),
description text,
status_id character varying(20),
created_date timestamp with time zone,
created_by_user_login character varying(255),
last_modified_date timestamp with time zone,
last_modified_by_user_login character varying(255),
last_updated_stamp timestamp with time zone,
last_updated_tx_stamp timestamp with time zone,
created_stamp timestamp with time zone,
created_tx_stamp timestamp with time zone,
CONSTRAINT pk_party PRIMARY KEY (party_id),
CONSTRAINT party_cul FOREIGN KEY (created_by_user_login)
REFERENCES user_login (user_login_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT party_lmcul FOREIGN KEY (last_modified_by_user_login)
REFERENCES user_login (user_login_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT party_pref_crncy FOREIGN KEY (preferred_currency_uom_id)
REFERENCES uom (uom_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT party_pty_typ FOREIGN KEY (party_type_id)
REFERENCES party_type (party_type_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT party_statusitm FOREIGN KEY (status_id)
REFERENCES status_item (status_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE,
autovacuum_vacuum_scale_factor=0.002,
autovacuum_analyze_scale_factor=0.001
);
ALTER TABLE party OWNER TO postgres;
ALTER TABLE party ALTER COLUMN party_id SET STATISTICS 300;

-- Index: mn_party_description

-- DROP INDEX mn_party_description;

CREATE INDEX mn_party_description
ON party
USING btree
(description);

-- Index: party_cul

-- DROP INDEX party_cul;

CREATE INDEX party_cul
ON party
USING btree
(created_by_user_login);

-- Index: party_lmcul

-- DROP INDEX party_lmcul;

CREATE INDEX party_lmcul
ON party
USING btree
(last_modified_by_user_login);

-- Index: party_pref_crncy

-- DROP INDEX party_pref_crncy;

CREATE INDEX party_pref_crncy
ON party
USING btree
(preferred_currency_uom_id);

-- Index: party_pty_typ

-- DROP INDEX party_pty_typ;

CREATE INDEX party_pty_typ
ON party
USING btree
(party_type_id);

-- Index: party_statusitm

-- DROP INDEX party_statusitm;

CREATE INDEX party_statusitm
ON party
USING btree
(status_id);

-- Index: party_txcrts

-- DROP INDEX party_txcrts;

CREATE INDEX party_txcrts
ON party
USING btree
(created_tx_stamp);

-- Index: party_txstmp

-- DROP INDEX party_txstmp;

CREATE INDEX party_txstmp
ON party
USING btree
(last_updated_tx_stamp);

-- Index: partyext_id_idx

-- DROP INDEX partyext_id_idx;

CREATE INDEX partyext_id_idx
ON party
USING btree
(external_id);

-- Index: upper_desc_idx

-- DROP INDEX upper_desc_idx;

CREATE INDEX upper_desc_idx
ON party
USING btree
(upper(btrim(description)));

-- Table: big_table

-- DROP TABLE big_table;

CREATE TABLE big_table
(
party_id character varying(255) NOT NULL,
attr_name character varying(60) NOT NULL,
attr_value character varying(255),
last_updated_stamp timestamp with time zone,
last_updated_tx_stamp timestamp with time zone,
created_stamp timestamp with time zone,
created_tx_stamp timestamp with time zone,
CONSTRAINT pk_big_table PRIMARY KEY (party_id, attr_name),
CONSTRAINT party_attr FOREIGN KEY (party_id)
REFERENCES party (party_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE,
autovacuum_vacuum_scale_factor=0.002,
autovacuum_analyze_scale_factor=0.001
);
ALTER TABLE big_table OWNER TO postgres;
ALTER TABLE big_table ALTER COLUMN party_id SET STATISTICS 300;

-- Index: attr_name_value

-- DROP INDEX attr_name_value;

CREATE INDEX attr_name_value
ON big_table
USING btree
(attr_name, attr_value);

-- Index: party_attr

-- DROP INDEX party_attr;

CREATE INDEX party_attr
ON big_table
USING btree
(party_id);

-- Index: prt_attrbt_txcrts

-- DROP INDEX prt_attrbt_txcrts;

CREATE INDEX prt_attrbt_txcrts
ON big_table
USING btree
(created_tx_stamp);

-- Index: prt_attrbt_txstmp

-- DROP INDEX prt_attrbt_txstmp;

CREATE INDEX prt_attrbt_txstmp
ON big_table
USING btree
(last_updated_tx_stamp);