Thread: 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/

Assuming you are joining on "Table 1".id = "Table 2".id - do you have indexes on both columns? Have you analyzed your
tables+ indexes (are there statistics available?) If not those criterias are met, it is unlikely that postgres will
choosean index scan. 

-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Fernando
Lujan
Sent: den 21 mars 2006 19:08
To: pgsql-performance@postgresql.org
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

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@mandic.com.br>
To: <pgsql-performance@postgresql.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@terra.com.br>:
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

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@bigfoot.com>:
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.32 rows=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.89 rows=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