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 |
+------------------------------+---------------------------------------------+
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
