Re: [GENERAL] datestyle question

[Alban Hertroys]

Diego Gil wrote:
> Hi,
> 
> I have a file to import to postgresql that have an unusual date format.
> For example, Jan 20 2007 is 20022007, in DDMM format, without any
> separator. I know that a 20072002 (MMDD) is ok, but I don't know how
> to handle the DDMM dates.

You could try importing those fields in a text field in a temporary
table and then convert them from there into your final tables using the
to_date() function.

If 20022007 really means 20 Jan instead of 20 Feb, try something like:

insert into my_table (my_date_field)
select to_date(my_date_text_field, 'DDMM') - interval '1 month'
  from my_temp_table;

Regards,
-- 
Alban Hertroys
[EMAIL PROTECTED]

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Performance Issues

[Peter Childs]

On 23/09/2007, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
>
> Christian Schröder wrote:
> > Alvaro Herrera wrote:
> >> Christian Schröder wrote:
> >>
> >>
> >>> I think it is my job as db admin to make the database work the way my
> >>> users need it, and not the user's job to find a solution that fits the
> >>> database's needs ...
> >>>
> >>> Is there really nothing that I can do?
> >>>
> >>
> >> You can improve the selectivity estimator function.  One idea is that
> if
> >> you are storing something that's not really a general character string,
> >> develop a specific datatype, with a more precise selectivity estimator.
> >> If you are you up to coding in C, that is.
> >>
> >
> > Hm, that sounds interesting! I will definitely give it a try.
> > Will that also solve the problem of combining more than one of these
> > conditions? As far as I can see, the main issue at the moment is that we
> > often have "... where test like '11%' and test not like '113%'" in our
> > queries. Even if the selectivity estimation of the single condition will
> be
> > improved, it will still be wrong to multiply the selectivities.
>
> Unless you can come up with an operator that expresses better the
> "starts with 11 but not with 113" type of condition.  For example if
> these were telephone number prefixes or something like that, probably
> there's some way to do that in a single operation instead of two, and
> the selectivity function could produce a much more accurate estimate
> saving the need to multiply.



select a from b where a ~ '^11[^3]'

Is that what you want?

I usually find using ~ far better than like.

Peter Childs


--
> Alvaro Herrera
> http://www.advogato.org/person/alvherre
> "I think my standards have lowered enough that now I think 'good design'
> is when the page doesn't irritate the living f*ck out of me." (JWZ)
>
> ---(end of broadcast)---
> TIP 1: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to [EMAIL PROTECTED] so that your
>message can get through to the mailing list cleanly
>

[GENERAL] Find "smallest common year"

[Stefan Schwarzer]

Hi there,

my aim is to plot a line graph for a single country but for two or
three variables. Now, one could use two different y-axis (if there
are two data sets with different units); but it's not really the
right way to analyse data.

One should rather normalize them to a base year to enhance
comparisons. To achieve this, one would set the values for a specific
year to 1 (or 100) and put the other values of all variables in
relation to it.

Thus, taking two variables, say,

Fish Catch  
- in Thousand Metric Tons
- data available for 1975 to 2005
and
Total Population
- in Million People
- data available for 1960 to 2005

Country is Kenya.

The "smallest common year" would be 1975. One needs thus to set the
values for that year to 1 (or 100), and calculate the other values of
the variables in respect to the needed factor.

Now the question is how to find that "smallest common year"
automatically, via SQL or PHP?

Can anyone give me a hint? Thanks for any help!

Stef

 

  Stefan Schwarzer

  Lean Back and Relax - Enjoy some Nature Photography:
  http://photoblog.la-famille-schwarzer.de

  Appetite for Global Data? UNEP GEP Data Portal:
  http://geodata.grid.unep.ch
  

Re: [GENERAL] Find "smallest common year"

[William Leite Araújo]

If I understood your question, maybe it's you want:

   SELECT min() FROM table1 JOIN table2 ON (table1.data_field=
table2.data_field);

