pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIndex not used - now me
6+ messages / 4 participants
[nested] [flat]
* Index not used - now me
@ 2004-02-09 13:50 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 1 reply; 6+ messages in thread
From: Christoph Haller @ 2004-02-09 13:50 UTC (permalink / raw)
To: pgsql-sql
I know there have been dozens of threads on this subject and
I have searched the archives well (I hope at least), but still ...
I have
select version();
version
--------------------------------------------------------------
PostgreSQL 7.3.4 on hppa-hp-hpux10.20, compiled by GCC 2.8.1
show enable_seqscan ;
enable_seqscan
----------------
off
\d ParDef_DimRange
Table "public.pardef_dimrange"
Column | Type | Modifiers
---------------+----------+-----------
primary_key | integer | not null
dim_pointer | smallint | not null
dimensions_nr | smallint | not null
first | smallint | not null
last | smallint | not null
max_range | smallint | not null
Indexes: pd_dptr_index btree (dim_pointer),
pd_pkey_index btree (primary_key)
explain analyze SELECT Dim_Pointer,Dimensions_Nr,First,Last FROM ParDef_DimRange
WHERE Dim_Pointer = 162::smallint ORDER BY Dim_Pointer,Dimensions_Nr;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Sort (cost=7.02..7.03 rows=2 width=8) (actual time=0.72..0.75 rows=2 loops=1)
Sort Key: dim_pointer, dimensions_nr
-> Index Scan using pd_dptr_index on pardef_dimrange (cost=0.00..7.01 rows=2 width=8) (actual time=0.20..0.28 rows=2 loops=1)
Index Cond: (dim_pointer = 162::smallint)
Total runtime: 1.24 msec
excellent, but
explain analyze SELECT Dim_Pointer,Dimensions_Nr,First,Last FROM ParDef_DimRange
WHERE Dim_Pointer = 162 ORDER BY Dim_Pointer,Dimensions_Nr;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Sort (cost=100000062.22..100000062.23 rows=2 width=8) (actual time=32.44..32.46 rows=2 loops=1)
Sort Key: dim_pointer, dimensions_nr
-> Seq Scan on pardef_dimrange (cost=100000000.00..100000062.21 rows=2 width=8) (actual time=11.06..31.93 rows=2 loops=1)
Filter: (dim_pointer = 162)
Total runtime: 32.79 msec
That's not nice. Will this go away on 7.4?
\d Transfer_ModRange
Table "public.transfer_modrange"
Column | Type | Modifiers
----------------+----------+-----------
module_pointer | smallint | not null
from_module | smallint | not null
to_module | smallint | not null
primary_key | integer | not null
Indexes: tmr_primkey_index btree (primary_key)
explain analyze SELECT Module_Pointer FROM Transfer_ModRange
WHERE Primary_Key = 13 AND From_Module <= 2 AND To_Module >= 2 ;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Index Scan using tmr_primkey_index on transfer_modrange (cost=0.00..115.09 rows=14 width=2) (actual time=2.11..2.11 rows=0 loops=1)
Index Cond: (primary_key = 13)
Filter: ((from_module <= 2) AND (to_module >= 2))
Total runtime: 2.46 msec
Now
set enable_seqscan to on ;
explain analyze SELECT Module_Pointer FROM Transfer_ModRange
WHERE Primary_Key = 13 AND From_Module <= 2 AND To_Module >= 2 ;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Seq Scan on transfer_modrange (cost=0.00..104.93 rows=14 width=2) (actual time=45.91..45.91 rows=0 loops=1)
Filter: ((primary_key = 13) AND (from_module <= 2) AND (to_module >= 2))
Total runtime: 46.19 msec
That's odd. May I please have an explanation for this.
Probably I should mention both tables have far less than 10.000 tuples.
VACUUM and ANALYZE was done just before.
TIA
Regards, Christoph
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Index not used - now me
@ 2004-02-09 14:28 Paul Thomas <paul@tmsl.demon.co.uk>
parent: Christoph Haller <ch@rodos.fzk.de>
0 siblings, 1 reply; 6+ messages in thread
From: Paul Thomas @ 2004-02-09 14:28 UTC (permalink / raw)
To: pgsql-sql
On 09/02/2004 12:50 Christoph Haller wrote:
> I know there have been dozens of threads on this subject and
> I have searched the archives well (I hope at least), but still ...
>
> I have
> select version();
> version
> --------------------------------------------------------------
> PostgreSQL 7.3.4 on hppa-hp-hpux10.20, compiled by GCC 2.8.1
>
> show enable_seqscan ;
> enable_seqscan
> ----------------
> off
>
> \d ParDef_DimRange
> Table "public.pardef_dimrange"
> Column | Type | Modifiers
> ---------------+----------+-----------
> primary_key | integer | not null
> dim_pointer | smallint | not null
> dimensions_nr | smallint | not null
> first | smallint | not null
> last | smallint | not null
> max_range | smallint | not null
> Indexes: pd_dptr_index btree (dim_pointer),
> pd_pkey_index btree (primary_key)
>
> explain analyze SELECT Dim_Pointer,Dimensions_Nr,First,Last FROM
> ParDef_DimRange
> WHERE Dim_Pointer = 162::smallint ORDER BY Dim_Pointer,Dimensions_Nr;
> QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------------------------
> Sort (cost=7.02..7.03 rows=2 width=8) (actual time=0.72..0.75 rows=2
> loops=1)
> Sort Key: dim_pointer, dimensions_nr
> -> Index Scan using pd_dptr_index on pardef_dimrange
> (cost=0.00..7.01 rows=2 width=8) (actual time=0.20..0.28 rows=2 loops=1)
> Index Cond: (dim_pointer = 162::smallint)
> Total runtime: 1.24 msec
>
> excellent, but
>
> explain analyze SELECT Dim_Pointer,Dimensions_Nr,First,Last FROM
> ParDef_DimRange
> WHERE Dim_Pointer = 162 ORDER BY Dim_Pointer,Dimensions_Nr;
> QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------------------
> Sort (cost=100000062.22..100000062.23 rows=2 width=8) (actual
> time=32.44..32.46 rows=2 loops=1)
> Sort Key: dim_pointer, dimensions_nr
> -> Seq Scan on pardef_dimrange (cost=100000000.00..100000062.21
> rows=2 width=8) (actual time=11.06..31.93 rows=2 loops=1)
> Filter: (dim_pointer = 162)
> Total runtime: 32.79 msec
>
> That's not nice. Will this go away on 7.4?
No. AFAIK, 7.4 is still very strict about column types so will still need
to explicitly cast to smallint.
>
> \d Transfer_ModRange
> Table "public.transfer_modrange"
> Column | Type | Modifiers
> ----------------+----------+-----------
> module_pointer | smallint | not null
> from_module | smallint | not null
> to_module | smallint | not null
> primary_key | integer | not null
> Indexes: tmr_primkey_index btree (primary_key)
>
> explain analyze SELECT Module_Pointer FROM Transfer_ModRange
> WHERE Primary_Key = 13 AND From_Module <= 2 AND To_Module >= 2 ;
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------------------------------------
> Index Scan using tmr_primkey_index on transfer_modrange
> (cost=0.00..115.09 rows=14 width=2) (actual time=2.11..2.11 rows=0
> loops=1)
> Index Cond: (primary_key = 13)
> Filter: ((from_module <= 2) AND (to_module >= 2))
> Total runtime: 2.46 msec
>
> Now
> set enable_seqscan to on ;
> explain analyze SELECT Module_Pointer FROM Transfer_ModRange
> WHERE Primary_Key = 13 AND From_Module <= 2 AND To_Module >= 2 ;
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------------
> Seq Scan on transfer_modrange (cost=0.00..104.93 rows=14 width=2)
> (actual time=45.91..45.91 rows=0 loops=1)
> Filter: ((primary_key = 13) AND (from_module <= 2) AND (to_module >=
> 2))
> Total runtime: 46.19 msec
>
> That's odd. May I please have an explanation for this.
> Probably I should mention both tables have far less than 10.000 tuples.
> VACUUM and ANALYZE was done just before.
That's because it's acually more efficent to do a seqscan on your small
table. When you have only a small table (like many of us do when testing),
the whole table will probably fit on one 8K page so the lowest cost
operation (= quickest) is to get that page. It was disabling seqscan that
was forcing an index scan to appear to be the least costly operation. BTW,
you can't actually prevent PG doing a seqscan if there's no alternative
plan. All set enable_seqscan = false does is make a seqscan appear very
expensive so that the planner is less likely to pick it.
HTH
--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants |
http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+---------------------------------------------+
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Index not used - now me
@ 2004-02-09 15:43 Greg Stark <gsstark@mit.edu>
parent: Christoph Haller <ch@rodos.fzk.de>
1 sibling, 1 reply; 6+ messages in thread
From: Greg Stark @ 2004-02-09 15:43 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: paul@tmsl.demon.co.uk; pgsql-sql
Christoph Haller <ch@rodos.fzk.de> writes:
> Why appear? If the Index Scan has a Total runtime: 2.46 msec and the Seq Scan
> a Total runtime: 46.19 msec, then the Index Scan is much faster.
> Or am I completely off the track reading the explain analyze output?
To estimate the relative costs of a sequential scan and an index scan Postgres
has to take into account the likelihood the blocks needed will be the disk
cache. In your example your database is otherwise idle and the entire table is
small enough that the entire index is probably in cache.
This means that the random access pattern of the index isn't really hurting
the index scan at all. Whereas in a busy database with less available RAM the
random access pattern makes a big difference.
You could try raising effective_cache_size to give postgres a better chance at
guessing that all the blocks will be in cache. But that may no longer be true
when the query is run on a busy database.
You could also try lowering random_page_cost. Some people find as low as 1.2
or so to be useful, but that would almost certainly be lying to postgres about
the costs of random access and would cause it to use index scans aggressively
even when they're not faster.
--
greg
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Index not used - now me
@ 2004-02-09 16:02 Christoph Haller <ch@rodos.fzk.de>
parent: Paul Thomas <paul@tmsl.demon.co.uk>
0 siblings, 2 replies; 6+ messages in thread
From: Christoph Haller @ 2004-02-09 16:02 UTC (permalink / raw)
To: paul@tmsl.demon.co.uk; +Cc: pgsql-sql
>
>
> On 09/02/2004 12:50 Christoph Haller wrote:
>
> >
> > \d Transfer_ModRange
> > Table "public.transfer_modrange"
> > Column | Type | Modifiers
> > ----------------+----------+-----------
> > module_pointer | smallint | not null
> > from_module | smallint | not null
> > to_module | smallint | not null
> > primary_key | integer | not null
> > Indexes: tmr_primkey_index btree (primary_key)
> >
> > explain analyze SELECT Module_Pointer FROM Transfer_ModRange
> > WHERE Primary_Key = 13 AND From_Module <= 2 AND To_Module >= 2 ;
> > QUERY PLAN
> > --------------------------------------------------------------------------------------------------------------------------------------
> > Index Scan using tmr_primkey_index on transfer_modrange
> > (cost=0.00..115.09 rows=14 width=2) (actual time=2.11..2.11 rows=0
> > loops=1)
> > Index Cond: (primary_key = 13)
> > Filter: ((from_module <= 2) AND (to_module >= 2))
> > Total runtime: 2.46 msec
> >
> > Now
> > set enable_seqscan to on ;
> > explain analyze SELECT Module_Pointer FROM Transfer_ModRange
> > WHERE Primary_Key = 13 AND From_Module <= 2 AND To_Module >= 2 ;
> > QUERY PLAN
> > --------------------------------------------------------------------------------------------------------------
> > Seq Scan on transfer_modrange (cost=0.00..104.93 rows=14 width=2)
> > (actual time=45.91..45.91 rows=0 loops=1)
> > Filter: ((primary_key = 13) AND (from_module <= 2) AND (to_module >=
> > 2))
> > Total runtime: 46.19 msec
> >
> > That's odd. May I please have an explanation for this.
> > Probably I should mention both tables have far less than 10.000 tuples.
> > VACUUM and ANALYZE was done just before.
>
> That's because it's acually more efficent to do a seqscan on your small
> table. When you have only a small table (like many of us do when testing),
> the whole table will probably fit on one 8K page so the lowest cost
> operation (= quickest) is to get that page. It was disabling seqscan that
> was forcing an index scan to appear to be the least costly operation. BTW,
> you can't actually prevent PG doing a seqscan if there's no alternative
> plan. All set enable_seqscan = false does is make a seqscan appear very
> expensive so that the planner is less likely to pick it.
>
> HTH
>
> --
> Paul Thomas
>
Thanks for your reply so far, but there is one thing I still don't understand.
You wrote
It was disabling seqscan that
was forcing an index scan to appear to be the least costly operation.
Why appear? If the Index Scan has a Total runtime: 2.46 msec and the Seq Scan
a Total runtime: 46.19 msec, then the Index Scan is much faster.
Or am I completely off the track reading the explain analyze output?
Again, thanks for your time.
Regards, Christoph
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Index not used - now me
@ 2004-02-09 16:38 Tom Lane <tgl@sss.pgh.pa.us>
parent: Greg Stark <gsstark@mit.edu>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2004-02-09 16:38 UTC (permalink / raw)
To: Greg Stark <gsstark@mit.edu>; +Cc: Christoph Haller <ch@rodos.fzk.de>; paul@tmsl.demon.co.uk; pgsql-sql
Greg Stark <gsstark@mit.edu> writes:
> You could also try lowering random_page_cost. Some people find as low as 1.2
> or so to be useful, but that would almost certainly be lying to postgres about
> the costs of random access and would cause it to use index scans aggressively
> even when they're not faster.
If the database is small enough to mostly fit in kernel disk cache, then
a very low random_page_cost is telling the truth, not lying. One might
also think about raising the various CPU_xxx cost parameters, since CPU
effort becomes proportionally much more significant when "I/O" only
requires a trip to kernel buffers.
However, Christoph's example looks to me like a classic case of testing
a toy table and assuming the results will extrapolate to production-size
cases. This table is small enough that it hardly matters which method
the planner chooses. I'd counsel being very wary about adjusting the
cost settings based on only this example.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Index not used - now me
@ 2004-02-09 17:27 Paul Thomas <paul@tmsl.demon.co.uk>
parent: Christoph Haller <ch@rodos.fzk.de>
1 sibling, 0 replies; 6+ messages in thread
From: Paul Thomas @ 2004-02-09 17:27 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: pgsql-sql
On 09/02/2004 15:02 Christoph Haller wrote:
> [snip]
> Thanks for your reply so far, but there is one thing I still don't
> understand.
> You wrote
> It was disabling seqscan that
> was forcing an index scan to appear to be the least costly operation.
>
> Why appear? If the Index Scan has a Total runtime: 2.46 msec and the Seq
> Scan
> a Total runtime: 46.19 msec, then the Index Scan is much faster.
> Or am I completely off the track reading the explain analyze output?
No, I think it's me who's not reading the output correctly :( I didn't
look closely enough to spot the run time difference. How many rows are
there in the table?
--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants |
http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+---------------------------------------------+
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-02-09 17:27 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-09 13:50 Index not used - now me Christoph Haller <ch@rodos.fzk.de>
2004-02-09 14:28 ` Paul Thomas <paul@tmsl.demon.co.uk>
2004-02-09 16:02 ` Christoph Haller <ch@rodos.fzk.de>
2004-02-09 15:43 ` Greg Stark <gsstark@mit.edu>
2004-02-09 16:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-09 17:27 ` Paul Thomas <paul@tmsl.demon.co.uk>
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