Обсуждение: Location of 7.1 version for download

----- Original Message -----

From: Paulo Parola

To: pgsql-general@postgresql.org

Sent: Thursday, February 22, 2001 10:04 AM

Subject: Location of 7.1 version for download

Where can I download PostgreSQL v7.1? Please forward me to the URL where I can download it. Is there already binaries or do I have to compile it?

 

TIA,

Paulo

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

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 <neilconway@home.com>
Get my GnuPG key from: http://klamath.dyndns.org/mykey.asc
Encrypted mail welcomed

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

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

Renaud Tthonnart writes:

> Table Observation has 5000 rows  and  10 columns
> Table Exposure_Epic has 45000 rows and 6 columns
> I haven't create any index

Time to start then.

--
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/

Renaud Tthonnart <thonnart@amwdb.u-strasbg.fr> 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

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