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

Re: Optimization of this SQL sentence

From: Alexander Staubo <alex(at)purefiction(dot)net>
To: Ruben Rubio <ruben(at)rentalia(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Optimization of this SQL sentence
Date: 2006-10-17 09:52:54
Message-ID: 181E6D21-8326-4ED6-A9AE-6F9917C1C319@purefiction.net (view raw or flat)
Thread:
Lists: pgsql-performance
On Oct 17, 2006, at 11:33 , Ruben Rubio wrote:

> CREATE TABLE "comment"
> (
>   idcomment int4 NOT NULL DEFAULT
> nextval('comment_idcomment_seq'::regclass),
[snip 28 columns]
>   CONSTRAINT comment_pkey PRIMARY KEY (idcomment)
> )
>
> Ficha structure:
> No indexes in ficha
> Ficha rows: 17.850
>
> CREATE TABLE ficha
> (
>   idficha int4 NOT NULL DEFAULT nextval 
> ('ficha_idficha_seq'::regclass),
[snip 67 (!) columns]
>   CONSTRAINT pk_ficha PRIMARY KEY (idficha),
>   CONSTRAINT fk_ficha_geonivel6 FOREIGN KEY (idlocacion) REFERENCES
> geonivel6 (idgeonivel6) ON UPDATE NO ACTION ON DELETE NO ACTION
> )

  These tables are particularly egregious examples of ignorant  
database design. You need to understand the relational model (http:// 
en.wikipedia.org/wiki/Relational_model), specifically data  
normalization (http://en.wikipedia.org/wiki/Database_normalization)  
and 3NF (http://en.wikipedia.org/wiki/3NF).

These columns are particularly telling:

   searchengine1 int4,
   searchengine2 int4,
   searchengine3 int4,
   searchengine4 int4,
   searchengine5 int4,
   searchengine6 int4,
   deseo1 int4,
   deseo2 int4,
   deseo3 int4,
   deseo4 int4,
   deseo5 int4,
   deseo6 int4,
   titulomapa_l0 varchar(255),
   titulomapa_l1 varchar(255),
   titulomapa_l2 varchar(255),
   titulomapa_l3 varchar(255),
   titulomapa_l4 varchar(255),
   titulomapa_l5 varchar(255),
   titulomapa_l6 varchar(255),
   titulomapa_l7 varchar(255),
   titulomapa_l8 varchar(255),
   titulomapa_l9 varchar(255),

Refactor into three separate tables:

   create table searchengine (
     idficha int references ficha (idficha),
     searchengine int,
     primary key (idficha, searchengine)
   );

   create table deseo (
     idficha int references ficha (idficha),
     deseo int,
     primary key (idficha, deseo)
   );

   create table titulomapa (
     idficha int references ficha (idficha),
     titulomapa int,
     primary key (idficha, titulomapa)
   );

Now you can find all search engines for a single ficha row:

   select searchengine from searchengine where idficha = n

This design allows for more than 5 search engines per ficha row, and  
allows expressive joins such as:

   select ficha.idficha, searchengine.searchengine
   inner join searchengine on searchengine.idfciha = ficha.idficha

Also, most of your columns are nullable. This alone shows that you  
don't understand your own data.

Lastly, note that in PostgreSQL these length declarations are not  
necessary:

   contacto varchar(255),
   fuente varchar(512),
   prefijopais varchar(10)

Instead, use:

   contacto text,
   fuente text,
   prefijopais text

See the PostgreSQL manual for an explanation of varchar vs. text.

Alexander.


In response to

Responses

pgsql-performance by date

Next:From: Heikki LinnakangasDate: 2006-10-17 10:00:18
Subject: Re: Optimization of this SQL sentence
Previous:From: A. KretschmerDate: 2006-10-17 09:48:41
Subject: Re: Optimization of this SQL sentence

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