createdb voitures
curl -kL https://dali.bo/tp_voitures -o /tmp/voitures.dmp
pg_restore -d voitures /tmp/voitures.dmp
# un message sur le schéma public préexistant est normal

SELECT * FROM voitures
WHERE caracteristiques LIKE '%abs%'

ALTER TABLE voitures ADD COLUMN couleur text
CHECK (couleur IN ('rouge','bleu','vert')) ;

CREATE TYPE color AS ENUM ('bleu', 'rouge', 'vert') ;
ALTER TABLE voitures ADD COLUMN couleur color ;

CREATE TABLE couleurs (
    couleur_id int  PRIMARY KEY,
    couleur    text
) ;
ALTER TABLE voitures ADD COLUMN couleur_id REFERENCES couleurs ;

CREATE TABLE mere (id integer, t text);

CREATE TABLE fille (id integer, mere_id integer, t text);

ALTER TABLE mere ADD CONSTRAINT pk_mere PRIMARY KEY (id);

ALTER TABLE fille
  ADD CONSTRAINT fk_mere_fille
      FOREIGN KEY (mere_id)
      REFERENCES mere (id)
         MATCH FULL
         ON UPDATE NO ACTION
         ON DELETE CASCADE
         DEFERRABLE;

BEGIN ;
SET CONSTRAINTS ALL DEFERRED ;

INSERT INTO fille (id, mere_id, t) VALUES (1, 1, 'val1');
INSERT INTO fille (id, mere_id, t) VALUES (2, 2, 'val2');

INSERT INTO mere (id, t) VALUES (1, 'val1'), (2, 'val2');

COMMIT;

CREATE TABLE personnes (id SERIAL PRIMARY KEY);

CREATE TABLE personne_attributs (
  id_pers INTEGER NOT NULL,
  nom_attr varchar(20) NOT NULL,
  val_attr varchar(100) NOT NULL
);

INSERT INTO personnes (id) VALUES (nextval('personnes_id_seq')) RETURNING id;

 id
----
  1

INSERT INTO personne_attributs (id_pers, nom_attr, val_attr)
  VALUES (1, 'nom', 'Prunelle'),
         (1, 'prenom', 'Léon');
(...)

CREATE EXTENSION hstore;
CREATE TABLE personnes (id SERIAL PRIMARY KEY, attributs hstore);

INSERT INTO personnes (attributs) VALUES ('nom=>Prunelle, prenom=>Léon');
INSERT INTO personnes (attributs) VALUES ('prenom=>Gaston,nom=>Lagaffe');
INSERT INTO personnes (attributs) VALUES ('nom=>DeMaesmaker');

SELECT * FROM personnes;

 id |              attributs
----+--------------------------------------
  1 | "nom"=>"Prunelle", "prenom"=>"Léon"
  2 | "nom"=>"Lagaffe", "prenom"=>"Gaston"
  3 | "nom"=>"DeMaesmaker"

SELECT id, attributs->'prenom' AS prenom FROM personnes;

 id |  prenom
----+----------
  1 | Léon
  2 | Gaston
  3 |

SELECT id, attributs->'nom' AS nom FROM personnes;

 id |     nom
----+-------------
  1 | Prunelle
  2 | Lagaffe
  3 | DeMaesmaker

CREATE TABLE personnes (
  per_id SERIAL PRIMARY KEY,
  nom VARCHAR(50) NOT NULL,
  pnom VARCHAR(50) NOT NULL,
  ...
);

CREATE TABLE telephones (
  per_id INTEGER NOT NULL,
  numero VARCHAR(20),
  PRIMARY KEY (per_id, numero),
  FOREIGN KEY (per_id) REFERENCES personnes (per_id)
);

CREATE TABLE personnes (
  per_id SERIAL PRIMARY KEY,
  nom VARCHAR(50) NOT NULL,
  pnom VARCHAR(50) NOT NULL,
  numero VARCHAR(20)[]
);

-- Ajout d'une personne
INSERT INTO personnes (nom, pnom, numero)
     VALUES ('Simpson', 'Omer', '{0607080910}');

SELECT *
  FROM personnes;

 per_id |   nom   | pnom |    numero
--------+---------+------+--------------
      1 | Simpson | Omer | {0607080910}

-- Ajout d'un numéro de téléphone pour une personne donnée :
UPDATE personnes
   SET numero = numero || '{0102030420}'
 WHERE per_id = 1;

-- Vérification de l'ajout :
SELECT * FROM personnes;

 per_id |   nom   | pnom |         numero
--------+---------+------+-------------------------
      1 | Simpson | Omer | {0607080910,0102030420}

-- Séparation des éléments du tableau :
SELECT per_id, nom, pnom, unnest(numero) AS numero
  FROM personnes;

 per_id |   nom   | pnom |   numero
--------+---------+------+------------
      1 | Simpson | Omer | 0607080910
      1 | Simpson | Omer | 0102030420

CREATE TABLE valeurs_capteur (d timestamp, v smallint);
-- soit 8 + 2 = 10 octets de données utiles par ligne

--  100 valeurs chaque seconde pendant 100 000 s = 10 millions de lignes
INSERT INTO valeurs_capteur (d, v)
SELECT current_timestamp + (i%100000) * interval '1 s',
       (random()*200)::smallint
FROM   generate_series (1,10000000) i ;

SELECT pg_size_pretty(pg_relation_size ('valeurs_capteur')) ;

 pg_size_pretty
----------------
  422 MB
-- dont seulement 10 octets * 10 Mlignes = 100 Mo de données utiles

CREATE TABLE valeurs_capteur_2 (d timestamp, tv smallint[]);

INSERT INTO valeurs_capteur_2
SELECT current_timestamp+  (i%100000) * interval '1 s' ,
       array_agg((random()*200)::smallint)
FROM   generate_series (1,10000000) i
GROUP BY 1 ;

SELECT pg_size_pretty(pg_relation_size ('valeurs_capteur_2'));

 pg_size_pretty
----------------
  25 MB
-- soit par ligne :
-- 23 octets d'entête + 8 pour la date + 100 * 2 octets de valeurs smallint

SELECT unnest(tv) FROM valeurs_capteur_2
WHERE  d = '2018-06-15 22:07:47.651295' ;

CREATE INDEX tvx ON valeurs_capteur_2 USING gin(tv);

EXPLAIN (ANALYZE) SELECT * FROM valeurs_capteur_2 WHERE '{199}' && tv ;

                            QUERY PLAN
---------------------------------------------------------------------------------
 Bitmap Heap Scan on valeurs_capteur_2 (cost=311.60..1134.20 rows=40000 width=232)
                                  (actual time=8.299..20.460 rows=39792 loops=1)
   Recheck Cond: ('{199}'::smallint[] && tv)
   Heap Blocks: exact=3226
   ->  Bitmap Index Scan on tvx  (cost=0.00..301.60 rows=40000 width=0)
                          (actual time=7.723..7.723 rows=39792 loops=1)
         Index Cond: ('{199}'::smallint[] && tv)
 Planning time: 0.214 ms
 Execution time: 22.386 ms

