Thread: Index on a view??
Hi all, Is it possible to create an index on a view? Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: J.Kraaijeveld@Askesis.nl web: www.askesis.nl
No, you can only create an index on the underlying tables. A view is a
virtual realtion, not a physical one, so what would the index point to?
Any functionality you need you should be able to get with functional
indexes.
Perhaps if/when postgresql gets materialized views an index on a view
might be useful.
Regards,
Jeff Davis
On Wed, 2005-01-05 at 20:15 +0100, Joost Kraaijeveld wrote:
> Hi all,
>
> Is it possible to create an index on a view?
>
> Groeten,
>
> Joost Kraaijeveld
> Askesis B.V.
> Molukkenstraat 14
> 6524NB Nijmegen
> tel: 024-3888063 / 06-51855277
> fax: 024-3608416
> e-mail: J.Kraaijeveld@Askesis.nl
> web: www.askesis.nl
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
On Wed, Jan 05, 2005 at 08:15:28PM +0100, Joost Kraaijeveld wrote: > > Is it possible to create an index on a view? Querying a view should use indexes if the underlying tables have indexes and the planner can figure out how to use them. For complex queries you might need to use a materialized view. PostgreSQL doesn't have materialized views per se but it does have functionality that can implement them. What problem are you trying to solve? Can you give us an example of what you're trying to do? -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Wed, 2005-01-05 at 13:14 -0700, Michael Fuhr wrote:
> On Wed, Jan 05, 2005 at 08:15:28PM +0100, Joost Kraaijeveld wrote:
[snip]
> PostgreSQL
> doesn't have materialized views per se but it does have functionality
> that can implement them.
>
Can you tell me what you mean by that?
Regards,
Jeff Davis
Jeff Davis wrote: > On Wed, 2005-01-05 at 13:14 -0700, Michael Fuhr wrote: > >>On Wed, Jan 05, 2005 at 08:15:28PM +0100, Joost Kraaijeveld wrote: > > > [snip] > > >>PostgreSQL >>doesn't have materialized views per se but it does have functionality >>that can implement them. >> > > > Can you tell me what you mean by that? You can use triggers to create your materialized views. > > Regards, > Jeff Davis > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Command Prompt, Inc., home of PostgreSQL Replication, and plPHP. Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
Attachment
On Wed, 2005-01-05 at 13:03 -0800, Jeff Davis wrote: > On Wed, 2005-01-05 at 13:14 -0700, Michael Fuhr wrote: > > On Wed, Jan 05, 2005 at 08:15:28PM +0100, Joost Kraaijeveld wrote: > > [snip] > > > PostgreSQL > > doesn't have materialized views per se but it does have functionality > > that can implement them. > > > > Can you tell me what you mean by that? triggers, maybe ? gnari
Ragnar Hafstað wrote: >On Wed, 2005-01-05 at 13:03 -0800, Jeff Davis wrote: > > >>On Wed, 2005-01-05 at 13:14 -0700, Michael Fuhr wrote: >> >> >>>On Wed, Jan 05, 2005 at 08:15:28PM +0100, Joost Kraaijeveld wrote: >>> >>> >>[snip] >> >> >> >>>PostgreSQL >>>doesn't have materialized views per se but it does have functionality >>>that can implement them. >>> >>> >>> >>Can you tell me what you mean by that? >> >> > >triggers, maybe ? > >gnari > > Specifically, info can be found here: http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html Jeff