Re: performance problem aftrer update from 7.1 to 7.4.2

Hello,

sorry im late, but here are more details:

im wondering why 7.4 doesnt use the newsletter_site_id_date_idx-index

explain with 7.1: without analyze
mcms09=> explain select * from newsletter where site_id='m000000-970' order by date desc,id desc limit 10;
NOTICE: QUERY PLAN:
Limit (cost=9.26..9.26 rows=7 width=84)
-> Sort (cost=9.26..9.26 rows=8 width=84)
-> Index Scan using newsletter_site_id_date_idx on newsletter (cost=0.00..9.14 rows=8 width=84)
EXPLAIN
__________________________
and with pgsql 7.4.2:

mcms=# explain analyse select * from newsletter where site_id='m000000-970' order by date desc,id desc limit 10;

QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------------

Limit (cost=17.78..17.81 rows=10 width=610) (actual time=0.625..0.645 rows=10 loops=1)

-> Sort (cost=17.78..17.81 rows=11 width=610) (actual time=0.620..0.627 rows=10 loops=1)

Sort Key: date, id

-> Index Scan using site_id_newsletter_key on newsletter (cost=0.00..17.59 rows=11 width=610) (actual time=0.087..0.286 rows=15 loops=1)

Index Cond: ((site_id)::text = 'm000000-970'::text)

Total runtime: 0.766 ms

(6 rows)

_____________________________

i also dumped both db-structures, with pg_dump from 7.4.2
(i also used for dumping out old 7.1 before importing to 7.4.2,
i also tried pg_dump from 7.1 and restored the db to 7.4, but performace was the same)

from old 7.1 dumped with pg_dump from 7.4.2:

CREATE SEQUENCE newsletter_id_seq
INCREMENT BY 1
MAXVALUE 2147483647
NO MINVALUE
CACHE 1;

CREATE TABLE newsletter (
id integer DEFAULT nextval('"newsletter_id_seq"'::text) NOT NULL,
site_id character varying,
date character varying,
title character varying,
text text,
aktiv smallint DEFAULT 1,
online smallint DEFAULT 1,
subtitle character varying,
show_titles smallint,
show_headline smallint,
bgcolor character varying
);

CREATE UNIQUE INDEX newsletter_id_key ON newsletter USING btree (id int4_ops);
CREATE INDEX aktiv_newsletter_key ON newsletter USING btree (aktiv int2_ops);
CREATE INDEX site_id_newsletter_key ON newsletter USING btree (site_id varchar_ops);
CREATE INDEX date_newsletter_key ON newsletter USING btree (date varchar_ops);
CREATE INDEX online_newsletter_key ON newsletter USING btree (online int2_ops);
CREATE INDEX newsletter_site_id_date_idx ON newsletter USING btree (site_id varchar_ops, date varchar_ops);

mcms09=> \d newsletter
Table "newsletter"
Attribute | Type | Modifier
---------------+-------------------+-------------------------------------------------------
id | integer | not null default nextval('"newsletter_id_seq"'::text)
site_id | character varying |
date | character varying |
title | character varying |
text | text |
aktiv | smallint | default 1
online | smallint | default 1
subtitle | character varying |
show_titles | smallint |
show_headline | smallint |
bgcolor | character varying |
Indices: aktiv_newsletter_key,
date_newsletter_key,
newsletter_id_key,
newsletter_site_id_date_idx,
online_newsletter_key,
site_id_newsletter_key

____________________

and structure from pgsql 7.4.2:

CREATE SEQUENCE newsletter_id_seq

INCREMENT BY 1
MAXVALUE 2147483647
NO MINVALUE
CACHE 1;

CREATE TABLE newsletter (
id integer DEFAULT nextval('"newsletter_id_seq"'::text) NOT NULL,
site_id character varying,
date character varying,
title character varying,
text text,
aktiv smallint DEFAULT 1,
online smallint DEFAULT 1,
subtitle character varying,
show_titles smallint,
show_headline smallint,
bgcolor character varying
);

CREATE UNIQUE INDEX newsletter_id_key ON newsletter USING btree (id);
CREATE INDEX site_id_newsletter_key ON newsletter USING btree (site_id);
CREATE INDEX date_newsletter_key ON newsletter USING btree (date);
CREATE INDEX online_newsletter_key ON newsletter USING btree (online);
CREATE INDEX newsleter_date_idx ON newsletter USING btree (date);
CREATE INDEX newsletter_site_id_date_idx ON newsletter USING btree (site_id varchar_ops, date varchar_ops);

mcms=# \d newsletter
Table "public.newsletter"
Column | Type | Modifiers
---------------+-------------------+-------------------------------------------------------
id | integer | not null default nextval('"newsletter_id_seq"'::text)
site_id | character varying |
date | character varying |
title | character varying |
text | text |
aktiv | smallint | default 1
online | smallint | default 1
subtitle | character varying |
show_titles | smallint |
show_headline | smallint |
bgcolor | character varying |
Indexes:
"newsletter_id_key" unique, btree (id)
"aktiv_newsletter_key" btree (aktiv)
"date_newsletter_key" btree (date)
"newsleter_date_idx" btree (date)
"newsletter_site_id_date_idx" btree (site_id, date)
"online_newsletter_key" btree (online)
"site_id_newsletter_key" btree (site_id)

i tried also creation of index on date only in 7.4, but this does not change anything.
hmmm

special thanks for reading and all comments :)
yours sincerely,
volker

Tom Lane wrote:

>Richard Huxton <dev(at)archonet(dot)com> writes:
>
>
>>1. PG has changed the way it reports row width (I don't remember any such
>>change).
>>
>>
>
>My recollection is that up till 7.2, the estimation of widths for
>variable-width columns was completely bogus. Since 7.2 it's driven by
>an actual average width for the column as measured by ANALYZE. So if
>the query is selecting some fairly wide variable-width columns then it's
>entirely likely for the width estimate to take a big jump.
>
>Given that we haven't seen any EXPLAIN ANALYZE output it's hard to say
>anything about what the *real* problem is ...
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
>
>
>