Re: 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

On Tue, Mar 21, 2006 at 03:08:07PM -0300, Fernando Lujan wrote:
> 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. :)

That is a very broad question, and you're introducing somewhat of a false
choice since you're talking about joins (a join can be solved by more methods
than just "sequential scan" or not).

Could you please paste the exact query you're using, with EXPLAIN ANALYZE for
both the case with and without sequential scans?

/* Steinar */
--
Homepage: http://www.sesse.net/

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(dot)lujan(at)mandic(dot)com(dot)br>
To: <pgsql-performance(at)postgresql(dot)org>
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
>

2006/3/21, Reimer <carlosreimer(at)terra(dot)com(dot)br>:
>
> 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(dot)lujan(at)mandic(dot)com(dot)br>
> To: <pgsql-performance(at)postgresql(dot)org>
> 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(at)postgresql(dot)org 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.32rows=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.89rows=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

On Wed, Mar 22, 2006 at 08:50:20AM -0300, Alejandro D. Burne wrote:
> Explains:
> ------------
> With SET ENABLE_SEQSCAN TO ON;
> HashAggregate (cost=251306.99..251627.36 rows=11650 width=78)

You'll need to post EXPLAIN ANALYZE results, not just EXPLAIN.

/* Steinar */
--
Homepage: http://www.sesse.net/

2006/3/22, Steinar H. Gunderson <sgunderson(at)bigfoot(dot)com>:
>
> On Wed, Mar 22, 2006 at 08:50:20AM -0300, Alejandro D. Burne wrote:
> > Explains:
> > ------------
> > With SET ENABLE_SEQSCAN TO ON;
> > HashAggregate (cost=251306.99..251627.36 rows=11650 width=78)
>
> You'll need to post EXPLAIN ANALYZE results, not just EXPLAIN.
>
> /* Steinar */
> --
> Homepage: http://www.sesse.net/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

Sorry, this is the result:

WITH SET ENABLE_SEQSCAN TO ON;

HashAggregate (cost=251306.99..251627.36 rows=11650 width=78) (actual time=
25089.024..25090.340 rows=1780 loops=1)
-> Hash Join (cost=1894.30..250155.54 rows=153526 width=78) (actual
time=3190.599..24944.418 rows=38009 loops=1)
Hash Cond: ("outer".codigoliquidacionos =
"inner".codigoliquidacionos)
-> Seq Scan on detalleprestaciones dp
(cost=0.00..219621.32rows=5420932 width=82) (actual time=
0.058..23198.852 rows=5421786 loops=1)
-> Hash (cost=1891.01..1891.01 rows=1318 width=4) (actual time=
60.777..60.777 rows=1530 loops=1)
-> Bitmap Heap Scan on liquidacionesos l (cost=
43.89..1891.01 rows=1318 width=4) (actual time=1.843..59.574 rows=1530
loops=1)
Recheck Cond: (codigoobrasocial = 54)
Filter: ((per_a = '2005'::bpchar) AND facturada)
-> Bitmap Index Scan on ixliqos_os
(cost=0.00..43.89rows=4541 width=0) (actual time=
1.439..1.439 rows=4736 loops=1)
Index Cond: (codigoobrasocial = 54)
Total runtime: 25090.920 ms

WITH SET ENABLE_SEQSCAN TO OFF;
HashAggregate (cost=2943834.84..2944155.21 rows=11650 width=78) (actual
time=1479.361..1480.641 rows=1780 loops=1)
-> Nested Loop (cost=0.00..2942683.39 rows=153526 width=78) (actual
time=195.690..1345.494 rows=38009 loops=1)
-> Index Scan using liqos_pkey on liquidacionesos l (cost=
0.00..3020.21 rows=1318 width=4) (actual time=174.546..666.761 rows=1530
loops=1)
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) (actual time=0.333..0.422 rows=25
loops=1530)
Index Cond: (dp.codigoliquidacionos =
"outer".codigoliquidacionos)
Total runtime: 1481.244 ms

Thanks again, Alejandro