Postgres Pro
Downloads
Home   >   mailing lists

Using indexes through a plpgsql procedure - Mailing list pgsql-general

From Ciaran Doherty
Subject Using indexes through a plpgsql procedure
Date
Msg-id CA+kYa_Ff=1Y4YafbJqrGg0sJbSrPUV422-ET7wGbGZKfv1PHtw@mail.gmail.com
Whole thread Raw
Responses Re: Using indexes through a plpgsql procedure  ("Peter J. Holzer" <hjp-pgsql@hjp.at>)
List pgsql-general
Tree view
Hello,


Thank you for taking the time to read this.

As a quick question. Can Postgres make use of indexes on a table when the data from that table is being returned from a procedure?

Some more details. I have a big table which has a long running query running on it. This table gets rebuilt from scratch on a weekly basis. Currently, I cannot update/replace the table in a timely manner, I have to wait for the long running queries to finish before we can update the table (the total time can be 5+ hours).

I am currently trying to build a procedure which will return data from the latest version of the table (by dynamically choosing the table name, something like
https://stackoverflow.com/questions/35559093/how-to-use-variable-as-table-name-in-plpgsql)  . To get a consistent return type I am having to use the `create type`  to build a custom return type. The problem (I think) I am having is that the indexes on the underlying tables are not usable after calling this procedure

(e.g.
```
select *
  from example_table as et
    join example_procedure() as ep on et.exapmle_column = ep.example_column
```
there will be no index on the column ep.example_column)

Is there a way to make sure indexes are used even if the data is accessed threw a procdure?

Thank you

--
while (e) { kyoatie(); }

pgsql-general by date:

Previous
From: Mihalidesová Jana
Date:
Subject: RE: Upgrade from 11.3 to 13.1 failed with out of memory
Next
From: PegoraroF10
Date:
Subject: Cascade publication with pg_stat_replication empty