Обсуждение: performance difference between pgsql and SQL

Hi,

I am using postgreSQL 7.3.4

I have a single table in the databasse.

mytable(id, tag, parent, label)
all in int8.

hash index is built for id and btree index for all attributes.


now, I have over 9 million tuples in the table.

The problem is when I access the database through
psql interface, where I issued the following query:

select tag from mytable where id = 1;

it took 51880 milliseconds to execute.

the "explain" command showed it used seq scan.

I turned enable_seqscan and enable_nestloop off, and it still
used seqscan.



however, if I call it through a pgsql function, it only took 
aobut 0.17 milliseconds.

create function test_tag(int8) returns int8 as'
declare
begin   return tag from mytable where id = $1;
end;
' language 'plpgsql';


This looks like a bug to me.
-- 
Bill Shui

On Sat, 11 Oct 2003, Bill Shui wrote:

> Hi,
>
> I am using postgreSQL 7.3.4
>
> I have a single table in the databasse.
>
> mytable(id, tag, parent, label)
> all in int8.
>
> hash index is built for id and btree index for all attributes.
>

Hash indexes  are generally a bad idea in 7.3.

> select tag from mytable where id = 1;

Try: select tag from mytable where id = 1::int8;

This is a known flaw of the typing system which the community has been
discussing/attempting to fix for some time.

Gavin