test=# CREATE TABLE comptes (compte_id serial PRIMARY KEY, solde numeric);
CREATE TABLE

test=# INSERT INTO comptes (solde) VALUES (100000000.1), (10.1), (10000.2),
(100000000000000.1);
INSERT 0 4

test=# SELECT SUM(solde) FROM comptes;
        sum
-------------------
 100000100010010.5

ALTER TABLE utilisateurs ADD COLUMN username_KTL VARCHAR(11);
UPDATE utilisateurs SET username_KTL=username;
ALTER TABLE utilisateurs DROP COLUMN username;
ALTER TABLE utilisateurs RENAME username_KTL TO username

CREATE TABLE insere (id integer PRIMARY KEY, col1 varchar(5), col2 integer);

INSERT INTO insere VALUES (1, 'XX', 10);

SELECT * FROM insere ;

 id | col1 | col2
----+------+------
  1 | XX   |   10

ALTER TABLE insere ADD COLUMN col1_tmp varchar(6);
UPDATE insere SET col1_tmp = col1;
ALTER TABLE insere DROP COLUMN col1;
ALTER TABLE insere RENAME COLUMN col1_tmp TO col1;

INSERT INTO insere VALUES (2, 'XXX', 10);

ERROR:  invalid input syntax for integer: "XXX"
LINE 1: INSERT INTO insere VALUES (2, 'XXX', 10);
                                     ^

INSERT INTO insere (id, col1, col2) VALUES (2, 'XXX', 10);

SELECT * FROM insere ;

 id | col2 | col1
----+------+------
  1 |   10 | XX
  2 |   10 | XXX

SELECT  Article.datem                          AS Article_1_9,
    Article.degre_alcool                   AS Article_1_10,
    Article.id                             AS Article_1_19,
    Article.iddf_categor                   AS Article_1_20,
    Article.iddp_clsvtel                   AS Article_1_21,
    Article.iddp_cdelist                   AS Article_1_22,
    Article.iddf_cd_prix                   AS Article_1_23,
    Article.iddp_agreage                   AS Article_1_24,
    Article.iddp_codelec                   AS Article_1_25,
    Article.idda_compo                     AS Article_1_26,
    Article.iddp_comptex                   AS Article_1_27,
    Article.iddp_cmptmat                   AS Article_1_28,
    Article.idda_articleparent             AS Article_1_29,
    Article.iddp_danger                    AS Article_1_30,
    Article.iddf_fabric                    AS Article_1_33,
    Article.iddp_marqcom                   AS Article_1_34,
    Article.iddp_nomdoua                   AS Article_1_35,
    Article.iddp_pays                      AS Article_1_37,
    Article.iddp_recept                    AS Article_1_40,
    Article.idda_unalvte                   AS Article_1_42,
    Article.iddb_sitecl                   AS Article_1_43,
    Article.lib_caisse                     AS Article_1_49,
    Article.lib_com                        AS Article_1_50,
    Article.maj_en_attente                 AS Article_1_61,
    Article.qte_stk                        AS Article_1_63,
    Article.ref_tech                       AS Article_1_64,
    1                                      AS Article_1_70,
    CASE
      WHEN (SELECT COUNT(MA.id)
            FROM   da_majart MA
                   join da_majmas MM
                     ON MM.id = MA.idda_majmas
                   join gt_tmtprg TMT
                     ON TMT.id = MM.idgt_tmtprg
                   join gt_prog PROG
                     ON PROG.id = TMT.idgt_prog
            WHERE  idda_article = Article.id
               AND TO_DATE(TO_CHAR(PROG.date_lancement, 'DDMMYYYY')
                           || TO_CHAR(PROG.heure_lancement, ' HH24:MI:SS'),
                            'DDMMYYYY HH24:MI:SS') >= SYSDATE) >= 1 THEN 1
      ELSE 0
    END                                           AS Article_1_74,
    Article.iddp_compnat                          AS Article_2_0,
    Article.iddp_modven                           AS Article_2_1,
    Article.iddp_nature                           AS Article_2_2,
    Article.iddp_preclin                          AS Article_2_3,
    Article.iddp_raybala                          AS Article_2_4,
    Article.iddp_sensgrt                          AS Article_2_5,
    Article.iddp_tcdtfl                           AS Article_2_6,
    Article.iddp_unite                            AS Article_2_8,
    Article.idda_untgrat                          AS Article_2_9,
    Article.idda_unpoids                          AS Article_2_10,
    Article.iddp_unilogi                          AS Article_2_11,
    ArticleComplement.datem                       AS ArticleComplement_5_6,
    ArticleComplement.extgar_depl                 AS ArticleComplement_5_9,
    ArticleComplement.extgar_mo                   AS ArticleComplement_5_10,
    ArticleComplement.extgar_piece                AS ArticleComplement_5_11,
    ArticleComplement.id                          AS ArticleComplement_5_20,
    ArticleComplement.iddf_collect                AS ArticleComplement_5_22,
    ArticleComplement.iddp_gpdtcul                AS ArticleComplement_5_23,
    ArticleComplement.iddp_support                AS ArticleComplement_5_25,
    ArticleComplement.iddp_typcarb                AS ArticleComplement_5_27,
    ArticleComplement.mt_ext_gar                  AS ArticleComplement_5_36,
    ArticleComplement.pres_cpt                    AS ArticleComplement_5_44,
    GenreProduitCulturel.code                     AS GenreProduitCulturel_6_0,
    Collection.libelle                            AS Collection_8_1,
    Gtin.date_dern_vte                            AS Gtin_10_0,
    Gtin.gtin                                     AS Gtin_10_1,
    Gtin.id                                       AS Gtin_10_3,
    Fabricant.code                                AS Fabricant_14_0,
    Fabricant.nom                                 AS Fabricant_14_2,
    ClassificationVenteLocale.niveau1             AS ClassificationVenteL_16_2,
    ClassificationVenteLocale.niveau2             AS ClassificationVenteL_16_3,
    ClassificationVenteLocale.niveau3             AS ClassificationVenteL_16_4,
    ClassificationVenteLocale.niveau4             AS ClassificationVenteL_16_5,
    MarqueCommerciale.code                        AS MarqueCommerciale_18_0,
    MarqueCommerciale.libellelong                 AS MarqueCommerciale_18_4,
    Composition.code                              AS Composition_20_0,
    CompositionTextile.code                       AS CompositionTextile_21_0,
    AssoArticleInterfaceBalance.datem             AS AssoArticleInterface_23_0,
    AssoArticleInterfaceBalance.lib_envoi         AS AssoArticleInterface_23_3,
    AssoArticleInterfaceCaisse.datem              AS AssoArticleInterface_24_0,
    AssoArticleInterfaceCaisse.lib_envoi          AS AssoArticleInterface_24_3,
    NULL                                          AS TypeTraitement_25_0,
    NULL                                          AS TypeTraitement_25_1,
    RayonBalance.code                             AS RayonBalance_31_0,
    RayonBalance.max_cde_article                  AS RayonBalance_31_5,
    RayonBalance.min_cde_article                  AS RayonBalance_31_6,
    TypeTare.code                                 AS TypeTare_32_0,
    GrilleDePrix.datem                            AS GrilleDePrix_34_1,
    GrilleDePrix.libelle                          AS GrilleDePrix_34_3,
    FicheAgreage.code                             AS FicheAgreage_38_0,
    Codelec.iddp_periact                          AS Codelec_40_1,
    Codelec.libelle                               AS Codelec_40_2,
    Codelec.niveau1                               AS Codelec_40_3,
    Codelec.niveau2                               AS Codelec_40_4,
    Codelec.niveau3                               AS Codelec_40_5,
    Codelec.niveau4                               AS Codelec_40_6,
    PerimetreActivite.code                        AS PerimetreActivite_41_0,
    DonneesPersonnalisablesCodelec.gestionreftech AS DonneesPersonnalisab_42_0,
    ClassificationArticleInterne.id               AS ClassificationArticl_43_0,
    ClassificationArticleInterne.niveau1          AS ClassificationArticl_43_2,
    DossierCommercial.id                          AS DossierCommercial_52_0,
    DossierCommercial.codefourndc                 AS DossierCommercial_52_1,
    DossierCommercial.anneedc                     AS DossierCommercial_52_3,
    DossierCommercial.codeclassdc                 AS DossierCommercial_52_4,
    DossierCommercial.numversiondc                AS DossierCommercial_52_5,
    DossierCommercial.indice                      AS DossierCommercial_52_6,
    DossierCommercial.code_ss_classement          AS DossierCommercial_52_7,
    OrigineNegociation.code                       AS OrigineNegociation_53_0,
    MotifBlocageInformation.libellelong           AS MotifBlocageInformat_54_3,
    ArbreLogistique.id                            AS ArbreLogistique_63_1,
    ArbreLogistique.codesap                       AS ArbreLogistique_63_5,
    Fournisseur.code                              AS Fournisseur_66_0,
    Fournisseur.nom                               AS Fournisseur_66_2,
    Filiere.code                                  AS Filiere_67_0,
    Filiere.nom                                   AS Filiere_67_2,
    ValorisationAchat.val_ach_patc                AS Valorisation_74_3,
    LienPrixVente.code                            AS LienPrixVente_76_0,
    LienPrixVente.datem                           AS LienPrixVente_76_1,
    LienGratuite.code                             AS LienGratuite_78_0,
    LienGratuite.datem                            AS LienGratuite_78_1,
    LienCoordonnable.code                         AS LienCoordonnable_79_0,
    LienCoordonnable.datem                        AS LienCoordonnable_79_1,
    LienStatistique.code                          AS LienStatistique_81_0,
    LienStatistique.datem                         AS LienStatistique_81_1
