Re: the "SORT" late very much.

On Tuesday 26 Nov 2002 12:38 pm, jose antonio leo wrote:
> Hi,
> I don't understand because the sort late very much.
> I have a table "vtdiaart" with the next fields:
> date,int_art,cod_pto,tip_mov,qty and 2381514 records.
> They are 4 index created.
> name fields
> 1: vtdiaart_pkey: "date, int_art, cod_pto" .
> 2: vtdiaart_d_ia: "date, int_art".
> 3: vtdiaart_d_cp: "date,cod_pto".
> 4: vtdiaart_d: "date" .

Not sure I'd call a column "date", but that's beside the point here.

> WHERE date>='2002/08/1' and date <='2002/11/30'
> -> Seq Scan on vtdiaart v (cost=0.00..80825.71 rows=116313 width=71)

> WHERE date>='2002/08/1' and date <='2002/11/30' and cod_pto='1001401'
> -> Seq Scan on vtdiaart v (cost=0.00..86779.49 rows=34118 width=71)

Here PG estimates 34118 and 116313 rows - that's a lot of index entries to
read followed by a lot of reads for the data. PG decides to just scan the
table.

> WHERE date>='2002/08/1' and date <='2002/11/30' and cod_pto='1001401'
> -> Index Scan using vtdiaart_pkey on vtdiaart v (cost=0.00..38.13 rows=9

Here we're getting 9 rows - PG decides to use the index.

Make sure you've run VACUUM ANALYSE and if that doesn't help try
SET ENABLE_SEQSCAN = 'off'
and see if that forces the index to be used.

--
Richard Huxton