Re: Problema Order By en PosgreSQL 8.1

Alvaro:

>
> Por favor prueba lo siguiente: crea la tabla y agrega los resultados,
> tal como lo hiciste antes. Ejecuta VACUUM ANALYZE tablita, y despues
>
> SET enable_seqscan TO off;
> y prueba nuevamente la consulta.

Me salé:

Paul2=# select tablita.idDia , tablita.numHora
Paul2-# from tablita
Paul2-# WHERE tablita.campoA = 1 AND
Paul2-# tablita.campoB =1 AND
Paul2-# tablita.campoC =1 AND
Paul2-# tablita.campoD =1 AND
Paul2-# tablita.campoE = 1
Paul2-# ORDER BY tablita.idDia , tablita.numHora;
iddia | numhora
-------+---------
1 | 1
1 | 2
1 | 3
1 | 4
1 | 5
1 | 6
1 | 7
1 | 8
2 | 1
2 | 2
2 | 3
2 | 4
2 | 5
2 | 6
2 | 7
2 | 8
3 | 1
3 | 2
3 | 3
3 | 4
3 | 5
3 | 6
3 | 7
3 | 8
4 | 1
4 | 2
4 | 3
4 | 4
4 | 5
4 | 6
4 | 7
4 | 8
5 | 1
5 | 2
5 | 3
5 | 4
5 | 5
5 | 6
5 | 7
5 | 8
(40 rows)

> Envia el EXPLAIN ANALYZE, y verifica

El EXPLAIN salé :

Sort (cost=2.96..3.06 rows=40 width=8) (actual time=0.338..0.375
rows=40 loops=1)
Sort Key: iddia, numhora
-> Seq Scan on tablita (cost=0.00..1.90 rows=40 width=8) (actual
time=0.028..0.188 rows=40 loops=1)
Filter: ((campoa = 1) AND (campob = 1) AND (campoc = 1) AND
(campod = 1) AND (campoe = 1))
Total runtime: 0.501 ms

> si el orden es correcto o no.

el orden es correcto :)

>
> Luego: borra el indice. ALTER TABLE tablita DROP PRIMARY KEY.

borrado constraint

tablita_pkey PRIMARY KEY (campoA, campoB, campoC, campoD, campoE,
campoF, iddia, numhora);

> Luego
> crealo de nuevo: ALTER TABLE tablita ADD PRIMARY KEY ( ... )

creando:

ALTER TABLE ONLY tablita
ADD CONSTRAINT tablita_pkey PRIMARY KEY (campoA, campoB, campoC,
campoD, campoE, campoF, iddia, numhora);

> Luego
> SET enable_seqscan TO off;
> y la consulta otra vez; pega el EXPLAIN ANALYZE

me salé :

Sort (cost=2.96..3.06 rows=40 width=8) (actual time=0.330..0.368
rows=40 loops=1)
Sort Key: iddia, numhora
-> Seq Scan on tablita (cost=0.00..1.90 rows=40 width=8) (actual
time=0.026..0.184 rows=40 loops=1)
Filter: ((campoa = 1) AND (campob = 1) AND (campoc = 1) AND
(campod = 1) AND (campoe = 1))
Total runtime: 0.491 ms

> e indica si el resultado
> es correcto o no.
>

me sale correcto :)

Paul2=# select tablita.idDia , tablita.numHora
Paul2-# from tablita
Paul2-# WHERE tablita.campoA = 1 AND
Paul2-# tablita.campoB =1 AND
Paul2-# tablita.campoC =1 AND
Paul2-# tablita.campoD =1 AND
Paul2-# tablita.campoE = 1
Paul2-# ORDER BY tablita.idDia , tablita.numHora;
iddia | numhora
-------+---------
1 | 1
1 | 2
1 | 3
1 | 4
1 | 5
1 | 6
1 | 7
1 | 8
2 | 1
2 | 2
2 | 3
2 | 4
2 | 5
2 | 6
2 | 7
2 | 8
3 | 1
3 | 2
3 | 3
3 | 4
3 | 5
3 | 6
3 | 7
3 | 8
4 | 1
4 | 2
4 | 3
4 | 4
4 | 5
4 | 6
4 | 7
4 | 8
5 | 1
5 | 2
5 | 3
5 | 4
5 | 5
5 | 6
5 | 7
5 | 8
(40 rows)

>
> Me gustaria saber si alguien mas puede reproducir este problema en
> Windows.
>
> --
> Alvaro Herrera http://www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.
>

Paolo.