Обсуждение: sequencial scans

Greetings,

  With short words (time is money and my english is very bad), why sequencial scans in the example below ?

persona=# CREATE TABLE teste_erro(
persona(#   cod integer,
persona(#   CONSTRAINT XPKteste_erro PRIMARY KEY(cod));
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'xpkteste_erro' for table 'teste_erro'
CREATE

persona=# CREATE FUNCTION teste_proc(VARCHAR, VARCHAR) RETURNS integer AS '
persona'# DECLARE
persona'#   numeroa ALIAS for $1;
persona'#   dddlocal ALIAS for $2;
persona'# BEGIN
persona'#   RETURN 666;
persona'# END;
persona'# ' LANGUAGE 'plpgsql';
CREATE

persona=# explain select * from teste_erro where cod = teste_proc('3138414411','19');
NOTICE:  QUERY PLAN:
Seq Scan on teste_erro  (cost=0.00..25.00 rows=10 width=4)
EXPLAIN

persona=#explain select * from teste_erro where cod = 313;
NOTICE:  QUERY PLAN:
Index Scan using xpkteste_erro on teste_erro  (cost=0.00..8.14 rows=10 width=4)
EXPLAIN


Regards,

José Vilson de Mello de Farias
Dígitro Tecnologia Ltda - Brazil

Has anyone on this list used sql-relay with pgsql?  If you're not familiar
with it, you can find out more at:
http://www.firstworks.com/sqlrelay.html.  My primary interest is to reap
the benefits of connection pooling on an Apache/PHP/PostgreSQL
configuration.  We're setting up a farm of webservers to talk to our DB, if
we use PHP pooling, we may end up with thousands un-needed open
connections.  Sql-relay looks a bit involved to set up - but just may be
worth it.

TIA

-Ryan Mahoney
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01

"Vilson farias" <vilson.farias@digitro.com.br> writes:
> persona=# explain select * from teste_erro where cod = teste_proc('3138414411','19');
> NOTICE:  QUERY PLAN:
> Seq Scan on teste_erro  (cost=0.00..25.00 rows=10 width=4)
> EXPLAIN

You need to mark the function cachable, else the planner doesn't
believe it's a constant.  See "iscachable" in the CREATE FUNCTION
reference page.

            regards, tom lane

Ryan Mahoney wrote:
> Has anyone on this list used sql-relay with pgsql?  If you're not familiar
> with it, you can find out more at:
> http://www.firstworks.com/sqlrelay.html.  My primary interest is to reap
> the benefits of connection pooling on an Apache/PHP/PostgreSQL
> configuration.  We're setting up a farm of webservers to talk to our DB, if
> we use PHP pooling, we may end up with thousands un-needed open
> connections.  Sql-relay looks a bit involved to set up - but just may be
> worth it.

    Well,  the  descriptions look really interesting so far. I'll
    take a closer look at it soon.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

On Thu, Jul 05, 2001 at 03:00:59PM -0400, Jan Wieck wrote:
> Ryan Mahoney wrote:
> > Has anyone on this list used sql-relay with pgsql?  If you're not familiar
> > with it, you can find out more at:
> > http://www.firstworks.com/sqlrelay.html.  My primary interest is to reap
> > the benefits of connection pooling on an Apache/PHP/PostgreSQL
> > configuration.  We're setting up a farm of webservers to talk to our DB, if
> > we use PHP pooling, we may end up with thousands un-needed open
> > connections.  Sql-relay looks a bit involved to set up - but just may be
> > worth it.
>
>     Well,  the  descriptions look really interesting so far. I'll
>     take a closer look at it soon.

We looked at it a while ago, and were unhappy with it.  I'm trying to
remember why.  I believe it was something to do with it mangling the
types of objects returned.  In any case, now that I'm interested again
I'll see if it's improved since then ;-)


--
Matt Beauregard
Technical Director, Designscape
Ph: +61 2 9361 4233   Fx: +61 2 9361 4633

Permission is given for unlimited distribution of this work under the
Copyright Act 1968 (2001 amended).  Unless otherwise stated, you agree
that these terms also cover works which you send in response.