2007/9/27, Stefan Schwarzer <[EMAIL PROTECTED]>:
>
> Hi there,
>
> my aim is to plot a line graph for a single country but for two or
> three variables. Now, one could use two different y-axis (if there
> are two data sets with different units); but it's not really the
> right way to analyse data.
>
> One should rather normalize them to a base year to enhance
> comparisons. To achieve this, one would set the values for a specific
> year to 1 (or 100) and put the other values of all variables in
> relation to it.
>
> Thus, taking two variables, say,
>
>
>  Fish Catch
>  - in Thousand Metric Tons
>  - data available for 1975 to 2005
> and
>  Total Population
>  - in Million People
>  - data available for 1960 to 2005
>
> Country is Kenya.
>
> The "smallest common year" would be 1975. One needs thus to set the
> values for that year to 1 (or 100), and calculate the other values of
> the variables in respect to the needed factor.
>
> Now the question is how to find that "smallest common year"
> automatically, via SQL or PHP?
>
> Can anyone give me a hint? Thanks for any help!
>
> Stef
>
>  
>
>
>   *Stefan Schwarzer*
>
>   Lean Back and Relax - Enjoy some Nature Photography:
>   http://photoblog.la-famille-schwarzer.de
>
>   Appetite for Global Data? UNEP GEP Data Portal:
>   http://geodata.grid.unep.ch
>   
>
>
>
>
>
>


-- 
William Leite Araújo
Estudante de paternidade - 24a semana

Re: [GENERAL] Find "smallest common year"

[Stefan Schwarzer]

Sorry,

I forgot to mention my table design, which is like this:

 name 20012002   2003   2004   2005
-
Afghanistan
Albania


(Yep, I know, bad table design :-)). I tried to change it to the  
more common "id | year | value" format, but there were too many SQL  
problems afterwards for the different queries/calculations we need to  
have)


So, the mentioned SQL unfortunately doesn't work in that case

Stef

On Sep 27, 2007, at 1:52 PM, William Leite Araújo wrote:


If I understood your question, maybe it's you want:

   SELECT min() FROM table1 JOIN table2 ON  
(table1.data_field = table2.data_field);


2007/9/27, Stefan Schwarzer <[EMAIL PROTECTED] >:
Hi there,

my aim is to plot a line graph for a single country but for two or
three variables. Now, one could use two different y-axis (if there
are two data sets with different units); but it's not really the
right way to analyse data.

One should rather normalize them to a base year to enhance
comparisons. To achieve this, one would set the values for a specific
year to 1 (or 100) and put the other values of all variables in
relation to it.

Thus, taking two variables, say,

Fish Catch  
- in Thousand Metric Tons
- data available for 1975 to 2005
and
Total Population
- in Million People
- data available for 1960 to 2005

Country is Kenya.

The "smallest common year" would be 1975. One needs thus to set the
values for that year to 1 (or 100), and calculate the other values of
the variables in respect to the needed factor.

Now the question is how to find that "smallest common year"
automatically, via SQL or PHP?

Can anyone give me a hint? Thanks for any help!

Stef

 

   Stefan Schwarzer

  Lean Back and Relax - Enjoy some Nature Photography :
  http://photoblog.la-famille-schwarzer.de

   Appetite for Global Dat a? UNEP GEP Data Portal:
  http://geodata.grid.unep.ch











--
William Leite Araújo
Estudante de paternidade - 24a semana

Re: [GENERAL] Find "smallest common year"

[Carlos Moreno]

Stefan Schwarzer wrote:

Sorry,

I forgot to mention my table design, which is like this:

 name 20012002   2003   2004   2005
-
Afghanistan
Albania

(Yep, I know, bad table design :-)). I tried to change it to the 
more common "id | year | value" format, but there were too many SQL 
problems afterwards for the different queries/calculations we need to 
have)


May I suggest that you concentrate on solving *those* problems instead of
the programmatically trivial computation of lowest common value?  Notice
that a *really trivial* programming exercise becomes highly involved in
your case --- if I'm understanding correctly what you have, I assume you'd
have to check one by one the fields for NULL or non-NULL values --- that's
intolerably ugly, IMHO, and it is a very simple and direct consequence of
an as-unsound-as-it-gets db/table design.

Carlos
--


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] pg_dump (8.1.9) does not output copy statements

[Jan de Visser]

