pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIndex on View ?
5+ messages / 3 participants
[nested] [flat]
* Index on View ?
@ 2001-04-05 04:18 Keith Gray <keith@heart.com.au>
2001-04-05 08:45 ` Re: Index on View ? Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 5+ messages in thread
From: Keith Gray @ 2001-04-05 04:18 UTC (permalink / raw)
To: pgsql-sql
Is it possible (feasible) to create an index on a view.
We have a large table and a defined sub-set (view)
from this table, would it be possible to keep an index
of the sub-set.
Keith
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Index on View ?
2001-04-05 04:18 Index on View ? Keith Gray <keith@heart.com.au>
@ 2001-04-05 08:45 ` Richard Huxton <dev@archonet.com>
2001-04-05 23:14 ` Re: Index on View ? Keith Gray <keith@heart.com.au>
2001-04-05 23:19 ` Re: Index on View ? Ian Harding <iharding@pakrat.com>
0 siblings, 2 replies; 5+ messages in thread
From: Richard Huxton @ 2001-04-05 08:45 UTC (permalink / raw)
To: Keith Gray <keith@heart.com.au>; pgsql-sql
From: "Keith Gray" <keith@heart.com.au>
> Is it possible (feasible) to create an index on a view.
>
> We have a large table and a defined sub-set (view)
> from this table, would it be possible to keep an index
> of the sub-set.
I don't think so - the view is basically just a select rule that rewrites
queries based on it.
Indexes on underlying tables should be used though. Difficult to suggest
what indices you might need without knowing the view/tables/queries
involved.
- Richard Huxton
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Index on View ?
2001-04-05 04:18 Index on View ? Keith Gray <keith@heart.com.au>
2001-04-05 08:45 ` Re: Index on View ? Richard Huxton <dev@archonet.com>
@ 2001-04-05 23:14 ` Keith Gray <keith@heart.com.au>
2001-04-06 08:54 ` Re: Index on View ? Richard Huxton <dev@archonet.com>
1 sibling, 1 reply; 5+ messages in thread
From: Keith Gray @ 2001-04-05 23:14 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
Richard Huxton wrote:
>
> Indexes on underlying tables should be used though. Difficult to suggest
> what indices you might need without knowing the view/tables/queries
> involved.
As an example I may have an "Invoice" table with several thousand invoices.
I could design a query/view "Aged" to get all unpaid invoices
greater than 15 days old.
I would often look for Invoices per Client and should have an index on
Invoice(ClientID).
e.g. CREATE INDEX Invoice_ClientID ON Invoice(ClientID);
Is there any advantage in having an index on ClientID for the Aged query?
e.g. CREATE INDEX Aged_ClientID ON Aged(ClientID);
Would this index be continually maintained by the RDBMS or only on lookup?
Keith
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Index on View ?
2001-04-05 04:18 Index on View ? Keith Gray <keith@heart.com.au>
2001-04-05 08:45 ` Re: Index on View ? Richard Huxton <dev@archonet.com>
2001-04-05 23:14 ` Re: Index on View ? Keith Gray <keith@heart.com.au>
@ 2001-04-06 08:54 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 5+ messages in thread
From: Richard Huxton @ 2001-04-06 08:54 UTC (permalink / raw)
To: Keith Gray <keith@heart.com.au>; +Cc: pgsql-sql
From: "Keith Gray" <keith@heart.com.au>
> Richard Huxton wrote:
> >
> > Indexes on underlying tables should be used though. Difficult to suggest
> > what indices you might need without knowing the view/tables/queries
> > involved.
>
> As an example I may have an "Invoice" table with several thousand
invoices.
> I could design a query/view "Aged" to get all unpaid invoices
> greater than 15 days old.
>
> I would often look for Invoices per Client and should have an index on
> Invoice(ClientID).
>
> e.g. CREATE INDEX Invoice_ClientID ON Invoice(ClientID);
OK - makes sense.
>
> Is there any advantage in having an index on ClientID for the Aged query?
>
> e.g. CREATE INDEX Aged_ClientID ON Aged(ClientID);
>
> Would this index be continually maintained by the RDBMS or only on lookup?
You can't do this at all I'm afraid. You can only index actual data. In this
case you already have an index on clientID so you're covered.
For your 15-day query, if it looks something like:
SELECT * FROM invoices WHERE status='UNPAID' AND inv_date < CURRENT_DATE-'15
days'
you might want indexes on inv_date and status.
This doesn't mean that they will definitely be used though - it depends on
how many records you have and how many the query returns.
It *is* possible to define an index on a function, so you could in theory
write a quite_old(inv_date) function and index that, but I doubt it makes
much sense in your case.
- Richard Huxton
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Index on View ?
2001-04-05 04:18 Index on View ? Keith Gray <keith@heart.com.au>
2001-04-05 08:45 ` Re: Index on View ? Richard Huxton <dev@archonet.com>
@ 2001-04-05 23:19 ` Ian Harding <iharding@pakrat.com>
1 sibling, 0 replies; 5+ messages in thread
From: Ian Harding @ 2001-04-05 23:19 UTC (permalink / raw)
To: pgsql-sql
The Brand-X DBMS have 'indexed views' but in all their explanations I can't
see where they would be useful. SQL Server 2000 creates a 'clustered index'
on the view, then lets you create other unclustered indexes in addition to
it. Any time one of the source tables is updated, the clustered index needs
to be updated, which to me means instantiating the view, which means tons of
overhead. They talk about it being handy if there are aggregates in the
view, but why not create a table to hold the aggregated data and updated
with a trigger/rule?
Richard Huxton wrote:
> From: "Keith Gray" <keith@heart.com.au>
>
> > Is it possible (feasible) to create an index on a view.
> >
> > We have a large table and a defined sub-set (view)
> > from this table, would it be possible to keep an index
> > of the sub-set.
>
> I don't think so - the view is basically just a select rule that rewrites
> queries based on it.
>
> Indexes on underlying tables should be used though. Difficult to suggest
> what indices you might need without knowing the view/tables/queries
> involved.
>
> - Richard Huxton
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2001-04-06 08:54 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-04-05 04:18 Index on View ? Keith Gray <keith@heart.com.au>
2001-04-05 08:45 ` Richard Huxton <dev@archonet.com>
2001-04-05 23:14 ` Keith Gray <keith@heart.com.au>
2001-04-06 08:54 ` Richard Huxton <dev@archonet.com>
2001-04-05 23:19 ` Ian Harding <iharding@pakrat.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox