Thread: the "SORT" late very much.
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
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
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. Right, the name of the column is "fecha", Date in spanish. > 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. With SET ENABLE_SEQSCAN = 'off' the time is upper. It is possible that this not have improvement? After, I join other table for extract the description of the column int_art and the run time will be to long.??