FROM   da_article Article
       join (SELECT idarticle,
                    poids,
                    ROW_NUMBER()
                      over (
                        PARTITION BY RNA.id
                        ORDER BY INNERSEARCH.poids) RN,
                    titre,
                    nom,
                    prenom
             FROM   da_article RNA
                    join (SELECT idarticle,
                        pkg_db_indexation.CALCULPOIDSMOTS(chaine,
                            'foire vins%') AS POIDS,
                        DECODE(index_clerecherche, 'Piste.titre', chaine,
                                                   '')                AS TITRE,
                        DECODE(index_clerecherche, 'Artiste.nom_prenom',
                            SUBSTR(chaine, 0, INSTR(chaine, '_') - 1),
                                                   '')               AS NOM,
                        DECODE(index_clerecherche, 'Artiste.nom_prenom',
                            SUBSTR(chaine, INSTR(chaine, '_') + 1),
                                                   '')               AS PRENOM
                          FROM   ((SELECT index_idenreg AS IDARTICLE,
                                          C.cde_art     AS CHAINE,
                                          index_clerecherche
                                   FROM   cstd_mots M
                                          join cstd_index I
                                            ON I.mots_id = M.mots_id
                                               AND index_clerecherche =
                                                'Article.codeArticle'
                                          join da_article C
                                            ON id = index_idenreg
                                   WHERE  mots_mot = 'foire'
                                   INTERSECT
                                   SELECT index_idenreg AS IDARTICLE,
                                          C.cde_art     AS CHAINE,
                                          index_clerecherche
                                   FROM   cstd_mots M
                                          join cstd_index I
                                            ON I.mots_id = M.mots_id
                                               AND index_clerecherche =
                                                'Article.codeArticle'
                                          join da_article C
                                            ON id = index_idenreg
                                   WHERE  mots_mot LIKE 'vins%'
                                      AND 1 = 1)
                                  UNION ALL
                                  (SELECT index_idenreg AS IDARTICLE,
                                          C.cde_art_bal AS CHAINE,
                                          index_clerecherche
                                   FROM   cstd_mots M
                                          join cstd_index I
                                            ON I.mots_id = M.mots_id
                                               AND index_clerecherche =
                                                'Article.codeArticleBalance'
                                          join da_article C
                                            ON id = index_idenreg
                                   WHERE  mots_mot = 'foire'
                                   INTERSECT
                                   SELECT index_idenreg AS IDARTICLE,
                                          C.cde_art_bal AS CHAINE,
                                          index_clerecherche
                                   FROM   cstd_mots M
                                          join cstd_index I
                                            ON I.mots_id = M.mots_id
                                               AND index_clerecherche =
                                                'Article.codeArticleBalance'
                                          join da_article C
                                            ON id = index_idenreg
                                   WHERE  mots_mot LIKE 'vins%'
                                      AND 1 = 1)
                                  UNION ALL
                                  (SELECT index_idenreg AS IDARTICLE,
                                          C.lib_com     AS CHAINE,
                                          index_clerecherche
                                   FROM   cstd_mots M
                                          join cstd_index I
                                            ON I.mots_id = M.mots_id
                                               AND index_clerecherche =
                                                'Article.libelleCommercial'
                                          join da_article C
                                            ON id = index_idenreg
                                   WHERE  mots_mot = 'foire'
                                   INTERSECT
                                   SELECT index_idenreg AS IDARTICLE,
                                          C.lib_com     AS CHAINE,
                                          index_clerecherche
                                   FROM   cstd_mots M
                                          join cstd_index I
                                            ON I.mots_id = M.mots_id
                                               AND index_clerecherche =
                                                'Article.libelleCommercial'
                                          join da_article C
                                            ON id = index_idenreg
                                   WHERE  mots_mot LIKE 'vins%'
                                      AND 1 = 1)
                                  UNION ALL
                                  (SELECT idda_article AS IDARTICLE,
                                          C.gtin       AS CHAINE,
                                          index_clerecherche
                                   FROM   cstd_mots M
                                          join cstd_index I
                                            ON I.mots_id = M.mots_id
                                               AND index_clerecherche =
                                                 'Gtin.gtin'
                                          join da_gtin C
                                            ON id = index_idenreg
                                   WHERE  mots_mot = 'foire'
                                   INTERSECT
                                   SELECT idda_article AS IDARTICLE,
                                          C.gtin       AS CHAINE,
                                          index_clerecherche
                                   FROM   cstd_mots M
                                          join cstd_index I
                                            ON I.mots_id = M.mots_id
                                               AND index_clerecherche =
                                                'Gtin.gtin'
                                          join da_gtin C
                                            ON id = index_idenreg
                                   WHERE  mots_mot LIKE 'vins%'
                                      AND 1 = 1)
                                  UNION ALL
                                  (SELECT idda_article AS IDARTICLE,
                                          C.ref_frn    AS CHAINE,
                                          index_clerecherche
                                   FROM   cstd_mots M
                                          join cstd_index I
                                            ON I.mots_id = M.mots_id
                                               AND index_clerecherche =
                                        'ArbreLogistique.referenceFournisseur'
                                          join da_arblogi C
                                            ON id = index_idenreg
                                   WHERE  mots_mot = 'foire'
                                   INTERSECT
                                   SELECT idda_article AS IDARTICLE,
                                          C.ref_frn    AS CHAINE,
                                          index_clerecherche
                                   FROM   cstd_mots M
                                          join cstd_index I
                                            ON I.mots_id = M.mots_id
                                               AND index_clerecherche =
                                        'ArbreLogistique.referenceFournisseur'
                                          join da_arblogi C
                                            ON id = index_idenreg
                                   WHERE  mots_mot LIKE 'vins%'
                                      AND 1 = 1))) INNERSEARCH
                      ON INNERSEARCH.idarticle = RNA.id) SEARCHMC
         ON SEARCHMC.idarticle = Article.id
            AND 1 = 1
       left join da_artcmpl ArticleComplement
              ON Article.id = ArticleComplement.idda_article
       left join dp_gpdtcul GenreProduitCulturel
              ON ArticleComplement.iddp_gpdtcul = GenreProduitCulturel.id
       left join df_collect Collection
              ON ArticleComplement.iddf_collect = Collection.id
       left join da_gtin Gtin
              ON Article.id = Gtin.idda_article
                 AND Gtin.principal = 1
                 AND Gtin.db_suplog = 0
       left join df_fabric Fabricant
              ON Article.iddf_fabric = Fabricant.id
       left join dp_clsvtel ClassificationVenteLocale
              ON Article.iddp_clsvtel = ClassificationVenteLocale.id
       left join dp_marqcom MarqueCommerciale
              ON Article.iddp_marqcom = MarqueCommerciale.id
       left join da_compo Composition
              ON Composition.id = Article.idda_compo
       left join dp_comptex CompositionTextile
              ON CompositionTextile.id = Article.iddp_comptex
       left join da_arttrai AssoArticleInterfaceBalance
              ON AssoArticleInterfaceBalance.idda_article = Article.id
                 AND AssoArticleInterfaceBalance.iddp_tinterf = 1
       left join da_arttrai AssoArticleInterfaceCaisse
              ON AssoArticleInterfaceCaisse.idda_article = Article.id
                 AND AssoArticleInterfaceCaisse.iddp_tinterf = 4
       left join dp_raybala RayonBalance
              ON Article.iddp_raybala = RayonBalance.id
       left join dp_valdico TypeTare
              ON TypeTare.id = RayonBalance.iddp_typtare
       left join df_categor Categorie
              ON Categorie.id = Article.iddf_categor
       left join df_grille GrilleDePrix
              ON GrilleDePrix.id = Categorie.iddf_grille
       left join dp_agreage FicheAgreage
              ON FicheAgreage.id = Article.iddp_agreage
       join dp_codelec Codelec
         ON Article.iddp_codelec = Codelec.id
       left join dp_periact PerimetreActivite
              ON PerimetreActivite.id = Codelec.iddp_periact
       left join dp_perscod DonneesPersonnalisablesCodelec
              ON Codelec.id = DonneesPersonnalisablesCodelec.iddp_codelec
                 AND DonneesPersonnalisablesCodelec.db_suplog = 0
                 AND DonneesPersonnalisablesCodelec.iddb_sitecl = 1012124
       left join dp_clsart ClassificationArticleInterne
              ON DonneesPersonnalisablesCodelec.iddp_clsart =
                ClassificationArticleInterne.id
       left join da_artdeno ArticleDenormalise
              ON Article.id = ArticleDenormalise.idda_article
       left join df_clasmnt ClassementFournisseur
              ON ArticleDenormalise.iddf_clasmnt = ClassementFournisseur.id
       left join tr_dosclas DossierDeClassement
              ON ClassementFournisseur.id = DossierDeClassement.iddf_clasmnt
                 AND DossierDeClassement.date_deb <= '2013-09-27'
                 AND COALESCE(DossierDeClassement.date_fin,
                    TO_DATE('31129999', 'DDMMYYYY')) >= '2013-09-27'
       left join tr_doscomm DossierCommercial
              ON DossierDeClassement.idtr_doscomm = DossierCommercial.id
       left join dp_valdico OrigineNegociation
              ON DossierCommercial.iddp_dossref = OrigineNegociation.id
       left join dp_motbloc MotifBlocageInformation
              ON MotifBlocageInformation.id = ArticleDenormalise.idda_motinf
       left join da_arblogi ArbreLogistique
              ON Article.id = ArbreLogistique.idda_article
                 AND ArbreLogistique.princ = 1
                 AND ArbreLogistique.db_suplog = 0
       left join df_filiere Filiere
              ON ArbreLogistique.iddf_filiere = Filiere.id
       left join df_fourn Fournisseur
              ON Filiere.iddf_fourn = Fournisseur.id
       left join od_dosal dossierALValo
              ON dossierALValo.idda_arblogi = ArbreLogistique.id
                 AND dossierALValo.idod_dossier IS NULL
       left join tt_val_dal valoDossier
              ON valoDossier.idod_dosal = dossierALValo.id
                 AND valoDossier.estarecalculer = 0
       left join tt_valo ValorisationAchat
              ON ValorisationAchat.idtt_val_dal = valoDossier.id
                 AND ValorisationAchat.date_modif_retro IS NULL
                 AND ValorisationAchat.date_debut_achat <= '2013-09-27'
                 AND COALESCE(ValorisationAchat.date_fin_achat,
                    TO_DATE('31129999', 'DDMMYYYY')) >= '2013-09-27'
                 AND ValorisationAchat.val_ach_pab IS NOT NULL
       left join da_lienart assoALPXVT
              ON assoALPXVT.idda_article = Article.id
                 AND assoALPXVT.iddp_typlien = 14893
       left join da_lien LienPrixVente
              ON LienPrixVente.id = assoALPXVT.idda_lien
       left join da_lienart assoALGRAT
              ON assoALGRAT.idda_article = Article.id
                 AND assoALGRAT.iddp_typlien = 14894
       left join da_lien LienGratuite
              ON LienGratuite.id = assoALGRAT.idda_lien
       left join da_lienart assoALCOOR
              ON assoALCOOR.idda_article = Article.id
                 AND assoALCOOR.iddp_typlien = 14899
       left join da_lien LienCoordonnable
              ON LienCoordonnable.id = assoALCOOR.idda_lien
       left join da_lienal assoALSTAT
              ON assoALSTAT.idda_arblogi = ArbreLogistique.id
                 AND assoALSTAT.iddp_typlien = 14897
       left join da_lien LienStatistique
              ON LienStatistique.id = assoALSTAT.idda_lien WHERE
