inconsistend performance

From: fredrik chabot <fredrik(at)f6(dot)nl>
To: pgsql-bugs <pgsql-bugs(at)postgresql(dot)org>
Subject: inconsistend performance
Date: 2002-08-20 19:05:17
Message-ID: 3D6292ED.4050902@f6.nl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hello experts,

Abstract;

If I select 1 record on the primary key I know exists postgresql is very
fast, selecting the previous or next record (with limit 1) is horible.

Given this table:

CREATE TABLE dsrgl ("f6lock" int4, "afd" varchar( 4), "dss" int4, "dat"
varchar( 16), "srt" int4, "akt" varchar( 12), "oms" varchar( 20), "anc"
int4, "vkk" float8, "vkka" float8, "vkkt" float8, "vkkv" varchar( 4),
"vkkr" float8, "fav" varchar( 12), "ibs" int4, "nkk" float8, "nkkv"
varchar( 4), "nkkr" float8, "vko" float8, "vkoa" float8, "vkot" float8,
"vkov" varchar( 4), "vkor" float8, "faa" varchar( 12), "vbs" int4,
"sta" int4, "nko" float8, "nkov" varchar( 4), "nkor" float8, "stb"
int4, "stc" int4, "std" int4, "ste" int4, "fsa" int4, "fsb" int4, "fsc"
int4, "fsd" int4, "fse" int4,
CONSTRAINT dsrgl_primary PRIMARY KEY("afd","dss","dat"));

Insert +/- 700000 rows and then:

SELECT "afd", "dss", "dat", "srt", "akt", "oms", "anc", "vkk", "vkka",
"vkkt", "vkkv", "vkkr", "fav", "ibs", "nkk", "nkkv", "nkkr", "vko",
"vkoa", "vkot", "vkov", "vkor", "faa", "vbs", "sta", "nko", "nkov",
"nkor", "stb", "stc", "std", "ste", "fsa", "fsb", "fsc", "fsd", "fse",
"usr", "dtv", "dti" FROM dsrgl
WHERE ( "afd" = 'fb' and "dss" = 13 and "dat" = '20020712143411' ) ;

takes approx 0.000939 seconds

SELECT "afd", "dss", "dat", "srt", "akt", "oms", "anc", "vkk", "vkka",
"vkkt", "vkkv", "vkkr", "fav", "ibs", "nkk", "nkkv", "nkkr", "vko",
"vkoa", "vkot", "vkov", "vkor", "faa", "vbs", "sta", "nko", "nkov",
"nkor", "stb", "stc", "std", "ste", "fsa", "fsb", "fsc", "fsd", "fse",
"usr", "dtv", "dti" FROM dsrgl
WHERE ( "afd" = 'fb' and "dss" = 13 and "dat" < '2002080719163600' )
or ( "afd" = 'fb' and "dss" < 13 ) or ( "afd" < 'fb' )
ORDER BY "afd" DESC , "dss" DESC , "dat" DESC LIMIT 1;

takes approx 7.048736 seconds

If I let postgresql explain it to me:

NOTICE: QUERY PLAN:

Limit (cost=0.00..910.68 rows=100 width=344)
-> Index Scan Backward using dsrgl_primary on dsrgl
(cost=0.00..325691.57 rows=35764 width=344)

EXPLAIN

Is it something I'm doing wrong or doesn't postgresql optimizer
understand my select and if so is there something I can do so it will
understand?

thanks,

fredrik chabot

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Jean-Luc Lachance 2002-08-20 19:51:54 Re: inconsistend performance
Previous Message Peter Eisentraut 2002-08-20 18:03:28 Re: [BUGS] Long strings, short varchars