On Thursday 27 September 2007 00:38:15 Tom Lane wrote:
> Jan de Visser <[EMAIL PROTECTED]> writes:
> > In my world two identical pilot errors within a short timeframe are
> > indicat= ive=20
> > of a bad interface.
>
> Yeah, it's inconsistent.  How many people's dump scripts do you want to
> break to make it more consistent?

I understand, I'm just sayin' that in this case calling it 'pilot error' is a 
bit harsh :)

jan

-- 
--
Jan de Visser                     [EMAIL PROTECTED]

                Baruk Khazad! Khazad ai-menu!
--

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[GENERAL] How unsubscrib this list ?

[Gerson Machado]

How unsubscrib this list ?
   Flickr agora em português. Você clica, todo mundo vê. Saiba mais.

[GENERAL] TSearch - Bulgarian

[Hristo Filipov]

Hello world!

J!! We are Bulgarian developers currently working on some web project(server
must run on windows and linux) and we stop our choice for database at
PostgreSQL, which I may say offers great things at no cost at all :-P.

So we have our tables ready after some times (new for psql, migrating
database from access) all works  fine! Even better! But we can't get to run
FTS on PostgreSQL L. We have tables in locale Bulgaria_Bulgarian_1251,
Bulgaria_Bulgarian_UTF8. I read "Full-Text Search in PostgreSQL - A Gentle
Introduction" from Oleg Bartunov and Teodor Sigaev (great work guys!), and
it open my eyes in some way, but I still can't understand what I need to do
- so I can create FTS for Bulgarian language L and make TSearch work for us.
Can someone help us?

 

Hristo Filipov

Re: [GENERAL] How unsubscrib this list ?

[Bill Moran]

In response to Gerson Machado <[EMAIL PROTECTED]>:

> How unsubscrib this list ?
>Flickr agora em português. Você clica, todo mundo vê. Saiba mais.

The information is in the mail headers:

List-Archive: 
List-Help: 
List-ID: 
List-Owner: 
List-Post: 
List-Subscribe: 
List-Unsubscribe: 

-- 
Bill Moran
http://www.potentialtech.com

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] TSearch - Bulgarian

[Christian Rengstl]

You might have to create a Bulgarian dictionary first before you will be
able to successfully use tsearch. Maybe some information here will help
you:
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/


Christian Rengstl M.A.
Klinik und Poliklinik für Innere Medizin II
Kardiologie - Forschung
Universitätsklinikum Regensburg
B3 1.388
Franz-Josef-Strauss-Allee 11
93053 Regensburg
Tel.: +49-941-944-7230




>>> On Thu, Sep 27, 2007 at  3:17 PM, in message
<[EMAIL PROTECTED]>, "Hristo Filipov"
<[EMAIL PROTECTED]>
wrote: 
> Hello world!
> 
> J!! We are Bulgarian developers currently working on some web
project(server
> must run on windows and linux) and we stop our choice for database
at
> PostgreSQL, which I may say offers great things at no cost at all :-
P.
> 
> So we have our tables ready after some times (new for psql,
migrating
> database from access) all works  fine! Even better! But we can't get
to run
> FTS on PostgreSQL L. We have tables in locale
Bulgaria_Bulgarian_1251,
> Bulgaria_Bulgarian_UTF8. I read "Full- Text Search in PostgreSQL -  A
Gentle
> Introduction" from Oleg Bartunov and Teodor Sigaev (great work
guys!), and
> it open my eyes in some way, but I still can't understand what I need
to do
> -  so I can create FTS for Bulgarian language L and make TSearch work
for us.
> Can someone help us?
> 
>  
> 
> Hristo Filipov


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

[GENERAL] access privileges: grant select on (all current and future tables)?

[John Smith]

how'd i "grant select on (all current and future tables inside a
private schema) to username" without turning that user into superuser?
"grant usage on..." doesn't do it.

or do i, everytime i batch/auto create the tables, do a "grant select
on (new table) to username"?

http://www.postgresql.org/docs/8.1/static/sql-grant.html
CREATE: For schemas, allows new objects to be created within the
schema. To rename an existing object, you must own the object and have
this privilege for the containing schema.
USAGE: For schemas, allows access to objects contained in the
specified schema (assuming that the objects' own privilege
requirements are also met). Essentially this allows the grantee to
"look up" objects within the schema.

