Performance difference between char and int2 columns

Поиск
Список
Период
Сортировка
От Yavuz Kavus
Тема Performance difference between char and int2 columns
Дата
Msg-id 95b926fc0606030730o762e6610n9a2790a49be29d9b@mail.gmail.com
обсуждение исходный текст
Ответы Re: Performance difference between char and int2 columns  (Alban Hertroys <alban@magproductions.nl>)
Список pgsql-general
hi everybody.
 
i have two table as :
 
CREATE TABLE "public"."tb_section_phones" (
  "section_id" INTEGER NOT NULL,
  "ptype_id" INTEGER NOT NULL,
  "country_code" SMALLINT DEFAULT 90,
  "domain_code" SMALLINT,
  "number" CHAR(7) NOT NULL,
  "extension" VARCHAR(6),
  CONSTRAINT "tb_section_phones_fk" FOREIGN KEY ("section_id")
    REFERENCES "public"."tb_sections"("sect_id")
    ON DELETE CASCADE
    ON UPDATE CASCADE
    NOT DEFERRABLE,
  CONSTRAINT "tb_section_phones_fk1" FOREIGN KEY ("ptype_id")
    REFERENCES "public"."tb_phone_types"("ptype_id")
    ON DELETE CASCADE
    ON UPDATE CASCADE
    NOT DEFERRABLE
) WITHOUT OIDS;
 
 
CREATE TABLE "public"."tb_section_phones_temp" (
  "sect_id" INTEGER NOT NULL,
  "ptype_id" INTEGER NOT NULL,
  "country_code" CHAR(2) DEFAULT 90,
  "domain_code" CHAR(3),
  "number" CHAR(7) NOT NULL,
  "extension" VARCHAR(6),
  CONSTRAINT "tb_section_phones_fk" FOREIGN KEY ("sect_id")
    REFERENCES "public"."tb_sections"("sect_id")
    ON DELETE CASCADE
    ON UPDATE CASCADE
    NOT DEFERRABLE,
  CONSTRAINT "tb_section_phones_fk2" FOREIGN KEY ("ptype_id")
    REFERENCES "public"."tb_phone_types"("ptype_id")
    ON DELETE CASCADE
    ON UPDATE CASCADE
    NOT DEFERRABLE
) WITHOUT OIDS;
 
the differences between two table are :
in the first table country_code type is smallint, in the second table char(2)
in the first table domain_code type is smallint, in the second table char(3)
.
 
The problem is that:
my both table has 10 records.
when i run this query on first table :
   select * from tb_section_phones;
   it lasts 0.02 sec.
when i run this query on second table :
   select * from tb_section_phones_temp;
   it lasts 0.13 sec.
 
6.5 times slower.
why so performance difference?
should i prefer int as column type?
 
thanks in advance.

В списке pgsql-general по дате отправления:

Предыдущее
От: "Magnus Hagander"
Дата:
Сообщение: Re: psql: krb5_sendauth: Bad application version was sent (via sendauth) - Windows 2000, MIT Kerberos, PG v 8.1.1
Следующее
От: "Harald Armin Massa"
Дата:
Сообщение: Re: Using postgresql as desktop DB