Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group