using 8.1.4
cheers, jzs

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] Re: Why the ERROR: duplicate key violates unique constraint "master_pkey" is raised? - Is this a Bug?

[Nis Jørgensen]

Ardian Xharra skrev:

> *From:* Anoo Sivadasan Pillai 

>> I am not using any sequences, The following batch can reproduce the
>> behaviour.
>> CREATE TABLE master ( m1 INT primary key , m2 int unique ) ;
>> INSERT  INTO master VALUES  ( 1, 1 ) ;
>> INSERT  INTO master VALUES  ( 2, 2) ;
>> UPDATE  master SET m2 = m2 + 1;

> It's normal behaviour, because after the first update it will be 2 same
> values for m2 and you don't want that since you have a unique constraint
> for that column. 

Please note: This is a bug in Postgresql, not "normal behaviour". From a
conceptual perspective, there is no "after the first update" - the
statement is supposed to be atomic.

Unfortunately, the problem is waiting for someone to get a great idea:

http://svr5.postgresql.org/pgsql-bugs/2007-02/msg00075.php


If you can't wait, you are probably better off working around the
problem. Standard solution is to do:

UPDATE  master SET m2 = -m2;
UPDATE  master SET m2 = -m2+1;

or something similar.

Nis


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Find "smallest common year"

[Stefan Schwarzer]

I really tried it out. I changed my whole database to the "id-country  
| year | value" - format. And then tried to build my SQL queries. But  
it was really, really difficult, and sometimes impossible (for me) to  
get what I wanted.


Actually, it remains quite difficult for me to remember the actual  
problems I had with it.


But, for example, I need to aggregate the national data on-the-fly to  
their regions. I need to calculate per Capita data on-the-fly for  
each variable. Although, one would say this should be simple to  
accomplish, me and a semi-professional database expert could hardly  
solve these things.


In one case we came up with as many sub-selects as years were  
available (or selected by the user) (that can be up to 60 years). Is  
this "efficient" SQL programming?


What would you recommend for say, 500 global national statistical  
variables, 500 regional and 500 subregional and 500 global  
aggregations? Years being covered having something between 10 and 60  
years for each of these variables. All available for 240 countries/ 
territories.


Thanks for any recommendations!

Stef




Stefan Schwarzer wrote:
Sorry,

I forgot to mention my table design, which is like this:

 name 20012002   2003   2004   2005
-
Afghanistan
Albania

(Yep, I know, bad table design :-)). I tried to change it to the  
more common "id | year | value" format, but there were too many SQL  
problems afterwards for the different queries/calculations we need to  
have)



May I suggest that you concentrate on solving *those* problems  
instead of

the programmatically trivial computation of lowest common value?  Notice
that a *really trivial* programming exercise becomes highly involved in
your case --- if I'm understanding correctly what you have, I assume  
you'd
have to check one by one the fields for NULL or non-NULL values ---  
that's
intolerably ugly, IMHO, and it is a very simple and direct  
consequence of

an as-unsound-as-it-gets db/table design.




 

  Stefan Schwarzer

  Lean Back and Relax - Enjoy some Nature Photography:
  http://photoblog.la-famille-schwarzer.de

  Appetite for Global Data? UNEP GEP Data Portal:
  http://geodata.grid.unep.ch
  

[GENERAL] Getting the search_path value for a query listed in pg_stat_activity output (feature request?)

[Aleksander Kmetec - INTERA]

Hi, all.

Is there a way to find out which search_path a query is using?

I have around 100 schemas, each of them containing the same set of tables. When the app connects it sets the search_path 
and then doesn't use the schema name anywhere again, which means that it's impossible to see which exact table is being 
used by a query just by looking at pg_stat_activity output.


It would also be nice if it was possible to have search_path added to logged 
statements.

Regards,
Aleksander.

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Find "smallest common year"

[Scott Marlowe]

