the "SORT" late very much.

From: "jose antonio leo" <jaleo8(at)storelandia(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: the "SORT" late very much.
Date: 2002-11-26 12:38:55
Message-ID: KAEFLIDKHEJNPPKOLGHMCEGBCFAA.jaleo8@storelandia.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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" .

The next sentences return the next explain analyze.

EXPLAIN ANALYZE
SELECT cod_pto,date,int_art,tip_mov,qty
FROM vtdiaart v
WHERE date>='2002/08/1' and date <='2002/11/30'
ORDER BY date asc,int_art;

NOTICE: QUERY PLAN:
Sort (cost=100252.07..100252.07 rows=116313 width=71) (actual
time=22958.25..2)
-> Seq Scan on vtdiaart v (cost=0.00..80825.71 rows=116313 width=71)
(actua)
Total runtime: 23815.94 msec
EXPLAIN

EXPLAIN ANALYZE
SELECT cod_pto,date,int_art,tip_mov,qty
FROM vtdiaart v
WHERE date>='2002/08/1' and date <='2002/11/30' and cod_pto='1001401'
ORDER BY date asc,int_art;

NOTICE: QUERY PLAN:
Sort (cost=90212.32..90212.32 rows=34118 width=71) (actual
time=18056.57..1811)
-> Seq Scan on vtdiaart v (cost=0.00..86779.49 rows=34118 width=71)
(actual)
Total runtime: 18283.06 msec
EXPLAIN

EXPLAIN ANALYZE
SELECT cod_pto,date,int_art,tip_mov,qty
FROM vtdiaart v
WHERE date>='2002/08/1' and date <='2002/11/30' and cod_pto='1001401'
and int_art='29324'
ORDER BY date asc,int_art;

EXPLAIN
NOTICE: QUERY PLAN:
Sort (cost=38.27..38.27 rows=9 width=71) (actual time=167.42..167.47
rows=95 l)
-> Index Scan using vtdiaart_pkey on vtdiaart v (cost=0.00..38.13 rows=9
wi)
Total runtime: 167.62 msec
EXPLAIN

Only the last sentence use the index

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Richard Huxton 2002-11-26 13:03:35 Re: the "SORT" late very much.
Previous Message Savita 2002-11-26 12:06:22 Re: COPY COMMAND