Skip site navigation (1) Skip section navigation (2)

PostgreSQL upgraded to 8.2 but forcing index scan on query produces faster

From: Christian Brink <cbrink(at)r-stream(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: PostgreSQL upgraded to 8.2 but forcing index scan on query produces faster
Date: 2010-03-22 19:09:11
Message-ID: 4BA7C057.9090909@r-stream.com (view raw or flat)
Thread:
Lists: 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



Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2010-03-22 19:21:35
Subject: Re: PostgreSQL upgraded to 8.2 but forcing index scan on query produces faster
Previous:From: Scott CareyDate: 2010-03-22 18:47:43
Subject: Re: Block at a time ...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group