pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedindexes
22+ messages / 12 participants
[nested] [flat]
* indexes
@ 1999-05-31 08:42 Remigiusz Sokolowski <rems@gdansk.sprint.pl>
0 siblings, 2 replies; 22+ messages in thread
From: Remigiusz Sokolowski @ 1999-05-31 08:42 UTC (permalink / raw)
To: pgsql-sql
Hi!
I try to optimize following query
SELECT DISTINCT e1.name_ent AS nazwa_grupy
FROM ent e1, binds b1, ent e2
WHERE e1.id_ent=b1.id_parent AND b1.id_child=e2.id_ent AND b1.id_links=0
AND e2.name_ent='SERWIS';
Is it possible to get better results? From explain notes it looks, that
engine uses indexes on ent table, but I can't constrain it to use any
index on binds table - this causes main problem.
Or may be query should look in other way?
EXPLAIN gives following information:
NOTICE: QUERY PLAN:
Unique (cost=77.02 size=0 width=0)
-> Sort (cost=77.02 size=0 width=0)
-> Nested Loop (cost=77.02 size=1 width=28)
-> Nested Loop (cost=74.97 size=1 width=12)
-> Seq Scan on b1 (cost=72.97 size=1 width=8)
-> Index Scan on e2 (cost=2.00 size=1 width=4)
-> Index Scan on e1 (cost=2.05 size=1304 width=16)
and I have following indexes
Table = ent2_idx
+--------------------+--------------------+------+
|Field |Type |Length|
+--------------------+--------------------+------+
| id_ent | int4 |4 |
| name_ent | text |var |
+--------------------+--------------------+------+
and
Table = binds2_idx
+--------------------+----------------------------------+------+
|Field | Type |Length|
+--------------------+----------------------------------+------+
| id_parent | int4 |4 |
| id_child | int4 |4 |
| id_links | int4 |4 |
+--------------------+----------------------------------+------+
TIA
Rem
-------------------------------------------------------------------*------------
Remigiusz Sokolowski e-mail: rems@gdansk.sprint.pl * *
-----------------------------------------------------------------*****----------
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] indexes
@ 1999-05-31 08:50 Vadim Mikheev <vadim@krs.ru>
parent: Remigiusz Sokolowski <rems@gdansk.sprint.pl>
1 sibling, 1 reply; 22+ messages in thread
From: Vadim Mikheev @ 1999-05-31 08:50 UTC (permalink / raw)
To: Remigiusz Sokolowski <rems@gdansk.sprint.pl>; +Cc: pgsql-sql
Remigiusz Sokolowski wrote:
>
> Hi!
> I try to optimize following query
> SELECT DISTINCT e1.name_ent AS nazwa_grupy
> FROM ent e1, binds b1, ent e2
> WHERE e1.id_ent=b1.id_parent AND b1.id_child=e2.id_ent AND b1.id_links=0
^^^^^^^^^^^^^
> AND e2.name_ent='SERWIS';
>
> Is it possible to get better results? From explain notes it looks, that
> engine uses indexes on ent table, but I can't constrain it to use any
> index on binds table - this causes main problem.
> Or may be query should look in other way?
>
...
> Table = binds2_idx
> +--------------------+----------------------------------+------+
> |Field | Type |Length|
> +--------------------+----------------------------------+------+
> | id_parent | int4 |4 |
> | id_child | int4 |4 |
> | id_links | int4 |4 |
> +--------------------+----------------------------------+------+
Try to create index on binds (id_links, id_parent, id_child)
Vadim
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] indexes
@ 1999-05-31 09:35 Remigiusz Sokolowski <rems@gdansk.sprint.pl>
parent: Vadim Mikheev <vadim@krs.ru>
0 siblings, 1 reply; 22+ messages in thread
From: Remigiusz Sokolowski @ 1999-05-31 09:35 UTC (permalink / raw)
To: Vadim Mikheev <vadim@krs.ru>; +Cc: pgsql-sql
> > Hi!
> > I try to optimize following query
> > SELECT DISTINCT e1.name_ent AS nazwa_grupy
> > FROM ent e1, binds b1, ent e2
> > WHERE e1.id_ent=b1.id_parent AND b1.id_child=e2.id_ent AND b1.id_links=0
> > AND e2.name_ent='SERWIS';
> >
> > Table = binds2_idx
> > +--------------------+----------------------------------+------+
> > |Field | Type |Length|
> > +--------------------+----------------------------------+------+
> > | id_parent | int4 |4 |
> > | id_child | int4 |4 |
> > | id_links | int4 |4 |
> > +--------------------+----------------------------------+------+
>
> Try to create index on binds (id_links, id_parent, id_child)
>
> Vadim
Wow - works great - big thanks!
Could You throw some hints - why index should look like as You wrote?
I looked in docs, but there is no clues how to build indices (or I just
didn't find them)
Thanks once more :-)
Rem
-------------------------------------------------------------------*------------
Remigiusz Sokolowski e-mail: rems@gdansk.sprint.pl * *
-----------------------------------------------------------------*****----------
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] indexes
@ 1999-05-31 11:54 Vadim Mikheev <vadim@krs.ru>
parent: Remigiusz Sokolowski <rems@gdansk.sprint.pl>
0 siblings, 1 reply; 22+ messages in thread
From: Vadim Mikheev @ 1999-05-31 11:54 UTC (permalink / raw)
To: Remigiusz Sokolowski <rems@gdansk.sprint.pl>; +Cc: pgsql-sql
Remigiusz Sokolowski wrote:
>
> > > Hi!
> > > I try to optimize following query
> > > SELECT DISTINCT e1.name_ent AS nazwa_grupy
> > > FROM ent e1, binds b1, ent e2
> > > WHERE e1.id_ent=b1.id_parent AND b1.id_child=e2.id_ent AND b1.id_links=0
> > > AND e2.name_ent='SERWIS';
> > >
> > > Table = binds2_idx
> > > +--------------------+----------------------------------+------+
> > > |Field | Type |Length|
> > > +--------------------+----------------------------------+------+
> > > | id_parent | int4 |4 |
> > > | id_child | int4 |4 |
> > > | id_links | int4 |4 |
> > > +--------------------+----------------------------------+------+
> >
> > Try to create index on binds (id_links, id_parent, id_child)
> >
> > Vadim
>
> Wow - works great - big thanks!
> Could You throw some hints - why index should look like as You wrote?
> I looked in docs, but there is no clues how to build indices (or I just
> didn't find them)
Index can be used only if first index key is defined. In your query
b1.id_parent (your old first index key) is used in join clause and
so binds index could be used only in inner plan: after a tuple
is fetched from e1 (outer plan) making b1.id_parent defined, but
optimizer decided that this is not right way.
b1.id_links has constant value and so binds index with id_links as
first key may be used in outer plan (scanned first). Other keys of
new binds index will not be used in index scan of this query anyway.
After a binds tuple is fetched, keys for e1/e2 indices is defined and
so these indices can be used, and more of that - both keys of
e2 index will be used in scan.
Vadim
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] indexes
@ 1999-05-31 15:17 Heiko Wilms <wilms@stud.fh-hannover.de>
parent: Vadim Mikheev <vadim@krs.ru>
0 siblings, 1 reply; 22+ messages in thread
From: Heiko Wilms @ 1999-05-31 15:17 UTC (permalink / raw)
To: pgsql-sql
Hi all,
indices speed up database queries. Tell me if I'm wrong.
Does it make sense to create indices on all fields involved in
a query? If so, what is the best access method (btree,rtree,hash)?
Heiko
--
H. Wilms
Homepage schering.si2.uni-hannover.de/hugo
email wilms@stud.fh-hannover.de
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] indexes
@ 1999-05-31 15:20 Tom Lane <tgl@sss.pgh.pa.us>
parent: Remigiusz Sokolowski <rems@gdansk.sprint.pl>
1 sibling, 1 reply; 22+ messages in thread
From: Tom Lane @ 1999-05-31 15:20 UTC (permalink / raw)
To: Remigiusz Sokolowski <rems@gdansk.sprint.pl>; +Cc: pgsql-sql
Remigiusz Sokolowski <rems@gdansk.sprint.pl> writes:
> NOTICE: QUERY PLAN:
> Unique (cost=77.02 size=0 width=0)
> -> Sort (cost=77.02 size=0 width=0)
> -> Nested Loop (cost=77.02 size=1 width=28)
> -> Nested Loop (cost=74.97 size=1 width=12)
> -> Seq Scan on b1 (cost=72.97 size=1 width=8)
> -> Index Scan on e2 (cost=2.00 size=1 width=4)
> -> Index Scan on e1 (cost=2.05 size=1304 width=16)
>
I think the real problem here is that the optimizer thinks your tables
are small (notice the size=1 estimates in the inner loop). Have you
done a VACUUM lately? You need that to update the statistics that the
optimizer uses.
Unless you are dealing with very small tables, you don't want to see
nested-loop joins (that means scanning the lower table once for each
tuple in the upper table!). You want to see merge joins or hash joins.
Vadim's suggestion of a better-adapted index was a good one, but I
wonder whether the speedup you saw wasn't just a side effect from
CREATE INDEX having updated the optimizer's stats, so that it stopped
using nested loops...
regards, tom lane
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] indexes
@ 1999-05-31 17:28 Tom Lane <tgl@sss.pgh.pa.us>
parent: Heiko Wilms <wilms@stud.fh-hannover.de>
0 siblings, 0 replies; 22+ messages in thread
From: Tom Lane @ 1999-05-31 17:28 UTC (permalink / raw)
To: Heiko Wilms <wilms@stud.fh-hannover.de>; +Cc: pgsql-sql
Heiko Wilms <wilms@stud.fh-hannover.de> writes:
> indices speed up database queries. Tell me if I'm wrong.
> Does it make sense to create indices on all fields involved in
> a query?
Well, indexes can speed up queries, but they also slow down inserts
and updates, because each index has to be updated whenever you update
the table. So it's a tradeoff. You probably don't want to make indexes
that are only useful for seldom-used queries, unless updates to the
table are even less frequent than the queries.
If you are wondering *which* fields to make indexes on, one way to
decide is to create a whole set of indexes and then run EXPLAIN on
the kinds of queries you do most often. EXPLAIN will show you which
indexes the optimizer is actually using --- then you can get rid of
the rest. (Be sure to load the tables to representative sizes and
do a VACUUM ANALYZE before you trust the results from EXPLAIN very
much.)
> If so, what is the best access method (btree,rtree,hash)?
btree is the best general-purpose index type. rtree is only suitable
for 2-D data (polygons and similar data types). hash is less flexible
than btree because you cannot use it for an ordered scan of the table.
regards, tom lane
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] indexes
@ 1999-06-01 06:41 Remigiusz Sokolowski <rems@gdansk.sprint.pl>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 22+ messages in thread
From: Remigiusz Sokolowski @ 1999-06-01 06:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
> Remigiusz Sokolowski <rems@gdansk.sprint.pl> writes:
> > NOTICE: QUERY PLAN:
> > Unique (cost=77.02 size=0 width=0)
> > -> Sort (cost=77.02 size=0 width=0)
> > -> Nested Loop (cost=77.02 size=1 width=28)
> > -> Nested Loop (cost=74.97 size=1 width=12)
> > -> Seq Scan on b1 (cost=72.97 size=1 width=8)
> > -> Index Scan on e2 (cost=2.00 size=1 width=4)
> > -> Index Scan on e1 (cost=2.05 size=1304 width=16)
> >
>
> I think the real problem here is that the optimizer thinks your tables
> are small (notice the size=1 estimates in the inner loop). Have you
> done a VACUUM lately? You need that to update the statistics that the
> optimizer uses.
Yea - I've just done VACUUM during my efforts to speed up query (btw.
should I do something with notices from VACUUM like that one:
NOTICE: Ind binds_idx: NUMBER OF INDEX' TUPLES (4) IS NOT THE SAME AS
HEAP' (1787))
> Unless you are dealing with very small tables, you don't want to see
> nested-loop joins (that means scanning the lower table once for each
> tuple in the upper table!). You want to see merge joins or hash joins.
>
> Vadim's suggestion of a better-adapted index was a good one, but I
> wonder whether the speedup you saw wasn't just a side effect from
> CREATE INDEX having updated the optimizer's stats, so that it stopped
> using nested loops...
>
I have too less experience to know at what quantity of records use which
join.
In this case table ent has 1304 rows and table binds 1787.
But without Vadim's suggestion I've got still similar results - sometimes
optimizer has used index on e1 and on b1, sometimes on e1 and e2, but
never on e1,b1 and e2 at once (note: this was join on ent e1, binds b1 and
ent e2) and whole query was performed at cost about 70. After rebuilding
index at cost 6-7 (what is enough for me)
Rem
-------------------------------------------------------------------*------------
Remigiusz Sokolowski e-mail: rems@gdansk.sprint.pl * *
-----------------------------------------------------------------*****----------
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] indexes
@ 1999-06-01 07:45 Vadim Mikheev <vadim@krs.ru>
parent: Remigiusz Sokolowski <rems@gdansk.sprint.pl>
1 sibling, 1 reply; 22+ messages in thread
From: Vadim Mikheev @ 1999-06-01 07:45 UTC (permalink / raw)
To: Remigiusz Sokolowski <rems@gdansk.sprint.pl>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
Remigiusz Sokolowski wrote:
>
> Yea - I've just done VACUUM during my efforts to speed up query (btw.
> should I do something with notices from VACUUM like that one:
> NOTICE: Ind binds_idx: NUMBER OF INDEX' TUPLES (4) IS NOT THE SAME AS
> HEAP' (1787))
Ops. Re-build binds_idx! And use latest 6.5 snapshot if you use
6.5 beta...
Vadim
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] indexes
@ 1999-06-01 07:57 Remigiusz Sokolowski <rems@gdansk.sprint.pl>
parent: Vadim Mikheev <vadim@krs.ru>
0 siblings, 0 replies; 22+ messages in thread
From: Remigiusz Sokolowski @ 1999-06-01 07:57 UTC (permalink / raw)
To: Vadim Mikheev <vadim@krs.ru>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
> >
> > Yea - I've just done VACUUM during my efforts to speed up query (btw.
> > should I do something with notices from VACUUM like that one:
> > NOTICE: Ind binds_idx: NUMBER OF INDEX' TUPLES (4) IS NOT THE SAME AS
> > HEAP' (1787))
>
> Ops. Re-build binds_idx! And use latest 6.5 snapshot if you use
> 6.5 beta...
>
> Vadim
I use ver. 6.3.2 and have rather little influence on which version is used
- my admin decide about such things.
Is this possible to solve this manually?
Rem
-------------------------------------------------------------------*------------
Remigiusz Sokolowski e-mail: rems@gdansk.sprint.pl * *
-----------------------------------------------------------------*****----------
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] indexes
@ 1999-06-01 14:04 Tom Lane <tgl@sss.pgh.pa.us>
parent: Remigiusz Sokolowski <rems@gdansk.sprint.pl>
1 sibling, 0 replies; 22+ messages in thread
From: Tom Lane @ 1999-06-01 14:04 UTC (permalink / raw)
To: Remigiusz Sokolowski <rems@gdansk.sprint.pl>; +Cc: pgsql-sql
Remigiusz Sokolowski <rems@gdansk.sprint.pl> writes:
> But without Vadim's suggestion I've got still similar results - sometimes
> optimizer has used index on e1 and on b1, sometimes on e1 and e2, but
> never on e1,b1 and e2 at once (note: this was join on ent e1, binds b1 and
> ent e2)
You won't ever see a three-way join in EXPLAIN's output, if that was
what you were looking for. Postgres always handles joins by combining
two tables, then combining the result with another table, etc.
regards, tom lane
^ permalink raw reply [nested|flat] 22+ messages in thread
* Indexes
@ 2001-10-12 20:37 Aasmund Midttun Godal <postgresql@envisity.com>
0 siblings, 1 reply; 22+ messages in thread
From: Aasmund Midttun Godal @ 2001-10-12 20:37 UTC (permalink / raw)
To: pgsql-sql
Can someone shed some light on as to how the indexes actually work?
I want to index the results of a function, the function does a whole lot of different selects on different tables. Will the database know when to update the index.
If there already is some documentation on this (besides the source...) I would be glad to know.
Aasmund Midttun Godal
aasmund@godal.com - http://www.godal.com/
+47 40 45 20 46
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Indexes
@ 2001-10-13 02:33 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Aasmund Midttun Godal <postgresql@envisity.com>
0 siblings, 1 reply; 22+ messages in thread
From: Stephan Szabo @ 2001-10-13 02:33 UTC (permalink / raw)
To: Aasmund Midttun Godal <postgresql@envisity.com>; +Cc: pgsql-sql
On Fri, 12 Oct 2001, Aasmund Midttun Godal wrote:
> Can someone shed some light on as to how the indexes actually work?
>
> I want to index the results of a function, the function does a whole
> lot of different selects on different tables. Will the database know
> when to update the index.
AFAIK No. The functions for functional indexes probably should only
depend upon the arguments and no outside data. Anything else sounds like
a recipe for broken indexes.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Indexes
@ 2001-10-13 14:57 Aasmund Midttun Godal <aasmund@godal.com>
parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 22+ messages in thread
From: Aasmund Midttun Godal @ 2001-10-13 14:57 UTC (permalink / raw)
To: pgsql-sql; +Cc: sszabo@megazone23.bigpanda.com
On Sat, 13 Oct 2001 14:17:48 GMT, "Aasmund Midttun Godal" <postgresql@envisity.com> wrote:
> On Fri, 12 Oct 2001 19:33:46 -0700 (PDT), Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:
> Well, then another question; will a function be only evaluated once inside a query with the specific arguments?
>
> i.e.will
>
> my_table
>
> foo|bar
> 1 |2
> 1 |3
> 2 |1
> 1 |3
>
>
> SELECT foo, bar FROM my_table WHERE func(foo, bar);
>
> will func be evaluated 3 or 4 times ?
>
I did some research on this issue. and I noticed the following:
the func was executed for each row i.e. 4 times.
However if I set iscachable it was only executed 3 times.
Yet it was executed again next time I called the select (I would have expected it to still be cached) is this intentional?
Or would it be a good idea to introduce a new option,
iscacheable (permanently cachable)
querycachable (cachable within a given query)
If there is some info on this issue please let me know!
> Aasmund Midttun Godal
>
> aasmund@godal.com - http://www.godal.com/
> +47 40 45 20 46
Aasmund Midttun Godal
aasmund@godal.com - http://www.godal.com/
+47 40 45 20 46
^ permalink raw reply [nested|flat] 22+ messages in thread
* indexes
@ 2002-02-05 17:05 Isabelle Brette <isabelle@apartia.fr>
0 siblings, 2 replies; 22+ messages in thread
From: Isabelle Brette @ 2002-02-05 17:05 UTC (permalink / raw)
To: pgsql-sql
Hi everybody ^^
I have a big, big table (over 1 million rows) that's been logging
sessions on a website for more than a year. And a few queries so I can
have a few stats on it.
One of the problems is, for stats I need to exclude internal IPs (the
web server is also used for the intranet). Another one, I generally need
the stats for each month. One of the queries is something like :
select to_char(created,'YYYY/MM'), count(*) from sessions where not
(ip << '192.168.0.0/16' or ip << '10.0.2.0/24') group by
to_char(created,'YYYY/MM');
As you can guess, with 1 million rows, it has become very, very slow and
I would need a few indexes to make this a little faster.
As you may also guess, making straight indexes does not work (I still
have this damn Seq Scan while EXPLAINing). For the date, I tried this :
create index sess_created on sessions (to_char(created,'YYYY/MM'));
but I get a parse error near "'". What do I have wrong, as the query
seems right ? Is it because of the non-column argument ?
And what would you suggest for the ip column ?
Thanks for your help ^^
--
Isabelle Brette - isabelle@apartia.fr
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: indexes
@ 2002-02-05 17:50 Chris Ruprecht <chrup999@yahoo.com>
parent: Isabelle Brette <isabelle@apartia.fr>
1 sibling, 1 reply; 22+ messages in thread
From: Chris Ruprecht @ 2002-02-05 17:50 UTC (permalink / raw)
To: isabelle@apartia.fr; +Cc: pgsql-sql
Isabella,
I would do a
create index sess_created on sessions (ip, to_char);
This satisfies the where clause and might help in the group by.
Best regards,
Chris
At 18:05 +0100 02/05/2002, Isabelle Brette wrote:
>Hi everybody ^^
>
[snip]
--
Chris Ruprecht
Network grunt and bit pusher extraordinaíre
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: indexes
@ 2002-02-06 09:50 Isabelle Brette <isabelle@apartia.fr>
parent: Chris Ruprecht <chrup999@yahoo.com>
0 siblings, 0 replies; 22+ messages in thread
From: Isabelle Brette @ 2002-02-06 09:50 UTC (permalink / raw)
To: Chris Ruprecht <chrup999@yahoo.com>; +Cc: pgsql-sql
Hi again,
On Tue, Feb 05, 2002 at 11:50:52AM -0600, Chris Ruprecht wrote:
> Isabella,
Mh, it's Isabelle, thanks.
> I would do a
> create index sess_created on sessions (ip, to_char);
>
> This satisfies the where clause and might help in the group by.
This does not seem to work.
One thing or two I did not say in my previous post :
- the 'created' column is a timestamp, so the to_char function needs a
second argument to know which format to use.
- the 'ip' column is of postgres type inet, that's why I use the <<
operator.
- for some other queries, I also need a filter on IPs but not on the
timestamp.
For the ip thing, I just looked at the doc and it seems a '<<' operator
on inet columns just can't use an indexed column properly. All I can do
is simple comparisons such as <, <=, =, >=, > (only the btree access
method is implemented in my current version of Postgres : 7.1.3 ; will
this change in 7.2 ?).
--
Isabelle Brette - isabelle@apartia.fr
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: indexes
@ 2002-02-06 15:29 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Isabelle Brette <isabelle@apartia.fr>
1 sibling, 0 replies; 22+ messages in thread
From: Stephan Szabo @ 2002-02-06 15:29 UTC (permalink / raw)
To: Isabelle Brette <isabelle@apartia.fr>; +Cc: pgsql-sql
On Tue, 5 Feb 2002, Isabelle Brette wrote:
> As you may also guess, making straight indexes does not work (I still
> have this damn Seq Scan while EXPLAINing). For the date, I tried this :
>
> create index sess_created on sessions (to_char(created,'YYYY/MM'));
>
> but I get a parse error near "'". What do I have wrong, as the query
> seems right ? Is it because of the non-column argument ?
For a functional index that you want to pass constants to, right now you'd
need to make a cacheable function that takes created in this case and
returns the to_char(created, 'YYYY/MM') and then use that in the index and
query.
^ permalink raw reply [nested|flat] 22+ messages in thread
* indexes
@ 2010-01-15 16:04 Seb <spluque@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Seb @ 2010-01-15 16:04 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have some views and queries that take a bit too long to return, so
perhaps some judicious indexes might help, but I don't know much about
how to use them. The PostgreSQL manual has a good section on indexes,
but I can't find guidance on (unless I missed something):
o How to decide what columns need an index?
o Should all foreign keys have an index?
o Naming conventions?
o Does PostgreSQL use available indexes that can be useful in any query,
without the user having to do anything in particular?
I'd appreciate any pointers to documents with guidance on these
questions. Thanks.
--
Seb
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: indexes
@ 2010-01-17 21:59 Chris <dmagick@gmail.com>
parent: Seb <spluque@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Chris @ 2010-01-17 21:59 UTC (permalink / raw)
To: Seb <spluque@gmail.com>; +Cc: pgsql-sql
Seb wrote:
> Hi,
>
> I have some views and queries that take a bit too long to return, so
> perhaps some judicious indexes might help, but I don't know much about
> how to use them. The PostgreSQL manual has a good section on indexes,
> but I can't find guidance on (unless I missed something):
>
> o How to decide what columns need an index?
I wrote something a little while ago about this:
http://www.designmagick.com/article/16/ (comments welcome!)
> o Should all foreign keys have an index?
Not necessarily, you might just want the db to enforce the restriction
but not actually use the data in it. For example, keep a userid (and
timestamp) column of the last person to update a row. You may need it to
say "aha - this was last changed on this date and by person X", but
you'll never generally use it.
If you never have a where clause with that column, no need to index it.
If you're using it in a join all the time, then yes it would be better
to index it.
> o Naming conventions?
That comes down to personal or project preference - there's no
particular convention used anywhere.
> o Does PostgreSQL use available indexes that can be useful in any query,
> without the user having to do anything in particular?
Yes - though just because an index is present doesn't mean postgres will
use it, in some cases it's better for it to ignore the index altogether
and use some other method to perform your query.
--
Postgresql & php tutorials
http://www.designmagick.com/
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: indexes
@ 2010-01-18 01:10 Seb <spluque@gmail.com>
parent: Chris <dmagick@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Seb @ 2010-01-18 01:10 UTC (permalink / raw)
To: pgsql-sql
On Mon, 18 Jan 2010 08:59:56 +1100,
Chris <dmagick@gmail.com> wrote:
>> o Should all foreign keys have an index?
> Not necessarily, you might just want the db to enforce the restriction
> but not actually use the data in it. For example, keep a userid (and
> timestamp) column of the last person to update a row. You may need it
> to say "aha - this was last changed on this date and by person X", but
> you'll never generally use it.
> If you never have a where clause with that column, no need to index
> it. If you're using it in a join all the time, then yes it would be
> better to index it.
Thanks for all your pointers!
Do views use the indexes in the underlying tables, whenever say a SELECT
operation is called on the view? If so, indexes on views don't make any
sense right?
--
Seb
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: indexes
@ 2010-01-18 01:22 Craig Ringer <craig@postnewspapers.com.au>
parent: Seb <spluque@gmail.com>
0 siblings, 0 replies; 22+ messages in thread
From: Craig Ringer @ 2010-01-18 01:22 UTC (permalink / raw)
To: Seb <spluque@gmail.com>; +Cc: pgsql-sql
Seb wrote:
> On Mon, 18 Jan 2010 08:59:56 +1100,
> Chris <dmagick@gmail.com> wrote:
>
>>> o Should all foreign keys have an index?
>
>> Not necessarily, you might just want the db to enforce the restriction
>> but not actually use the data in it. For example, keep a userid (and
>> timestamp) column of the last person to update a row. You may need it
>> to say "aha - this was last changed on this date and by person X", but
>> you'll never generally use it.
>
>> If you never have a where clause with that column, no need to index
>> it. If you're using it in a join all the time, then yes it would be
>> better to index it.
>
> Thanks for all your pointers!
>
> Do views use the indexes in the underlying tables, whenever say a SELECT
> operation is called on the view? If so, indexes on views don't make any
> sense right?
A view WILL use an index(es) on the underlying table(s) if the planner
thinks the use of those indexes will be helpful. In general it's as if
you substituted the text of the view's query into the query using the
view and executed that composite query.
Sometimes views are very expensive to compute, and avoiding computing
values you're not interested in for a particular query would be very
handy. It's not always possible to tack on a WHERE clause that gets
applied as part of a big complex join; sometimes you land up computing a
lot of data then throwing the vast majority of it away. That's not
really desirable.
If you have very expensive views, a good way to handle this is to
maintain a materialized view and index the materialized view. Pg doesn't
currently have any support for creating and maintaining materialized
views automatically, but it's easy enough to do it with a few triggers
(and you can often do it more efficiently/intelligently than the
database could do in the general case).
I have a couple of materialized views in my schema that make queries
several orders of magnitude faster (!) when querying for current data by
eliminating the need for tree-like multiple self joins. Updates to these
views are cheap, because the triggers on the source tables can
selectively update only the mat.view entries they know are affected by a
given update/insert/delete.
--
Craig Ringer
^ permalink raw reply [nested|flat] 22+ messages in thread
end of thread, other threads:[~2010-01-18 01:22 UTC | newest]
Thread overview: 22+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-05-31 08:42 indexes Remigiusz Sokolowski <rems@gdansk.sprint.pl>
1999-05-31 08:50 ` Vadim Mikheev <vadim@krs.ru>
1999-05-31 09:35 ` Remigiusz Sokolowski <rems@gdansk.sprint.pl>
1999-05-31 11:54 ` Vadim Mikheev <vadim@krs.ru>
1999-05-31 15:17 ` Heiko Wilms <wilms@stud.fh-hannover.de>
1999-05-31 17:28 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-05-31 15:20 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-06-01 06:41 ` Remigiusz Sokolowski <rems@gdansk.sprint.pl>
1999-06-01 07:45 ` Vadim Mikheev <vadim@krs.ru>
1999-06-01 07:57 ` Remigiusz Sokolowski <rems@gdansk.sprint.pl>
1999-06-01 14:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-10-12 20:37 Indexes Aasmund Midttun Godal <postgresql@envisity.com>
2001-10-13 02:33 ` Re: Indexes Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-10-13 14:57 ` Re: Indexes Aasmund Midttun Godal <aasmund@godal.com>
2002-02-05 17:05 indexes Isabelle Brette <isabelle@apartia.fr>
2002-02-05 17:50 ` Chris Ruprecht <chrup999@yahoo.com>
2002-02-06 09:50 ` Isabelle Brette <isabelle@apartia.fr>
2002-02-06 15:29 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2010-01-15 16:04 indexes Seb <spluque@gmail.com>
2010-01-17 21:59 ` Chris <dmagick@gmail.com>
2010-01-18 01:10 ` Seb <spluque@gmail.com>
2010-01-18 01:22 ` Craig Ringer <craig@postnewspapers.com.au>
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