PostgreSQL upgraded to 8.2 but forcing index scan on query produces faster - Mailing list pgsql-performance

I previously posted 'forcing index scan on query produces 16x faster'
and it seemed that the consensus was that 8.0.x series had an issue. I
have upgraded to the highest practical version for our distro. But we
seem to have the same issue.

If I force the 'enable_seqscan' off our actual time is 9ms where if
'enable_seqscan' is on the performance is 2200ms ( the good news is the
Seq Scan query on 8.2 is 1/2 the time of the 8.0 query ).


The paste is below - I reloaded the table from scratch after the 8.2
upgrade. Then I ran a 'REINDEX DATABASE' and a 'VACUUM ANALYZE' (then
ran some queries and reran the vac analyze).



postream=> SELECT version();
                                                  version
---------------------------------------------------------------------------------------------------------
  PostgreSQL 8.2.11 on i386-redhat-linux-gnu, compiled by GCC gcc (GCC)
4.1.2 20070925 (Red Hat 4.1.2-33)
(1 row)

postream=> SET enable_seqscan = false;
SET
postream=> EXPLAIN ANALYZE
postream-> SELECT si.group1_id as name, sum(si.qty) as count,
sum(si.amt) as amt
postream->    FROM salesitems si, sales s, sysstrings
postream->   WHERE si.id = s.id
postream->    AND si.group1_id != ''
postream->    AND si.group1_id IS NOT NULL
postream->    AND NOT si.void
postream->    AND NOT s.void
postream->    AND NOT s.suspended
postream->    AND s.tranzdate >= (cast('2010-02-15' as date) +
cast(sysstrings.data as time))
postream->    AND s.tranzdate < ((cast('2010-02-15' as date) + 1) +
cast(sysstrings.data as time))
postream->    AND sysstrings.id='net/Console/Employee/Day End Time'
postream-> GROUP BY name;
                                                                                                 QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  HashAggregate  (cost=426973.65..426973.86 rows=14 width=35) (actual
time=9.424..9.438 rows=12 loops=1)
    ->  Nested Loop  (cost=0.01..426245.31 rows=97113 width=35) (actual
time=0.653..6.954 rows=894 loops=1)
          ->  Nested Loop  (cost=0.01..2416.59 rows=22477 width=4)
(actual time=0.595..2.150 rows=225 loops=1)
                ->  Index Scan using sysstrings_pkey on sysstrings
(cost=0.00..8.27 rows=1 width=182) (actual time=0.110..0.112 rows=1 loops=1)
                      Index Cond: (id = 'net/Console/Employee/Day End
Time'::text)
                ->  Index Scan using sales_tranzdate_index on sales s
(cost=0.01..1846.40 rows=22477 width=12) (actual time=0.454..1.687
rows=225 loops=1)
                      Index Cond: ((s.tranzdate >= ('2010-02-15'::date +
(sysstrings.data)::time without time zone)) AND (s.tranzdate <
('2010-02-16'::date + (sysstrings.data)::time without time zone)))
                      Filter: ((NOT void) AND (NOT suspended))
          ->  Index Scan using salesitems_pkey on salesitems si
(cost=0.00..18.54 rows=25 width=39) (actual time=0.007..0.013 rows=4
loops=225)
                Index Cond: (si.id = s.id)
                Filter: (((group1_id)::text <> ''::text) AND (group1_id
IS NOT NULL) AND (NOT void))
  Total runtime: 9.585 ms
(12 rows)

postream=> SET enable_seqscan = true;
SET
postream=> EXPLAIN ANALYZE
postream-> SELECT si.group1_id as name, sum(si.qty) as count,
sum(si.amt) as amt
postream->    FROM salesitems si, sales s, sysstrings
postream->   WHERE si.id = s.id
postream->    AND si.group1_id != ''
postream->    AND si.group1_id IS NOT NULL
postream->    AND NOT si.void
postream->    AND NOT s.void
postream->    AND NOT s.suspended
postream->    AND s.tranzdate >= (cast('2010-02-15' as date) +
cast(sysstrings.data as time))
postream->    AND s.tranzdate < ((cast('2010-02-15' as date) + 1) +
cast(sysstrings.data as time))
postream->    AND sysstrings.id='net/Console/Employee/Day End Time'
postream-> GROUP BY name;
                                                                                                    QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  HashAggregate  (cost=38315.09..38315.30 rows=14 width=35) (actual
time=2206.531..2206.545 rows=12 loops=1)
    ->  Hash Join  (cost=2697.55..37586.74 rows=97113 width=35) (actual
time=2128.070..2204.048 rows=894 loops=1)
          Hash Cond: (si.id = s.id)
          ->  Seq Scan on salesitems si  (cost=0.00..30578.15
rows=890646 width=39) (actual time=0.047..1487.688 rows=901281 loops=1)
                Filter: (((group1_id)::text <> ''::text) AND (group1_id
IS NOT NULL) AND (NOT void))
          ->  Hash  (cost=2416.59..2416.59 rows=22477 width=4) (actual
time=1.823..1.823 rows=225 loops=1)
                ->  Nested Loop  (cost=0.01..2416.59 rows=22477 width=4)
(actual time=0.477..1.592 rows=225 loops=1)
                      ->  Index Scan using sysstrings_pkey on
sysstrings  (cost=0.00..8.27 rows=1 width=182) (actual time=0.039..0.040
rows=1 loops=1)
                            Index Cond: (id = 'net/Console/Employee/Day
End Time'::text)
                      ->  Index Scan using sales_tranzdate_index on
sales s  (cost=0.01..1846.40 rows=22477 width=12) (actual
time=0.410..1.187 rows=225 loops=1)
                            Index Cond: ((s.tranzdate >=
('2010-02-15'::date + (sysstrings.data)::time without time zone)) AND
(s.tranzdate < ('2010-02-16'::date + (sysstrings.data)::time without
time zone)))
                            Filter: ((NOT void) AND (NOT suspended))
  Total runtime: 2206.706 ms
(13 rows)

postream=> \d salesitems;
                     Table "public.salesitems"
     Column    |           Type           |       Modifiers
--------------+--------------------------+------------------------
  id           | integer                  | not null
  lineno       | smallint                 | not null
  plu          | character varying(35)    |
  qty          | numeric(8,3)             | not null
  amt          | numeric(10,2)            |
  last_updated | timestamp with time zone | default now()
  group1_id    | character varying(64)    |
  group2_id    | text                     |
  group3_id    | text                     |
  void         | boolean                  | not null default false
  hash         | boolean                  | not null default false
  component    | boolean                  | not null default false
  subitem      | boolean                  | not null default false
Indexes:
     "salesitems_pkey" PRIMARY KEY, btree (id, lineno)
     "idx_si_group_id" btree (group1_id)
     "salesitems_last_updated_index" btree (last_updated)

--
Christian Brink