SEARCHMC.rn = 1
   AND ( ValorisationAchat.id IS NULL
          OR ValorisationAchat.date_debut_achat = (
                 SELECT MAX(VALMAX.date_debut_achat)
                 FROM   tt_valo VALMAX
                 WHERE  VALMAX.idtt_val_dal = ValorisationAchat.idtt_val_dal
                    AND VALMAX.date_modif_retro IS NULL
                    AND VALMAX.val_ach_pab IS NOT NULL
                    AND VALMAX.date_debut_achat <= '2013-09-27') )
   AND ( Article.id IN (SELECT A.id
                        FROM   da_article A
                               join du_ucutiar AssoUcUtiAr
                                 ON AssoUcUtiAr.idda_article = A.id
                               join du_asucuti AssoUcUti
                                 ON AssoUcUti.id = AssoUcUtiAr.iddu_asucuti
                        WHERE  ( AssoUcUti.iddu_uti IN ( 90000000000022 ) )
                           AND a.iddb_sitecl = 1012124) )
   AND Article.db_suplog = 0
ORDER  BY SEARCHMC.poids ASC

CREATE INDEX idx_appellation_libelle ON appellation
USING btree (libelle varchar_pattern_ops);

EXPLAIN SELECT * FROM appellation WHERE libelle LIKE '%wur%';

                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on appellation  (cost=0.00..6.99 rows=3 width=24)
   Filter: (libelle ~~ '%wur%'::text)

