Thread: View is not using a table index

From:
Dan Shea
Date:

We have a table which we want to normalize and use the same SQL to
perform selects using a view.
The old table had 3 columns in it's index
(region_id,wx_element,valid_time).
The new table meteocode_elmts has a similar index but the region_id is a
reference to another table region_lookup and wx_element to table
meteocode_elmts_lookup.  This will make our index and table
significantly smaller.
As stated ablove we want to use the same SQL query to check the view.
The problem is we have not been able to set up the view so that it
references the "rev" index.  It just uses the region_id but ignores the
wx_element, therefore the valid_time is also ignored.  The rev index now
consists of region_id(reference to  region_lookup
table),wx_element(reference to meteocode_elmts_lookup) and valid_time.

We are using Postgresql 7.4.0.  Below is the relevant views and tables
plus an explain analyze of the query to the old table and the view.

Old table forceastelement
phoenix=# \d forecastelement
              Table "public.forecastelement"
     Column     |            Type             | Modifiers
----------------+-----------------------------+-----------
 origin         | character varying(10)       | not null
 timezone       | character varying(99)       | not null
 region_id      | character varying(20)       | not null
 wx_element     | character varying(99)       | not null
 value          | character varying(99)       | not null
 flag           | character(3)                | not null
 units          | character varying(99)       | not null
 valid_time     | timestamp without time zone | not null
 issue_time     | timestamp without time zone | not null
 next_forecast  | timestamp without time zone | not null  reception_time
| timestamp without time zone | not null
Indexes:
    "forecastelement_rwv_idx" btree (region_id, wx_element, valid_time)

New and view nad tables are
phoenix=# \d fcstelmt_view
               View "public.fcstelmt_view"
     Column     |            Type             | Modifiers
----------------+-----------------------------+-----------
 origin         | character varying(10)       |
 timezone       | character varying(10)       |
 region_id      | character varying(99)       |
 wx_element     | character varying(99)       |
 value          | character varying(99)       |
 flag           | character(3)                |
 unit           | character varying           |
 valid_time     | timestamp without time zone |
 issue_time     | timestamp without time zone |
 next_forecast  | timestamp without time zone |  reception_time |
timestamp without time zone |

View definition:
 SELECT meteocode_bltns.origin, meteocode_bltns.timezone,
region_lookup.region_id, meteocode_elmts_lookup.wx_element,
meteocode_elmts.value, meteocode_bltns.flag, ( SELECT
meteocode_units_lookup.unit FROM meteocode_units_lookup WHERE
meteocode_units_lookup.id = meteocode_elmts.unit_id) AS unit,
meteocode_elmts.valid_time, meteocode_bltns.issue_time,
meteocode_bltns.next_forecast, meteocode_bltns.reception_time FROM
meteocode_bltns, meteocode_elmts, region_lookup, meteocode_elmts_lookup
WHERE meteocode_bltns.meteocode_id = meteocode_elmts.meteocode AND
region_lookup.id = meteocode_elmts.reg_id AND meteocode_elmts_lookup.id
= meteocode_elmts.wx_element_id;

phoenix=# \d meteocode_elmts
             Table "public.meteocode_elmts"
    Column     |            Type             | Modifiers
---------------+-----------------------------+-----------
 meteocode     | integer                     |
 value         | character varying(99)       | not null
 unit_id       | integer                     |
 valid_time    | timestamp without time zone | not null
 lcleffect     | integer                     |
 reg_id        | integer                     |
 wx_element_id | integer                     |
Indexes:
    "rev" btree (reg_id, wx_element_id, valid_time) phoenix=# \d
meteocode_bltns
                                      Table "public.meteocode_bltns"
     Column     |            Type             |
Modifiers
----------------+-----------------------------+-------------------------
----------------+-----------------------------+-------------------------
----------------+-----------------------------+---------
 meteocode_id   | integer                     | not null default
nextval('"meteocode_bltns_idseq"'::text)
 origin         | character varying(10)       | not null
 header         | character varying(20)       | not null
 timezone       | character varying(10)       | not null
 flag           | character(3)                | not null
 initial        | character varying(40)       | not null
 issue_time     | timestamp without time zone | not null
 next_forecast  | timestamp without time zone | not null  reception_time
| timestamp without time zone | not null
Indexes:
    "meteocode_bltns_meteocode_id_idx" btree (meteocode_id)

