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

Re: View is not using a table index

From: Dan Shea <dan(dot)shea(at)ec(dot)gc(dot)ca>
To: "Richard Huxton" <dev(at)archonet(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: View is not using a table index
Date: 2007-04-24 17:31:19
Message-ID: 75312DFA648AF74D8A236E5269BFBE4B04292F02@ncrx3.ncr.int.ec.gc.ca (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-performance
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:dev(at)archonet(dot)com] 
Sent: Tuesday, April 24, 2007 11:42 AM
To: Shea,Dan [NCR]
Cc: pgsql-performance(at)postgresql(dot)org
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

In response to

Responses

pgsql-performance by date

Next:From: Andreas KostyrkaDate: 2007-04-24 18:03:57
Subject: Re: View is not using a table index
Previous:From: Richard HuxtonDate: 2007-04-24 15:41:43
Subject: Re: View is not using a table index

pgsql-general by date

Next:From: Andy DaleDate: 2007-04-24 17:43:03
Subject: Re: Generic triggers ?
Previous:From: George PavlovDate: 2007-04-24 17:29:19
Subject: Re: PG service restart failure (start getting ahead of stop?)

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