CREATE EXTENSION pg_trgm;

CREATE INDEX idx_appellation_libelle_trgm ON appellation
USING gist (libelle gist_trgm_ops);

EXPLAIN SELECT * FROM appellation WHERE libelle LIKE '%wur%';

                               QUERY PLAN

-----------------------------------------------------------------------------
 Bitmap Heap Scan on appellation  (cost=4.27..7.41 rows=3 width=24)
   Recheck Cond: (libelle ~~ '%wur%'::text)
   ->  Bitmap Index Scan on idx_appellation_libelle_trgm  (cost=0.00..4.27...)
         Index Cond: (libelle ~~ '%wur%'::text)

createdb voitures
curl -kL https://dali.bo/tp_voitures -o /tmp/voitures.dmp
pg_restore -d voitures /tmp/voitures.dmp
# un message sur le schéma public préexistant est normal

curl -kL https://dali.bo/tp_posts -o /tmp/posts.dump
createdb posts
pg_restore -d posts /tmp/posts.dump
# le message sur le schéma public préexistant est normal
rm -- /tmp/posts.dump

# \d posts
                              Table « public.posts »
  Colonne   |           Type           | Collationnement | NULL-able | Par défaut
------------+--------------------------+-----------------+-----------+------------
 id_article | integer                  |                 |           |
 id_post    | integer                  |                 |           |
 ts         | timestamp with time zone |                 |           |
 message    | text                     |                 |           |
Index :
    "posts_ts_idx" btree (ts)

SET jit to off ;
SET max_parallel_workers_per_gather TO 0 ;

SELECT * FROM posts
WHERE id_article =
    (SELECT max(id_article) * 0.333
     FROM posts
     ) ;

  ALTER TABLE voitures rename TO voitures_orig;

  CREATE TABLE voitures AS
    SELECT DISTINCT ON (immatriculation) immatriculation, modele
    FROM voitures_orig ;

  ALTER TABLE voitures ADD PRIMARY KEY (immatriculation);

  CREATE TABLE caracteristiques
    AS SELECT *
       FROM (
               SELECT DISTINCT
                regexp_split_to_table(caracteristiques,',') caracteristique
               FROM voitures_orig)
            AS tmp
       WHERE caracteristique <> '' ;

  ALTER TABLE caracteristiques ADD PRIMARY KEY (caracteristique);

  CREATE TABLE caracteristiques_voitures
    AS SELECT DISTINCT *
    FROM (
         SELECT
           immatriculation,
           regexp_split_to_table(caracteristiques,',') caracteristique
         FROM voitures_orig
        )
      AS tmp
     WHERE caracteristique <> '';

  VACUUM ANALYZE ;

 \d+
                                      Liste des relations
 Schéma |            Nom            | Type  | Propriétaire | ... | Taille  | ...
--------+---------------------------+-------+--------------+-----+---------+-
 public | caracteristiques          | table | postgres     |     | 48 kB   |
 public | caracteristiques_voitures | table | postgres     |     | 3208 kB |
 public | voitures                  | table | postgres     |     | 4952 kB |
 public | voitures_ecv              | table | postgres     |     | 3336 kB |
 public | voitures_orig             | table | postgres     |     | 5736 kB |

  ALTER TABLE caracteristiques_voitures
  ADD PRIMARY KEY (immatriculation,caracteristique);

  ALTER TABLE caracteristiques_voitures
  ADD FOREIGN KEY (immatriculation)
  REFERENCES voitures(immatriculation);

  ALTER TABLE caracteristiques_voitures
  ADD FOREIGN KEY (caracteristique)
  REFERENCES caracteristiques(caracteristique);

BEGIN ;
INSERT INTO voitures VALUES ('AA-007-JB','clio') ;
INSERT INTO caracteristiques_voitures (immatriculation, caracteristique)
VALUES ('AA-007-JB','ABS') ;
INSERT INTO caracteristiques_voitures (immatriculation, caracteristique)
VALUES ('AA-007-JB','phares LED') ;
COMMIT ;

ERROR:  insert or update on table "caracteristiques_voitures" violates foreign key
        constraint "caracteristiques_voitures_caracteristique_fkey"
DÉTAIL : Key (caracteristique)=(ABS) is not present in table "caracteristiques".

ERROR:  insert or update on table "caracteristiques_voitures" violates foreign key
        constraint "caracteristiques_voitures_immatriculation_fkey"
DÉTAIL : Key (immatriculation)=(AA-007-JB) is not present in table "voitures".

BEGIN ;
INSERT INTO voitures VALUES ('AA-007-JB','clio') ;
INSERT INTO caracteristiques VALUES ('phares LED') ;
INSERT INTO caracteristiques_voitures (immatriculation, caracteristique)
VALUES ('AA-007-JB','abs') ;
INSERT INTO caracteristiques_voitures (immatriculation, caracteristique)
VALUES ('AA-007-JB','phares LED') ;
COMMIT ;

SELECT * FROM voitures_orig
WHERE caracteristiques like '%toit ouvrant%';

  EXPLAIN ANALYZE
  SELECT * FROM voitures_orig
  WHERE caracteristiques ~ E'[[:<:]]toit ouvrant[[:>:]]' ;

                              QUERY PLAN