phoenix=# \d region_lookup
         Table "public.region_lookup"
  Column   |         Type          | Modifiers
-----------+-----------------------+-----------
 id        | integer               | not null
 region_id | character varying(99) |
Indexes:
    "region_lookup_pkey" primary key, btree (id)

phoenix=# \d meteocode_elmts_lookup
     Table "public.meteocode_elmts_lookup"
   Column   |         Type          | Modifiers
------------+-----------------------+-----------
 id         | integer               | not null
 wx_element | character varying(99) | not null
Indexes:
    "meteocode_elmts_lookup_pkey" primary key, btree (id)
    "wx_element_idx" btree (wx_element)

phoenix=# \d meteocode_units_lookup
   Table "public.meteocode_units_lookup"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | integer               | not null
 unit   | character varying(99) | not null
Indexes:
    "meteocode_units_lookup_pkey" primary key, btree (id)

VIEW
PWFPM_DEV=# explain analyze SELECT
origin,timezone,region_id,wx_element,value,flag,unit,valid_time,issue_ti
me,next_forecast FROM fcstelmt_view where origin = 'OFFICIAL' and
timezone = 'CST6CDT' and region_id = 'PU-REG-WNT-00027' and wx_element
= 'NGTPERIOD_MINTEMP' and value = '-26' and flag= 'REG' and unit =
'CELSIUS' and valid_time = '2007-04-09 00:00:00'  and     issue_time =
'2007-04-08 15:00:00' and next_forecast = '2007-04-09 04:00:00' ;
QUERY PLAN

 Hash Join  (cost=1.47..1309504.33 rows=1 width=264) (actual
time=21.609..84.940 rows=1 loops=1)
   Hash Cond: ("outer".wx_element = "inner".id)
   ->  Nested Loop  (cost=0.00..1309501.76 rows=1 width=201) (actual
time=17.161..80.489 rows=1 loops=1)
         ->  Nested Loop  (cost=0.00..1309358.57 rows=1 width=154)
(actual time=17.018..80.373 rows=2 loops=1)
               ->  Seq Scan on region_lookup  (cost=0.00..26.73 rows=7
width=71) (actual time=0.578..2.135 rows=1 loops=1)
  Filter: ((region_id)::text = 'PU-REG-WNT-00027'::text)
               ->  Index Scan using rev on meteocode_elmts
(cost=0.00..187047.39 rows=1 width=91) (actual time=16.421..78    .208
rows=2 loops=1)
 Index Cond: ("outer".id = meteocode_elmts.region_id)
  Filter: (((value)::text = '-26'::text) AND (valid_time = '2007-04-09
00:00:00'::timestamp without tim    e zone) AND (((subplan))::text =
'CELSIUS'::text))
             SubPlan ->  Seq Scan on meteocode_units_lookup
(cost=0.00..1.09 rows=1 width=67) (actual time=0.013..0.018     rows=1
loops=2)
            Filter: (id = $0)
         ->  Index Scan using meteocode_bltns_meteocode_id_idx on
meteocode_bltns  (cost=0.00..143.18 rows=1 width=55) (ac    tual
time=0.044..0.045 rows=0 loops=2)
            Index Cond: (meteocode_bltns.meteocode_id =
"outer".meteocode)
            Filter: (((origin)::text = 'OFFICIAL'::text) AND
((timezone)::text = 'CST6CDT'::text) AND (flag = 'REG'::bp    char) AND
(issue_time = '2007-04-08 15:00:00'::timestamp without time zone) AND
(next_forecast = '2007-04-09 04:00:00'::ti    mestamp without time
zone))

   ->  Hash  (cost=1.46..1.46 rows=2 width=71) (actual time=0.081..0.081
rows=0 loops=1)
         ->  Seq Scan on meteocode_elmts_lookup  (cost=0.00..1.46 rows=2
width=71) (actual time=0.042..0.076 rows=1 loops=    1)
               Filter: ((wx_element)::text = 'NGTPERIOD_MINTEMP'::text)
   SubPlan
     ->  Seq Scan on meteocode_units_lookup  (cost=0.00..1.09 rows=1
width=67) (actual time=0.007..0.012 rows=1 loops=1)
           Filter: (id = $0)
 Total runtime: 85.190 ms
(22 rows)