On 9/27/07, Stefan Schwarzer <[EMAIL PROTECTED]> wrote:
>
> I really tried it out. I changed my whole database to the "id-country | year
> | value" - format. And then tried to build my SQL queries. But it was
> really, really difficult, and sometimes impossible (for me) to get what I
> wanted.
>
> Actually, it remains quite difficult for me to remember the actual problems
> I had with it.
>
> But, for example, I need to aggregate the national data on-the-fly to their
> regions. I need to calculate per Capita data on-the-fly for each variable.
> Although, one would say this should be simple to accomplish, me and a
> semi-professional database expert could hardly solve these things.

You should have brought your problem here.  You'd be surprised what a
fresh set of eyes can see.

> In one case we came up with as many sub-selects as years were available (or
> selected by the user) (that can be up to 60 years). Is this "efficient" SQL
> programming?

Probably not.  But that doesn't mean it was the right approach either.
 There well might have been a more efficient approach you didn't think
of.

> What would you recommend for say, 500 global national statistical variables,
> 500 regional and 500 subregional and 500 global aggregations? Years being
> covered having something between 10 and 60 years for each of these
> variables. All available for 240 countries/territories.

I generally approach such problems by putting the data right
(normalized) at the start, then munging the data into summary tables
to handle the problems you're seeing now.

I find it far easier to maintain normalized tables that produced
non-normalized ones (for things like data warehousing) than it is to
maintain non-normalized tables and trying to produce normalized data
from that.

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] DAGs and recursive queries

[Jeff Davis]

On Wed, 2007-09-26 at 16:54 +0100, Gregory Stark wrote:
> "paul.dorman" <[EMAIL PROTECTED]> writes:
> 
> > Hi everyone,
> >
> > I would like to know the best way to implement a DAG in PostgreSQL. I
> > understand there has been some talk of recursive queries, and I'm
> > wondering if there has been much progress on this.
> 
> The ANSI recursive queries didn't make it into 8.3. I still hope it makes 8.4.
> 
> You could check out the tablefunc contrib which includes a function called
> connectby() which implements a kind of recursive query.
> 
> Alternatively you might look at the ltree contrib module but that doesn't work
> the way you describe. It denormalizes the data for very fast but less flexible

Ltree seems like it might be a good option for him. What doesn't it do
that he needs?

I am also interested in graphs and trees in relational databases. Can
you recommend any good books? I particularly like CJ Date as an author,
but I can't find anything by him that specifically addresses this topic.

Also, how exactly is the database denormalized by using ltree?

Regards,
Jeff Davis


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] access privileges: grant select on (all current and future tables)?

[John Smith]

On 9/27/07, John Smith <[EMAIL PROTECTED]> wrote:
> how'd i "grant select on (all current and future tables inside a
> private schema) to username" without turning that user into superuser?
> "grant usage on..." doesn't do it.
>
> or do i, everytime i batch/auto create the tables, do a "grant select
> on (new table) to username"?

ok did some more googling. seems like "grant" is not the strongest
feature of postgresql yet.

for those who follow:
"grant select on schemaname.tablename..." alone works. gotto
explicitly mention tablename via trigger/script since schema privilege
is separate from table privilige.

and "grant usage on new tables in schema..." doesn't exist yet. which
leads to my next question (see
http://svr5.postgresql.org/pgsql-hackers/2005-01/msg01070.php)- are we
there yet?

also how'd i find access privileges for a schema. something like "\z
schemaname" not "\dp schemaname."?
cheers, jzs

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] pg_dump (8.1.9) does not output copy statements

[Matthew Dennis]

All of them

On 9/26/07, Tom Lane <[EMAIL PROTECTED]> wrote:
>
> Jan de Visser <[EMAIL PROTECTED]> writes:
> > In my world two identical pilot errors within a short timeframe are
> indicat=
> > ive=20
> > of a bad interface.
>
> Yeah, it's inconsistent.  How many people's dump scripts do you want to
> break to make it more consistent?
>
> regards, tom lane
>

[GENERAL] question about pg_dump -a

[Ottavio Campana]

I have a database which I create using dia and tedia2sql.

I developed another version with more tables, without changing anything
that was already present in the first version.

Now I want to copy the data from one database to another, so I thought
about pg_dump -a, assuming that since there is no change in the
structure and I can freely  and and reload the information.