--------------------------------------------------------------------------
 Seq Scan on voitures_orig  (cost=0.00..1962.00 rows=8419 width=25)
                            (actual time=0.030..92.226 rows=8358 loops=1)
   Filter: (caracteristiques ~ '[[:<:]]toit ouvrant[[:>:]]'::text)
   Rows Removed by Filter: 91642
 Planning Time: 0.658 ms
 Execution Time: 92.512 ms

SELECT *
FROM  voitures
INNER JOIN  caracteristiques_voitures
       ON ( caracteristiques_voitures.immatriculation=voitures.immatriculation)
WHERE caracteristique = 'toit ouvrant' ;

SELECT *
FROM voitures
WHERE EXISTS (
    SELECT 1 FROM caracteristiques_voitures
    WHERE caracteristiques_voitures.immatriculation=voitures.immatriculation
    AND caracteristique = 'toit ouvrant'
) ;

                            QUERY PLAN
----------------------------------------------------------------------
 Hash Join  (cost=1225.80..3102.17 rows=8329 width=16)
            (actual time=6.307..31.811 rows=8358 loops=1)
   Hash Cond: (voitures.immatriculation = caracteristiques_voitures.immatriculation)
   ->  Seq Scan on voitures  (cost=0.00..1613.89 rows=99989 width=16)
                             (actual time=0.019..10.432 rows=99989 loops=1)
   ->  Hash  (cost=1121.69..1121.69 rows=8329 width=10)
             (actual time=6.278..6.279 rows=8358 loops=1)
         Buckets: 16384  Batches: 1  Memory Usage: 577kB
         ->  Seq Scan on caracteristiques_voitures
                                   (cost=0.00..1121.69 rows=8329 width=10)
                                   (actual time=0.004..5.077 rows=8358 loops=1)
               Filter: (caracteristique = 'toit ouvrant'::text)
               Rows Removed by Filter: 49697
 Planning Time: 0.351 ms
 Execution Time: 32.155 ms

EXPLAIN ANALYZE
SELECT *
FROM  voitures
INNER JOIN  caracteristiques_voitures
       ON ( caracteristiques_voitures.immatriculation=voitures.immatriculation)
WHERE caracteristique = 'ordinateur de bord' ;

                              QUERY PLAN
---------------------------------------------------------------------
 Nested Loop  (cost=0.42..1130.12 rows=1 width=16)
              (actual time=4.849..4.850 rows=0 loops=1)
   ->  Seq Scan on caracteristiques_voitures  (cost=0.00..1121.69 rows=1 width=10)
                                        (actual time=4.848..4.848 rows=0 loops=1)
         Filter: (caracteristique = 'ordinateur de bord'::text)
         Rows Removed by Filter: 58055
   ->  Index Scan using voitures_pkey on voitures  (cost=0.42..8.44 rows=1 width=16)
                                                   (never executed)
         Index Cond: (immatriculation = caracteristiques_voitures.immatriculation)
 Planning Time: 0.337 ms
 Execution Time: 4.872 ms

CREATE INDEX ON caracteristiques_voitures (caracteristique) ;

EXPLAIN ANALYZE
SELECT *
FROM  voitures
INNER JOIN  caracteristiques_voitures
       ON ( caracteristiques_voitures.immatriculation=voitures.immatriculation)
WHERE caracteristique = 'ordinateur de bord' ;

                              QUERY PLAN
---------------------------------------------------------------------
 Nested Loop  (cost=0.83..16.78 rows=1 width=16)
              (actual time=0.010..0.011 rows=0 loops=1)
   ->  Index Scan using caracteristiques_voitures_caracteristique_idx
                     on caracteristiques_voitures
              (cost=0.41..8.35 rows=1 width=10)
              (actual time=0.010..0.010 rows=0 loops=1)
         Index Cond: (caracteristique = 'ordinateur de bord'::text)
   ->  Index Scan using voitures_pkey on voitures  (cost=0.42..8.44 rows=1 width=16)
                                                   (never executed)
         Index Cond: (immatriculation = caracteristiques_voitures.immatriculation)
 Planning Time: 0.268 ms
 Execution Time: 0.035 ms

  EXPLAIN (ANALYZE, COSTS OFF)
  SELECT *
  FROM voitures
  JOIN caracteristiques_voitures AS cr1 USING (immatriculation)
  JOIN caracteristiques_voitures AS cr2 USING (immatriculation)
  JOIN caracteristiques_voitures AS cr3 USING (immatriculation)
  WHERE cr1.caracteristique = 'toit ouvrant'
  AND cr2.caracteristique = 'abs'
  AND cr3.caracteristique='4 roues motrices' ;

                                 QUERY PLAN
--------------------------------------------------------------------------------
Nested Loop
->  Hash Join
        Hash Cond: (cr2.immatriculation = cr1.immatriculation)
        ->  Bitmap Heap Scan on caracteristiques_voitures cr2
            Recheck Cond: (caracteristique = 'abs'::text)
            ->  Bitmap Index Scan on caracteristiques_voitures_caracteristique_idx
                    Index Cond: (caracteristique = 'abs'::text)
        ->  Hash
            ->  Hash Join
                    Hash Cond: (cr1.immatriculation = cr3.immatriculation)
                    ->  Bitmap Heap Scan on caracteristiques_voitures cr1
                        Recheck Cond: (caracteristique = 'toit ouvrant'::text)
                        ->  Bitmap Index Scan
                            on caracteristiques_voitures_caracteristique_idx
                                Index Cond: (caracteristique = 'toit ouvrant'::text)
                    ->  Hash
                        ->  Bitmap Heap Scan on caracteristiques_voitures cr3
                                Recheck Cond: (caracteristique =
                                                           '4 roues motrices'::text)
                                ->  Bitmap Index Scan
                                    on caracteristiques_voitures_caracteristique_idx
                                    Index Cond: (caracteristique =
                                                           '4 roues motrices'::text)
->  Index Scan using voitures_pkey on voitures
        Index Cond: (immatriculation = cr1.immatriculation)

SELECT * FROM voitures_ecv
WHERE entite = 'ZY-745-KT' ;

  entite   |          cle          | valeur
-----------+-----------------------+--------
 ZY-745-KT | climatisation         | t
 ZY-745-KT | jantes aluminium      | t
 ZY-745-KT | regulateur de vitesse | t
 ZY-745-KT | toit ouvrant          | t

  EXPLAIN ANALYZE
  SELECT * FROM voitures_ecv
  WHERE EXISTS (
    SELECT 1 FROM voitures_ecv test
    WHERE test.entite=voitures_ecv.entite
    AND cle = 'toit ouvrant' AND valeur = true
  )
  ORDER BY entite ;

                              QUERY PLAN