OLD TABLE
PWFPM_DEV=# explain analyze SELECT
origin,timezone,region_id,wx_element,value,flag,units,valid_time,issue_t
ime,next_forecast FROM forecastelement where origin = 'OFFICIAL' and
timezone = 'CST6CDT' and region_id = 'PU-REG-WNT-00027' and wx_element =
'NGTPERIOD_MINTEMP' and value = '-26' and flag= 'REG' and units =
'CELSIUS' and valid_time = '2007-04-09 00:00:00'  and issue_time =
'2007-04-08 15:00:00' and next_forecast = '2007-04-09 04:00:00' ;
QUERY PLAN

 Index Scan using forecastelement_rwv_idx on forecastelement
(cost=0.00..4.03 rows=1 width=106) (actual time=0.207..0.207 rows=0
loops=1)
Index Cond: (((region_id)::text = 'PU-REG-WNT-00027'::text) AND
((wx_element)::text = 'NGTPERIOD_MINTEMP'::text) AND (valid_time =
'2007-04-09 00:00:00'::timestamp without time zone))
 Filter: (((origin)::text = 'OFFICIAL'::text) AND ((timezone)::text =
'CST6CDT'::text) AND ((value)::text = '-26'::text) AND (flag =
'REG'::bpchar) AND ((units)::text = 'CELSIUS'::text) AND (issue_time =
'2007-04-08 15:00:00'::timestamp without time zone) AND (next_forecast =
'2007-04-09 04:00:00'::timestamp without time zone))
 Total runtime: 0.327 ms
(4 rows)

From:
Richard Huxton
Date:

Dan Shea wrote:
> We have a table which we want to normalize and use the same SQL to
> perform selects using a view.
> The old table had 3 columns in it's index
> (region_id,wx_element,valid_time).
> The new table meteocode_elmts has a similar index but the region_id is a
> reference to another table region_lookup and wx_element to table
> meteocode_elmts_lookup.  This will make our index and table
> significantly smaller.
> As stated ablove we want to use the same SQL query to check the view.
> The problem is we have not been able to set up the view so that it
> references the "rev" index.  It just uses the region_id but ignores the
> wx_element, therefore the valid_time is also ignored.  The rev index now
> consists of region_id(reference to  region_lookup
> table),wx_element(reference to meteocode_elmts_lookup) and valid_time.
>
> We are using Postgresql 7.4.0.  Below is the relevant views and tables
> plus an explain analyze of the query to the old table and the view.

Please say it's not really 7.4.0 - you're running 7.4.xx actually,
aren't you, where xx is quite a high number?

> phoenix=# \d region_lookup
>          Table "public.region_lookup"
>   Column   |         Type          | Modifiers
> -----------+-----------------------+-----------
>  id        | integer               | not null
>  region_id | character varying(99) |
> Indexes:
>     "region_lookup_pkey" primary key, btree (id)
>
> phoenix=# \d meteocode_elmts_lookup
>      Table "public.meteocode_elmts_lookup"
>    Column   |         Type          | Modifiers
> ------------+-----------------------+-----------
>  id         | integer               | not null
>  wx_element | character varying(99) | not null
> Indexes:
>     "meteocode_elmts_lookup_pkey" primary key, btree (id)
>     "wx_element_idx" btree (wx_element)

Anyway, you're joining to these tables and testing against the text
values without any index useful to the join.

Try indexes on (wx_element, id) and (region_id,id) etc. Re-analyse the
tables and see what that does for you.

Oh - I'd expect an index over the timestamps might help too.

Then, if you've got time try setting up an 8.2 installation, do some
basic configuration and transfer the data. I'd be surprised if you
didn't get some noticeable improvements just from the version number
increase.
--
   Richard Huxton
   Archonet Ltd

From:
Dan Shea
Date:

Version is  PWFPM_DEV=# select version();
                                                version
------------------------------------------------------------------------
--------------------------------
 PostgreSQL 7.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
20030502 (Red Hat Linux 3.2.3-20)
(1 row)

We used the rpm source from postgresql-7.4-0.5PGDG.

You make it sound so easy.  Our database size is at 308 GB.  We actually
have 8.2.3 running and would like to transfer in the future.  We have to
investigate the best way to do it.

Dan.


-----Original Message-----
From: Richard Huxton [mailto:]
Sent: Tuesday, April 24, 2007 11:42 AM
To: Shea,Dan [NCR]
Cc: 
Subject: Re: [PERFORM] View is not using a table index

