Re: [GENERAL] Query with multiples operators BETWEEN

2001-02-23 Thread Renaud Tthonnart 

Tom Lane wrote:

>
> Do a VACUUM ANALYZE, for starters.  These results look like the planner
> is working with the initial default estimates for a never-vacuumed table.
>
>
> Indexes might be a good idea too.
> http://www.postgresql.org/devel-corner/docs/postgres/indices.html
> has a good intro to the basics.
>
> regards, tom lane

The qwery  I have problem with is always :

SELECT e.name
FROM Observation o, Exposure_EPIC e
WHERE o.numObs = e.obs
  AND e.instrPN IS NOT NULL
  AND o.RA BETWEEN 3 AND 5
  AND o.DE BETWEEN 2 AND 7;

EXPLAIN result is :

NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..56.67 rows=3 width=20)
  ->  Seq Scan on observation o  (cost=0.00..30.00 rows=1 width=4)
  ->  Seq Scan on exposure_epic e  (cost=0.00..22.50 rows=333 width=16)

EXPLAIN


I have created an index on columns ra and de :
CREATE INDEX ra_de_ind ON observation(ra,de);
CLUSTER ra_de_ind ON observation;
VACUUM observation;


EXPLAIN result becomes :

Nested Loop  (cost=0.00..28.69 rows=3 width=20)
  ->  Index Scan using ra_de_ind on observation o  (cost=0.00..2.03 rows=1
width=4)
  ->  Seq Scan on exposure_epic e  (cost=0.00..22.50 rows=333 width=16)


But the qwery is always not able to find what I'm looking for.

Renaud THONNART

Re: [GENERAL] Query with multiples operators BETWEEN

2001-02-22 Thread Tom Lane 

Renaud Tthonnart <[EMAIL PROTECTED]> writes:
> Nested Loop  (cost=0.00..56.67 rows=3 width=20)
>   ->  Seq Scan on observation o  (cost=0.00..30.00 rows=1 width=4)
>   ->  Seq Scan on exposure_epic e  (cost=0.00..22.50 rows=333 width=16)

Do a VACUUM ANALYZE, for starters.  These results look like the planner
is working with the initial default estimates for a never-vacuumed table.

> I haven't create any index

Indexes might be a good idea too.
http://www.postgresql.org/devel-corner/docs/postgres/indices.html
has a good intro to the basics.

regards, tom lane

Re: [GENERAL] Query with multiples operators BETWEEN

2001-02-22 Thread Renaud Tthonnart 

Here they are :

EXPLAIN
(SELECT e.name
FROM Observation o, Exposure_EPIC e
WHERE o.numObs = e.obs
  AND e.instrPN IS NOT NULL
  AND o.RA BETWEEN 3 AND 5);


NOTICE:  QUERY PLAN:

Hash Join  (cost=25.02..60.98 rows=33 width=20)
  ->  Seq Scan on exposure_epic e  (cost=0.00..22.50 rows=333 width=16)
  ->  Hash  (cost=25.00..25.00 rows=10 width=4)
->  Seq Scan on observation o  (cost=0.00..25.00 rows=10
width=4)

EXPLAIN



EXPLAIN
(SELECT e.name
FROM Observation o, Exposure_EPIC e
WHERE o.numObs = e.obs
  AND e.instrPN IS NOT NULL
  AND o.DE BETWEEN 2 AND 7);

NOTICE:  QUERY PLAN:

Hash Join  (cost=25.02..60.98 rows=33 width=20)
  ->  Seq Scan on exposure_epic e  (cost=0.00..22.50 rows=333 width=16)
  ->  Hash  (cost=25.00..25.00 rows=10 width=4)
->  Seq Scan on observation o  (cost=0.00..25.00 rows=10
width=4)

EXPLAIN


---

EXPLAIN
(SELECT e.name
FROM Observation o, Exposure_EPIC e
WHERE o.numObs = e.obs
  AND e.instrPN IS NOT NULL
  AND o.RA BETWEEN 3 AND 5
  AND o.DE BETWEEN 2 AND 7);

NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..56.67 rows=3 width=20)
  ->  Seq Scan on observation o  (cost=0.00..30.00 rows=1 width=4)
  ->  Seq Scan on exposure_epic e  (cost=0.00..22.50 rows=333 width=16)

EXPLAIN
--
Table Observation has 5000 rows  and  10 columns
Table Exposure_Epic has 45000 rows and 6 columns
I haven't create any index
If use--->  >2 and <7 , this is the same result.

Thank you for your help
Renaud THONNART

Re: [GENERAL] Query with multiples operators BETWEEN

2001-02-22 Thread Neil Conway 

On Thu, Feb 22, 2001 at 05:10:51PM +0100, Renaud Tthonnart wrote:
> This qwery takes about 2 seconds :
[..]

Can you send us the output of EXPLAIN for each of these queries?
Also, how large are the tables you're working with? What
indexes have been created?

Cheers,

Neil

-- 
Neil Conway <[EMAIL PROTECTED]>
Get my GnuPG key from: http://klamath.dyndns.org/mykey.asc
Encrypted mail welcomed

Good composers borrow. Great composers steal.
-- Igor Stravinsky

[GENERAL] Query with multiples operators BETWEEN

2001-02-22 Thread Renaud Tthonnart 


This qwery takes about 2 seconds :

SELECT e.name
FROM Observation o, Exposure_EPIC e
WHERE o.numObs = e.obs
  AND e.instrPN IS NOT NULL
  AND o.RA BETWEEN 3 AND 5;

This one about 5 seconds :

SELECT e.name
FROM Observation o, Exposure_EPIC e
WHERE o.numObs = e.obs
  AND e.instrPN IS NOT NULL
  AND o.DE BETWEEN 2 AND 7;

And this takes a lot of time (I can't say how much because I 've
always given it up before its end,
 at least
more than 10 minutes)

SELECT e.name
FROM Observation o, Exposure_EPIC e
WHERE o.numObs = e.obs
  AND e.instrPN IS NOT NULL
  AND o.RA BETWEEN 3 AND 5
  AND o.DE BETWEEN 2 AND 7;

Could someone help or explain me?

Thanks in advance and excuse my bad English
Renaud THONNART