---------------------------------------------------------------------
 Sort  (cost=3468.93..3507.74 rows=15527 width=25)
       (actual time=29.854..30.692 rows=17782 loops=1)
   Sort Key: voitures_ecv.entite
   Sort Method: quicksort  Memory: 2109kB
   ->  Hash Join  (cost=1243.09..2388.05 rows=15527 width=25)
                  (actual time=6.915..23.964 rows=17782 loops=1)
         Hash Cond: (voitures_ecv.entite = test.entite)
         ->  Seq Scan on voitures_ecv  (cost=0.00..992.55 rows=58055 width=25)
                                (actual time=0.006..4.242 rows=58055 loops=1)
         ->  Hash  (cost=1137.69..1137.69 rows=8432 width=10)
                   (actual time=6.899..6.899 rows=8358 loops=1)
               Buckets: 16384  Batches: 1  Memory Usage: 471kB
               ->  Seq Scan on voitures_ecv test
                                       (cost=0.00..1137.69 rows=8432 width=10)
                                 (actual time=0.005..5.615 rows=8358 loops=1)
                     Filter: (valeur AND (cle = 'toit ouvrant'::text))
                     Rows Removed by Filter: 49697
 Planning Time: 0.239 ms
 Execution Time: 31.321 ms

CREATE EXTENSION hstore;

CREATE TABLE voitures_hstore
AS
    SELECT entite AS immatriculation,
           hstore(array_agg(cle),array_agg(valeur)::text[]) AS caracteristiques
    FROM voitures_ecv group by entite;

ALTER TABLE voitures_hstore ADD PRIMARY KEY (immatriculation);

SELECT * FROM voitures_hstore
WHERE immatriculation = 'ZY-745-KT' \gx

-[ RECORD 1 ]----+--------------------------------------------------------------
immatriculation  | ZY-745-KT
caracteristiques | "toit ouvrant"=>"true", "climatisation"=>"true",
                 | "jantes aluminium"=>"true", "regulateur de vitesse"=>"true"

SELECT immatriculation, caracteristiques -> 'climatisation'
FROM voitures_hstore
WHERE immatriculation = 'ZY-745-KT' ;

INSERT INTO voitures_hstore
VALUES ('XX-4456-ZT', 'couleur=>vert, phares=>LED'::hstore) ;

CREATE INDEX voitures_hstore_caracteristiques
ON voitures_hstore
USING gist (caracteristiques);

SELECT *
FROM voitures_hstore
WHERE caracteristiques @> 'couleur=>vert' AND caracteristiques @> 'phares=>LED' ;

                               QUERY PLAN
---------------------------------------------------------------------
 Index Scan using voitures_hstore_caracteristiques on voitures_hstore
   (cost=0.28..2.30 rows=1 width=55) (actual time=0.033..0.033 rows=1 loops=1)
   Index Cond: ((caracteristiques @> '"couleur"=>"vert"'::hstore)
            AND (caracteristiques @> '"phares"=>"LED"'::hstore))
   Buffers: shared hit=4
 Planning Time: 0.055 ms
 Execution Time: 0.047 ms

SELECT immatriculation, modele,
       regexp_split_to_array(caracteristiques,',')
FROM voitures_orig
LIMIT 10;

 immatriculation | modele |          regexp_split_to_array
-----------------+--------+-----------------------------------------
 WW-649-AI       | twingo | {"regulateur de vitesse"}
 QZ-533-JD       | clio   | {"4 roues motrices","jantes aluminium"}
 YY-854-LE       | megane | {climatisation}
 QD-761-QV       | twingo | {""}
 LV-277-QC       | megane | {abs,"jantes aluminium"}
 ZI-003-BQ       | kangoo | {"boite automatique",climatisation}
 WT-817-IK       | megane | {""}
 JK-791-XB       | megane | {""}
 WW-019-EK       | megane | {""}
 BZ-544-OS       | twingo | {""}

CREATE INDEX idx_voitures_array ON voitures_orig
    USING gin (regexp_split_to_array(caracteristiques,','));

EXPLAIN ANALYZE
SELECT * FROM voitures_orig
WHERE regexp_split_to_array(caracteristiques,',') @> '{"toit ouvrant"}';

                               QUERY PLAN
--------------------------------------------------------------------------------
 Bitmap Heap Scan on voitures_orig  (cost=8.87..387.37 rows=500 width=25)
                                    (actual time=0.707..2.756 rows=8358 loops=1)
   Recheck Cond: (regexp_split_to_array(caracteristiques, ','::text)
                  @> '{"toit ouvrant"}'::text[])
   Heap Blocks: exact=712
   ->  Bitmap Index Scan on idx_voitures_array  (cost=0.00..8.75 rows=500 width=0)
                                      (actual time=0.631..0.631 rows=8358 loops=1)
         Index Cond: (regexp_split_to_array(caracteristiques, ','::text)
                                                    @> '{"toit ouvrant"}'::text[])
 Planning Time: 0.129 ms
 Execution Time: 3.028 ms

EXPLAIN ANALYZE
SELECT  *
FROM    posts
WHERE   id_article =12
ORDER BY id_post
LIMIT   10 ;

                                  QUERY PLAN
------------------------------------------------------------------------------
 Limit  (cost=153694.51..153694.53 rows=10 width=115)
        (actual time=500.525..500.528 rows=10 loops=1)
   ->  Sort  (cost=153694.51..153696.95 rows=979 width=115)
             (actual time=500.524..500.525 rows=10 loops=1)
         Sort Key: id_post
         Sort Method: top-N heapsort  Memory: 27kB
         ->  Seq Scan on posts  (cost=0.00..153673.35 rows=979 width=115)
                                (actual time=1.300..500.442 rows=1000 loops=1)
               Filter: (id_article = 12)
               Rows Removed by Filter: 4999000
 Planning Time: 0.089 ms
 Execution Time: 500.549 ms

CREATE INDEX posts_id_article_id_post ON posts (id_article, id_post);

EXPLAIN ANALYZE
SELECT  *
FROM    posts
WHERE   id_article =12
ORDER BY id_post
LIMIT   10 ;

                      QUERY PLAN
---------------------------------------------------------
 Limit  (cost=0.43..18.26 rows=10 width=115)
        (actual time=0.043..0.053 rows=10 loops=1)
   ->  Index Scan using posts_id_article_id_post on posts
                  (cost=0.43..1745.88 rows=979 width=115)
               (actual time=0.042..0.051 rows=10 loops=1)
         Index Cond: (id_article = 12)
 Planning Time: 0.204 ms
 Execution Time: 0.066 ms

EXPLAIN ANALYZE
SELECT  *
FROM    posts
WHERE   id_article = 12
ORDER BY id_post
LIMIT   10
OFFSET  10;

                          QUERY PLAN
---------------------------------------------------------
 Limit  (cost=18.26..36.09 rows=10 width=115)
        (actual time=0.020..0.023 rows=10 loops=1)
   ->  Index Scan using posts_id_article_id_post on posts
                  (cost=0.43..1745.88 rows=979 width=115)
                  (actual time=0.017..0.021 rows=20 loops=1)
         Index Cond: (id_article = 12)
 Planning Time: 0.061 ms
 Execution Time: 0.036 ms

EXPLAIN ANALYZE
SELECT  *
FROM    posts
WHERE   id_article = 12
ORDER BY id_post
LIMIT   10
OFFSET  900 ;

                          QUERY PLAN
