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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