My problem is that when I reload the data into the new database, I have
several error about foreign keys violation. For what I've been able to
understand, it seems to be a problem of loading order and tables
referring to others are loaded earlier than those.

Is there a way to export tables in order, so that dependencies are
always met? reading the manpage of pg_dump I found the -Fc flag, but I
haven't understood if it is good for me and how it works. Or is there a
way to relax constraints while loading data?



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] DAGs and recursive queries

[Gregory Stark]

"Jeff Davis" <[EMAIL PROTECTED]> writes:

> On Wed, 2007-09-26 at 16:54 +0100, Gregory Stark wrote:
>
>> You could check out the tablefunc contrib which includes a function called
>> connectby() which implements a kind of recursive query.
>> 
>> Alternatively you might look at the ltree contrib module but that doesn't 
>> work
>> the way you describe. It denormalizes the data for very fast but less 
>> flexible
>
> Ltree seems like it might be a good option for him. What doesn't it do
> that he needs?
...
> Also, how exactly is the database denormalized by using ltree?

It keeps the same information in more than one place. Consider:

1
1.1
1.1.1

Note that all three records contain the root's id of "1". If you want to
reparent 1.1 to be 2.1 you have to know that all its children also need to be
reparented as well.

That's what he said he wanted to be able to do. In general if you have a
relatively static hierarchy something like ltree works very well but if you
have a very dynamic hierarchy where nodes move around freely it's not a very
good fit.


-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[GENERAL] usage of indexes for inner joins

[Jan Theodore Galkowski]

I fear this has been asked many times about PostgreSQL, and I have read
the docs about how indexes are supposed to be defined and used, but I
don't understand why the engine and optimizer is doing what it does in
the simplest of situations.  Is it that its tuning is heavily data
dependent?

My case of interest is more complicated, but I decided to create a toy
case to try to understand.  Here it is:


  -- Table "foo" DDL

  CREATE TABLE "public"."foo"(

  "projectid" int4 NOT NULL ,

  "uid" int4 NOT NULL ,

  "name" varchar(254) NOT NULL ,

  "ver" varchar(127) NOT NULL ,

  "startdate" date NOT NULL ,

  "enddate" date NOT NULL ,

  "status" varchar(254) NOT NULL ,

  "percentdone" numeric(7,2) NOT NULL ,

  "championuid" int4 NOT NULL ,

  "pmuid" int4 NOT NULL ,

  PRIMARY KEY ("projectid")

  )  WITHOUT OIDS;


  -- Table "bignum" DDL

  CREATE TABLE "public"."bignum"(

  "thing" numeric(100) NOT NULL

  )  WITHOUT OIDS;

  CREATE INDEX "t" ON "public"."bignum" USING btree ("thing");


Running

EXPLAIN ANALYZE SELECT A.* FROM bignum  B, foo  A WHERE A.projectid
= B.thing;

yields:

Nested Loop  (cost=0.00..15.51 rows=1 width=407) (actual
time=0.041..0.041 rows=0 loops=1)

  Join Filter: ((a.projectid)::numeric = b.thing)  ->

Seq Scan on bignum b (cost=0.00..1.01 rows=1 width=16) (actual
time=0.024..0.027 rows=1 loops=1)  ->

Seq Scan on foo a  (cost=0.00..11.80 rows=180 width=407) (actual
time=0.003..0.003 rows=0 loops=1)

Total runtime: .169 ms ;

Like *how* *come*?  There are indexes on both columns of the join.  Is
it the NUMERIC datatype messing things up?  Unlikely, as I've seen the
same with INTEGERs.

If it is data dependent (these tables are presently empty), any
suggestions as to how to tune a database for unknown mixes of data?

This is run on the Windows version of PG, but I'm seeing the same kind
of thing on Linux.

Thanks.

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] usage of indexes for inner joins

[Tom Lane]

"Jan Theodore Galkowski" <[EMAIL PROTECTED]> writes:
> Total runtime: .169 ms ;

> Like *how* *come*?

You have a problem with 0.1 ms runtime?

But to correct your obvious misunderstanding: yes, the plan depends on
the table size, as well it should.

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq