Re: performance problem aftrer update from 7.1 to 7.4.2 - Mailing list pgsql-general

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 1online        | smallint          | default 1subtitle      | 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 1online        | smallint          | default 1subtitle      | 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@archonet.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