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

Re: View based upon function won't use index on joins

From: Jonathan Foy <thefoy(at)gmail(dot)com>
To: Віталій Тимчишин <tivv00(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: View based upon function won't use index on joins
Date: 2009-11-20 15:01:35
Message-ID: 4b46b5f00911200701q288fa6afsafe4bc21f708fee3@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
This seems to result in the same problem; should I attempt to pull for a
specific id_nbr/id_qfr, postgres uses the index without a problem. If I try
to join the two tables/views however, it insists on doing a sequential scan
(actually two in this case) and will not use the index.  Any other
ideas/explanations?

That being said, I probably need to look into arrays more.  I haven't used
them at all in my relatively brief experience with postgres.  More research!

2009/11/20 Віталій Тимчишин <tivv00(at)gmail(dot)com>

> How about
>
>
> CREATE OR REPLACE VIEW value_codes_view AS
> select * from (
>
>  SELECT value_codes.id_nbr,
>       value_codes.id_qfr,
>       (ARRAY[val_1_cd_1, ... , val_2_cd_12])[i] as value_code,
>       (ARRAY[val_1_amt_1, ... , val_2_amt_12])[i] as value_amount,
>    FROM value_codes, generate_series(1,24) i) a
> where value_code is not null and value_code != '';
> ?
>

In response to

Responses

pgsql-performance by date

Next:From: Matthew WakelingDate: 2009-11-20 15:08:00
Subject: Re: [PERFORM] Strange performance degradation
Previous:From: Tom LaneDate: 2009-11-20 14:49:59
Subject: Re: Strange performance degradation

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