pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIndices are not used by the optimizer
3+ messages / 3 participants
[nested] [flat]
* Indices are not used by the optimizer
@ 2003-05-05 12:16 Reiner Dassing <dassing@wettzell.ifag.de>
0 siblings, 2 replies; 3+ messages in thread
From: Reiner Dassing @ 2003-05-05 12:16 UTC (permalink / raw)
To: pgsql-sql
Hello all!
On PostgreSQL V7.3.2 on TRU64 I have a table
and applied indices for that table.
But on a simple query the indices are not used by the optimizer.
(An sequential scan is used which takes a lot of time)
I have done
VACUUM and VACUUM analyze
but without any change to the optimizer.
Can someone give me a hint what I should do to give the
optimizer a start?
--------------------------------------
Well, let's start by the query
wetter=# explain select * from wetter where epoche > '2001-01-01';
QUERY PLAN
-------------------------------------------------------------------------
Seq Scan on wetter (cost=0.00..614795.55 rows=19054156 width=16)
Filter: (epoche > '2001-01-01 00:00:00+00'::timestamp with time zone)
(2 rows)
wetter=#
The table definition is as follows:
\d wetter
Table "public.wetter"
Column | Type | Modifiers
-----------+--------------------------+-----------
sensor_id | integer | not null
epoche | timestamp with time zone | not null
wert | real | not null
Indexes: wetter_pkey primary key btree (sensor_id, epoche),
wetter_epoche_idx btree (epoche),
wetter_sensor_id_idx btree (sensor_id)
Triggers: RI_ConstraintTrigger_45702811,
t_ins_wetter_wetterakt
wetter=#
The trigger information is as follows:
select * from pg_trigger where tgname='RI_ConstraintTrigger_45702811';
tgrelid | tgname | tgfoid | tgtype | tgenabled
| tgisconstraint | tgconstrname | tgconstrrelid | tgdeferrable |
tginitdeferred | tgnargs | tgattr |
tgargs
----------+-------------------------------+--------+--------+-----------+----------------+--------------+---------------+--------------+----------------+---------+--------+---------------------------------------------------------------------------------------
43169106 | RI_ConstraintTrigger_45702811 | 1644 | 21 | t
| t | <unnamed> | 43169098 | f | f
| 6 | |
<unnamed>\000wetter\000sensoren_an_orten\000UNSPECIFIED\000sensor_id\000sensor_id\000
(1 row)
wetter=#
and t_ins_wetter_wetterakt
is a PLPGSQL Funktion which copies some information into another table
when an insert or update is done.
--
Mit freundlichen Gruessen / With best regards
Reiner Dassing
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Indices are not used by the optimizer
@ 2003-05-05 13:33 Rod Taylor <rbt@rbt.ca>
parent: Reiner Dassing <dassing@wettzell.ifag.de>
1 sibling, 0 replies; 3+ messages in thread
From: Rod Taylor @ 2003-05-05 13:33 UTC (permalink / raw)
To: Reiner Dassing <dassing@wettzell.ifag.de>; +Cc: pgsql-sql
Are you really expecting 19 million rows to be returned -- are you
really going to use them all?
How about explain analyze output?
Have you tried using a cursor to allow for parallel processing? (pull
1000 rows, do work, pull next 1000 rows, do work, etc.)
> wetter=# explain select * from wetter where epoche > '2001-01-01';
> QUERY PLAN
> -------------------------------------------------------------------------
> Seq Scan on wetter (cost=0.00..614795.55 rows=19054156 width=16)
> Filter: (epoche > '2001-01-01 00:00:00+00'::timestamp with time zone)
> (2 rows)
--
Rod Taylor <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] Indices are not used by the optimizer
@ 2003-05-05 18:38 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Reiner Dassing <dassing@wettzell.ifag.de>
1 sibling, 0 replies; 3+ messages in thread
From: Achilleus Mantzios @ 2003-05-05 18:38 UTC (permalink / raw)
To: Reiner Dassing <dassing@wettzell.ifag.de>; +Cc: pgsql-sql@postgresql.org, <pgsql-performance@postgresql.org>
Hi Reiner,
normally these kind of subjects must go
to pgsql-performance@postgresql.org
What's important is in pg_class and pg_statistic tables.
Especially, you may check out histgraph bounds
in pg_stats for attribute epoche.
For a test, did you do a
# set enable_seqscan to OFF
??
On Mon, 5 May 2003, Reiner Dassing wrote:
> Hello all!
>
> On PostgreSQL V7.3.2 on TRU64 I have a table
> and applied indices for that table.
> But on a simple query the indices are not used by the optimizer.
> (An sequential scan is used which takes a lot of time)
> I have done
> VACUUM and VACUUM analyze
> but without any change to the optimizer.
>
> Can someone give me a hint what I should do to give the
> optimizer a start?
> --------------------------------------
>
> Well, let's start by the query
>
> wetter=# explain select * from wetter where epoche > '2001-01-01';
> QUERY PLAN
> -------------------------------------------------------------------------
> Seq Scan on wetter (cost=0.00..614795.55 rows=19054156 width=16)
> Filter: (epoche > '2001-01-01 00:00:00+00'::timestamp with time zone)
> (2 rows)
>
> wetter=#
>
>
> The table definition is as follows:
> \d wetter
> Table "public.wetter"
> Column | Type | Modifiers
> -----------+--------------------------+-----------
> sensor_id | integer | not null
> epoche | timestamp with time zone | not null
> wert | real | not null
> Indexes: wetter_pkey primary key btree (sensor_id, epoche),
> wetter_epoche_idx btree (epoche),
> wetter_sensor_id_idx btree (sensor_id)
> Triggers: RI_ConstraintTrigger_45702811,
> t_ins_wetter_wetterakt
>
> wetter=#
>
>
> The trigger information is as follows:
> select * from pg_trigger where tgname='RI_ConstraintTrigger_45702811';
> tgrelid | tgname | tgfoid | tgtype | tgenabled
> | tgisconstraint | tgconstrname | tgconstrrelid | tgdeferrable |
> tginitdeferred | tgnargs | tgattr |
> tgargs
> ----------+-------------------------------+--------+--------+-----------+----------------+--------------+---------------+--------------+----------------+---------+--------+---------------------------------------------------------------------------------------
> 43169106 | RI_ConstraintTrigger_45702811 | 1644 | 21 | t
> | t | <unnamed> | 43169098 | f | f
> | 6 | |
> <unnamed>\000wetter\000sensoren_an_orten\000UNSPECIFIED\000sensor_id\000sensor_id\000
> (1 row)
>
> wetter=#
>
>
> and t_ins_wetter_wetterakt
> is a PLPGSQL Funktion which copies some information into another table
> when an insert or update is done.
>
>
>
--
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-210-8981112
fax: +30-210-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-05-05 18:38 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-05 12:16 Indices are not used by the optimizer Reiner Dassing <dassing@wettzell.ifag.de>
2003-05-05 13:33 ` Rod Taylor <rbt@rbt.ca>
2003-05-05 18:38 ` Achilleus Mantzios <achill@matrix.gatewaynet.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