Dan Shea wrote:
> We have a table which we want to normalize and use the same SQL to
> perform selects using a view.
> The old table had 3 columns in it's index
> (region_id,wx_element,valid_time).
> The new table meteocode_elmts has a similar index but the region_id is

> a reference to another table region_lookup and wx_element to table
> meteocode_elmts_lookup.  This will make our index and table
> significantly smaller.
> As stated ablove we want to use the same SQL query to check the view.

> The problem is we have not been able to set up the view so that it
> references the "rev" index.  It just uses the region_id but ignores
> the wx_element, therefore the valid_time is also ignored.  The rev
> index now consists of region_id(reference to  region_lookup
> table),wx_element(reference to meteocode_elmts_lookup) and valid_time.
>
> We are using Postgresql 7.4.0.  Below is the relevant views and tables

> plus an explain analyze of the query to the old table and the view.

Please say it's not really 7.4.0 - you're running 7.4.xx actually,
aren't you, where xx is quite a high number?

> phoenix=# \d region_lookup
>          Table "public.region_lookup"
>   Column   |         Type          | Modifiers
> -----------+-----------------------+-----------
>  id        | integer               | not null
>  region_id | character varying(99) |
> Indexes:
>     "region_lookup_pkey" primary key, btree (id)
>
> phoenix=# \d meteocode_elmts_lookup
>      Table "public.meteocode_elmts_lookup"
>    Column   |         Type          | Modifiers
> ------------+-----------------------+-----------
>  id         | integer               | not null
>  wx_element | character varying(99) | not null
> Indexes:
>     "meteocode_elmts_lookup_pkey" primary key, btree (id)
>     "wx_element_idx" btree (wx_element)

Anyway, you're joining to these tables and testing against the text
values without any index useful to the join.

Try indexes on (wx_element, id) and (region_id,id) etc. Re-analyse the
tables and see what that does for you.

Oh - I'd expect an index over the timestamps might help too.

Then, if you've got time try setting up an 8.2 installation, do some
basic configuration and transfer the data. I'd be surprised if you
didn't get some noticeable improvements just from the version number
increase.
--
   Richard Huxton
   Archonet Ltd

From:
Andreas Kostyrka
Date:

* Dan Shea <> [070424 19:33]:
> Version is  PWFPM_DEV=# select version();
>                                                 version
> ------------------------------------------------------------------------
> --------------------------------
>  PostgreSQL 7.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
> 20030502 (Red Hat Linux 3.2.3-20)
> (1 row)
>
> We used the rpm source from postgresql-7.4-0.5PGDG.
>
> You make it sound so easy.  Our database size is at 308 GB.  We actually
> have 8.2.3 running and would like to transfer in the future.  We have to
> investigate the best way to do it.

That depends upon your requirements for the uptime.

Andreas

From:
Tom Lane
Date:

Dan Shea <> writes:
> You make it sound so easy.  Our database size is at 308 GB.

Well, if you can't update major versions that's understandable; that's
why we're still maintaining the old branches.  But there is no excuse
for not running a reasonably recent sub-release within your branch.
Read the release notes, and consider what you will say if one of the
several data-loss-causing bugs that were fixed long ago eats your DB:
http://developer.postgresql.org/pgdocs/postgres/release.html

            regards, tom lane

From:
Carlos Moreno
Date:

Tom Lane wrote:
> Dan Shea <> writes:
>
>> You make it sound so easy.  Our database size is at 308 GB.
>>
>
> Well, if you can't update major versions that's understandable; that's
> why we're still maintaining the old branches.  But there is no excuse
> for not running a reasonably recent sub-release within your branch.
> Read the release notes, and consider what you will say if one of the
> several data-loss-causing bugs that were fixed long ago eats your DB:
>

Was it Feb 2002?  The Slammer effectively shut down the entire Internet,
due to a severe bug in Microsucks SQL Server...  A fix for that buffer
overflow bug had been available since August 2001;  yet 90% of all SQL
servers on the planet were unpatched.

As much as it pains me to admit it, the lesson about the importance of
being a conscious, competent administrator takes precedence over the
lesson of how unbelievably incompetent and irresponsible and etc. etc.
Microsoft is to have such a braindead bug in such a high-profile and
high-price product.

Tom said it really nicely --- do stop and think about it;  the day arrives
when you *lost* all those 308 GB of valuable data;  and it was only in
your hands to have prevented it!  Would you want to see the light of
*that* day?

Carlos
--