---------------------------------------------------------
 Limit  (cost=1605.04..1622.86 rows=10 width=115)
        (actual time=0.216..0.221 rows=10 loops=1)
   ->  Index Scan using posts_id_article_id_post on posts
                   (cost=0.43..1745.88 rows=979 width=115)
                   (actual time=0.018..0.194 rows=910 loops=1)
         Index Cond: (id_article = 12)
 Planning Time: 0.062 ms
 Execution Time: 0.243 ms

SELECT  id_article, id_post
FROM    posts
WHERE   id_article = 12
ORDER BY id_post
LIMIT   1
OFFSET  899 ;

 id_article | id_post
------------+---------
         12 | 12900

EXPLAIN ANALYZE
SELECT  *
FROM    posts
WHERE   id_article = 12
AND     id_post> 12900
ORDER BY id_post
LIMIT   10;

                            QUERY PLAN
----------------------------------------------------------------
 Limit  (cost=0.43..18.29 rows=10 width=115)
        (actual time=0.018..0.024 rows=10 loops=1)
   ->  Index Scan using posts_id_article_id_post on posts
                         (cost=0.43..1743.02 rows=976 width=115)
                         (actual time=0.016..0.020 rows=10 loops=1)
         Index Cond: ((id_article = 12) AND (id_post > 12900))
 Planning Time: 0.111 ms
 Execution Time: 0.039 ms

EXPLAIN ANALYZE
SELECT  *
FROM    posts
WHERE   to_char(ts,'YYYYMM')='201302' ;

                                QUERY PLAN
---------------------------------------------------------------------
 Seq Scan on posts  (cost=0.00..187728.49 rows=50000 width=269)
                    (actual time=0.380..14163.371 rows=18234 loops=1)
   Filter: (to_char(ts, 'YYYYMM'::text) = '201302'::text)
   Rows Removed by Filter: 9981766
 Total runtime: 14166.265 ms

EXPLAIN ANALYZE
SELECT  *
FROM    posts
WHERE   ts >= '2013-02-01'
AND     ts <  '2013-03-01' ;

                               QUERY PLAN
-------------------------------------------------------------------------------
 Index Scan using posts_ts_idx on posts  (cost=0.43..998.95 rows=20165 width=115)
                                         (actual time=0.050..5.907 rows=20160 loops=1)
   Index Cond: ((ts >= '2013-02-01 00:00:00+01'::timestamp with time zone)
             AND (ts < '2013-03-01 00:00:00+01'::timestamp with time zone))
 Planning Time: 0.095 ms
 Execution Time: 6.526 ms

SELECT generate_series(
    '2013-01-06 00:00:00',
    '2013-12-31 00:00:00',
    INTERVAL '7 days'
) ;

WITH premiersjours AS (
  SELECT '2000-01-01'::timestamp + i * interval '1 year' AS jan1
  FROM   generate_series(1, 30) i
),
dimanches AS (
 SELECT jan1,
   jan1
    + mod(13-extract(dow FROM (jan1 - interval '1 day'))::int, 7)
      + interval '1 day'
   AS dim1
 FROM   premiersjours
)
SELECT jan1, dim1
FROM dimanches ;

SELECT i AS debut,
       i + INTERVAL '1 day' AS fin
FROM generate_series(
    '2013-01-06 00:00:00',
    '2013-12-31 00:00:00',
    INTERVAL '7 days'
) g(i) ;

         debut          |          fin
------------------------+------------------------
 2013-01-06 00:00:00+01 | 2013-01-07 00:00:00+01
 2013-01-13 00:00:00+01 | 2013-01-14 00:00:00+01
...
 2013-12-29 00:00:00+01 | 2013-12-30 00:00:00+01

EXPLAIN ANALYZE
WITH dimanches AS (
    SELECT i AS debut,
        i + INTERVAL '1 day' AS fin
    FROM generate_series(
        '2013-01-06 00:00:00',
        '2013-12-31 00:00:00',
        INTERVAL '7 days'
    ) g(i)
)
SELECT posts.*
FROM posts
JOIN dimanches
ON (posts.ts >= dimanches.debut AND posts.ts < dimanches.fin) ;

                                QUERY PLAN
------------------------------------------------------------------------------
 Nested Loop  (cost=0.44..17086517.00 rows=555555556 width=115)
              (actual time=0.038..12.978 rows=37440 loops=1)
   ->  Function Scan on generate_series g  (cost=0.00..10.00 rows=1000 width=8)
                                           (actual time=0.016..0.031 rows=52 loops=1)
   ->  Index Scan using posts_ts_idx on posts
                                    (cost=0.43..11530.95 rows=555556 width=115)
                                    (actual time=0.009..0.168 rows=720 loops=52)
         Index Cond: ((ts >= g.i) AND (ts < (g.i + '1 day'::interval)))
 Planning Time: 0.131 ms
 Execution Time: 14.178 ms

SELECT * FROM posts
WHERE id_article =
    (SELECT max(id_article) * 0.333
     FROM posts
     ) ;

                               QUERY PLAN
--------------------------------------------------------------------------------
 Seq Scan on posts  (cost=0.48..166135.48 rows=25000 width=115)
                    (actual time=333.363..1000.696 rows=1000 loops=1)
   Filter: ((id_article)::numeric = $1)
   Rows Removed by Filter: 4999000
   InitPlan 2 (returns $1)
     ->  Result  (cost=0.46..0.48 rows=1 width=32)
                 (actual time=0.016..0.017 rows=1 loops=1)
           InitPlan 1 (returns $0)
             ->  Limit  (cost=0.43..0.46 rows=1 width=4)
                        (actual time=0.012..0.014 rows=1 loops=1)
                   ->  Index Only Scan Backward using posts_id_article_id_post
                                       on posts posts_1
                                       (cost=0.43..142352.43 rows=5000000 width=4)
                                       (actual time=0.012..0.012 rows=1 loops=1)
                         Index Cond: (id_article IS NOT NULL)
                         Heap Fetches: 0
 Planning Time: 0.097 ms
 Execution Time: 1000.753 ms

Filter: ((id_article)::numeric = $1)

SELECT max(id_article) * 0.333
     FROM posts
\gdesc

  Column  |  Type
----------+---------
 ?column? | numeric

SELECT * FROM posts
WHERE id_article =
    (SELECT max(id_article) * 0.333
     FROM posts
     )::int ;

                               QUERY PLAN
-------------------------------------------------------------------------------
  Index Scan using posts_id_article_id_post on posts
                            (cost=0.91..1796.42 rows=1007 width=115)
                            (actual time=0.031..0.198 rows=1000 loops=1)
   Index Cond: (id_article = ($1)::integer)
   InitPlan 2 (returns $1)
     ->  Result  (cost=0.46..0.48 rows=1 width=32) (...)
           InitPlan 1 (returns $0)
             ->  Limit  (cost=0.43..0.46 rows=1 width=4) (...)
                   ->  Index Only Scan Backward using posts_id_article_id_post
                                       on posts posts_1  (...)
                         Index Cond: (id_article IS NOT NULL)
                         Heap Fetches: 0
 Planning Time: 0.105 ms
 Execution Time: 0.245 ms