inconsistend performance
От | fredrik chabot |
---|---|
Тема | inconsistend performance |
Дата | |
Msg-id | 3D6292ED.4050902@f6.nl обсуждение исходный текст |
Список | 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
В списке pgsql-bugs по дате отправления: