Re: Sequence Scan vs. Index scan

["Alejandro D. Burne" <alejandro.dburne@xxxxxxxxx>]

2006/3/21, Reimer <carlosreimer@xxxxxxxxxxxx>:

Fernando,

If you need to read all the table for example it would be better to read
only the data pages instead of read data and index pages.

Reimer

----- Original Message -----
From: "Fernando Lujan" < fernando.lujan@xxxxxxxxxxxxx>
To: <pgsql-performance@xxxxxxxxxxxxxx>
Sent: Tuesday, March 21, 2006 3:08 PM
Subject: [PERFORM] Sequence Scan vs. Index scan


> Hi guys,
>
> I'm trying to figure out when Sequence Scan is better than Index Scan. I
> just want to know this because I disabled the sequence scan in postgresql
> and receive a better result. :)
>
> Two tables.
>
> Table 1 (1 million rows )
> -----------
> id
> text
> table2_id
>
> Table 2 (300 thousand rows)
> ----------
> id
> text 2
>
> When I join these two tables I have a sequence_scan. :(
>
> Thanks in advance.
>
> Fernando Lujan
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
       message can get through to the mailing list cleanly

Hi, I've got the same situation:

ENABLE_SEQSCAN ON  -> 5,031 ms

ENABLE_SEQSCAN OFF  -> 406 ms


Tables definition:

-----------------------


CREATE TABLE liquidacionesos

(

  codigoliquidacionos serial NOT NULL,

  codigoobrasocial int4 NOT NULL,

  quincena char(1) NOT NULL,

  per_m char(2) NOT NULL,

  per_a char(4) NOT NULL,

  nombreliquidacion varchar(60) NOT NULL,

  codigotipoliquidacionos int2 NOT NULL,

  importe numeric(12,2) NOT NULL,

  conformado bool NOT NULL,

  facturada bool NOT NULL,

  codigoremito int4 NOT NULL DEFAULT 0,

  codigoprofesion int2 NOT NULL DEFAULT 0,

  matriculaprofesional int4 NOT NULL DEFAULT 0,

  letrafactura char(1) NOT NULL DEFAULT ' '::bpchar,

  numerofactura varchar(13) NOT NULL DEFAULT '0000-00000000'::character varying,

  importegravado numeric(12,2) NOT NULL DEFAULT 0,

  importenogravado numeric(12,2) NOT NULL DEFAULT 0,

  importeiva numeric(12,2) NOT NULL DEFAULT 0,

  importefactura numeric(12,2) NOT NULL DEFAULT 0,

  fechahora_cga timestamp NOT NULL DEFAULT now(),

  userid varchar(20) NOT NULL DEFAULT "current_user"(),

  numerosecuencia int4 NOT NULL DEFAULT 0,

  CONSTRAINT liqos_pkey PRIMARY KEY (codigoliquidacionos)

) 

WITHOUT OIDS TABLESPACE data;

ALTER TABLE liquidacionesos ALTER COLUMN codigoliquidacionos SET STATISTICS 100;

ALTER TABLE liquidacionesos ALTER COLUMN per_a SET STATISTICS 100;

ALTER TABLE liquidacionesos ALTER COLUMN per_m SET STATISTICS 100;

ALTER TABLE liquidacionesos ALTER COLUMN quincena SET STATISTICS 100;

ALTER TABLE liquidacionesos ALTER COLUMN codigoobrasocial SET STATISTICS 100;

CREATE INDEX ixliqos_periodo

  ON liquidacionesos

  USING btree

  (per_a, per_m, quincena);



CREATE TABLE detalleprestaciones

(

  codigoliquidacionos int4 NOT NULL,

  numerosecuencia int4 NOT NULL,

  codigoprofesionclisanhosp int2 NOT NULL,

  matriculaprofesionalclisanhosp int4 NOT NULL,

  codigoctmclisanhosp int4 NOT NULL,

  codigoprofesionefector int2 NOT NULL,

  matriculaprofesionalefector int4 NOT NULL,

  codigoctmefector int4 NOT NULL,

  fechaprestacion date NOT NULL,

  codigonn char(6) NOT NULL,

  cantidad int2 NOT NULL,

  codigofacturacion int2 NOT NULL,

  porcentajehonorarios numeric(6,2) NOT NULL,

  porcentajederechos numeric(6,2) NOT NULL,

  importehonorarios numeric(12,2) NOT NULL,

  importederechos numeric(12,2) NOT NULL,

  importegastos numeric(12,2) NOT NULL,

  importegastosnogravados numeric(12,2) NOT NULL,

  importecompensacion numeric(12,2) NOT NULL,

  codigopadron int2 NOT NULL,

  codigoafiliado char(15) NOT NULL,

  numerobono varchar(15) NOT NULL,

  matriculaprofesionalprescriptor int4 NOT NULL,

  codigodevolucion int2 NOT NULL,

  importeforzado bool NOT NULL,

  codigotramo int2 NOT NULL DEFAULT 0,

  campocomodin int2 NOT NULL,

  fechahora_cga timestamp NOT NULL DEFAULT now(),

  userid varchar(20) NOT NULL DEFAULT "current_user"(),

  CONSTRAINT dp_pkey PRIMARY KEY (codigoliquidacionos, numerosecuencia)

) 

WITHOUT OIDS TABLESPACE data;

ALTER TABLE detalleprestaciones ALTER COLUMN codigoliquidacionos SET STATISTICS 100;


both vacummed and analyzed

table detalleprestaciones 5,408,590 rec

table liquidacionesos 16,752 rec


Query:

--------


SELECT DP.CodigoProfesionEfector, DP.MatriculaProfesionalEfector, 

SUM((ImporteHonorarios+ImporteD

erechos+ImporteCompensacion)*Cantidad+ImporteGastos+ImporteGastosNoGravados) AS Importe
FROM DetallePrestaciones DP INNER JOIN LiquidacionesOS L ON DP.CodigoLiquidacionOS=L.CodigoLiquidacionOS
WHERE L.Per_a='2005' AND L.Facturada AND L.CodigoObraSocial IN(54)
GROUP BY DP.CodigoProfesionEfector, DP.MatriculaProfesionalEfector;

Explains:
------------
With SET ENABLE_SEQSCAN TO ON;
HashAggregate  (cost=251306.99..251627.36 rows=11650 width=78)
  ->  Hash Join  (cost=1894.30..250155.54 rows=153526 width=78)
        Hash Cond: ("outer".codigoliquidacionos = "inner".codigoliquidacionos)
        ->  Seq Scan on detalleprestaciones dp  (cost=0.00..219621.32 rows=5420932 width=82)
        ->  Hash  (cost=1891.01..1891.01 rows=1318 width=4)
              ->  Bitmap Heap Scan on liquidacionesos l  (cost=43.89..1891.01 rows=1318 width=4)
                    Recheck Cond: (codigoobrasocial = 54)
                    Filter: ((per_a = '2005'::bpchar) AND facturada)
                    ->  Bitmap Index Scan on ixliqos_os  (cost=0.00..43.89 rows=4541 width=0)
                          Index Cond: (codigoobrasocial = 54)

With SET ENABLE_SEQSCAN TO OFF;
HashAggregate  (cost=2943834.84..2944155.21 rows=11650 width=78)
  ->  Nested Loop  (cost=0.00..2942683.39 rows=153526 width=78)
        ->  Index Scan using liqos_pkey on liquidacionesos l  (cost=0.00..3020.21 rows=1318 width=4)
              Filter: ((per_a = '2005'::bpchar) AND facturada AND (codigoobrasocial = 54))
        ->  Index Scan using dp_pkey on detalleprestaciones dp  (cost=0.00..2214.90 rows=1240 width=82)
              Index Cond: (dp.codigoliquidacionos = "outer".codigoliquidacionos)

Thanks for your time!!!!
Alejandro