Re: [SQL] UPDATE COMPATIBILITY

2012-01-17 Thread Thomas Kellerer 

Gera Mel Handumon, 17.01.2012 07:31:

What version of postgresql that the update compatibility below will be
implemented?

UPDATE COMPATIBILITY


UPDATE accounts SET (contact_last_name, contact_first_name) =
 (SELECT last_name, first_name FROM salesmen
  WHERE salesmen.id = accounts.sales_id);


None as far as I know.

You need to rewrite it to:

UPDATE accounts
  SET contact_last_name = s.last_name,
  contact_first_name = s.first_name
FROM salesmen s
WHERE s.id = accounts.sales_id



--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Query Problem... Left OuterJoin / Tagging Issue

2012-01-17 Thread John Tuliao 
Thank you so much for your prompt reply David. I will consider your 
advice and put it to mind and action. I hope you all don't get tired of 
helping!


For now, I will note down what I need to and do the necessary 
adjustments. Thank you for your time!


On Friday, 13 January, 2012 10:26 PM, David Johnston wrote:
On Jan 12, 2012, at 23:31, John Tuliao > wrote:



Hi,

I've been working on this for quite awhile now and don't seem to get 
the proper query.


I have basically 4 tables.

1. Table john_test contains the numbers of the calls.
2. Table john_country contains the country with prefix.
3. Table john_clients contains the clients and their sub_id's
(because a client usually has a subclient, [ex. highway-2, 
highway-3]) and their prefix.

4. Table john_test contains the phone numbers.



select * from john_client_rate limit 3;

   name   |country | cali | cana | callrate | dir_id | 
trans_id | svc_id | base | incr | client_id

--++--+--+--++--++--+--+---
 highway  | Afghanistan|  |  |   0.6212 |  0 | 
0||6 |6 | 4
 highway  | Albania|  |  |   0.3945 |  0 | 
1||6 |6 | 4
 highway  | Bahamas|  |  |   0.0513 |  0 | 
1||6 |6 | 4

(3 rows)

select * from john_country limit 3;

country| state |  prefix  | area_code
---+---+--+---
 Afghanistan   |   | 93   |
 Aland Islands |   | 35818120 |
 Albania   |   | 355  |
(3 rows)


select * from john_clients limit 3;

 id | client_id | sub_id | name | prefix  |  type
+---++--+-+
 80 |80 |  0 | highway  | 71081   | client
 80 |80 |  0 | highway  | 7107011 | client
 80 |80 |  0 | highway  | 71091   | client
(3 rows)

select * from john_test limit 3;

client_id |  name   |   phonenum   | calledphonenum  
| phonenumtranslat | direction | duration

--+-+--+-+--+---+--
2 | highway | 83863011351927330133 | 20100147011351927330133 
|  | outbound  |  363
2 | highway | 83863011441179218126 | 1943011441179218126 
|  | outbound  |   83
2 | highway | 83863011441179218126 | 20100147011441179218126 
|  | outbound  |   32

(3 rows)



What I want to do is to remove the prefix, and retain the number 
using the following query:


select
john_test.name,
john_test.gwrxdcdn,
john_test.duration as dur,
john_client_rate.name as name2,
john_client_rate.country,
john_country.prefix,
substring(john_test.gwrxdcdn from length(john_country.prefix)+1) as 
strip,
get_duration(john_test.duration::int,john_client_rate.base,john_client_rate.incr) 
as realdur

from john_test
left outer join john_client_rate
on (prefix in
   (
  select prefix from john_client_rate
  where john_test.phonenum ~ ( '^' || john_country.prefix)
  order by length(john_country.prefix) desc limit '1'
   )
   )
limit 20;


If you have a select within the ON clause of a join it isn't really a 
join.  ON clauses should be simple expressions (almost always 
equality) between fields on the two tables with AND/OR logic.






I have achieved this already, now I want to identify which country 
it's supposed to be for.
Problem is sometimes the "stripped" number that is retained shows: 
8661234567 or 8889876543
This would indicate that the call is already toll free without me 
being able to identify the country.

How can I get over this?

Further, I am planning to use multiple joins since I have several 
tables and so as to identify missing countries. On this questions 
which query is better?


Query 1:

Select table1.column,table2.column,table3.column from table1 left 
outer join table 2 on (table1.column=table2.column) left outer join 
table3 on (table2.column=table3.column) ;


or Query 2:

Select table1.column,table2.column,table3.column from 
table1,table2,table3 where [conditions] ;




Query 1 is an outer join, query 2 is an inner join; totally different 
semantics so the question is more "which one will work" versus "which 
one is better".  Do you at least understand the difference?


Ultimately, I want to run one query that will satisfy these things 
and help me insert into a table that will have it "TAGGED" properly 
with the righ

Re: [SQL] sql query problem

2012-01-17 Thread Alok Thakur 
Dear,

I am trying to provide you as much details as possible.

answer` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `question_id` int(10) NOT NULL,
  `user_id` int(10) NOT NULL,
  `answer` int(10) NOT NULL,  ->
  `status` tinyint(1) NOT NULL,  --> Status will be 0 or 1 means wrong
or right answer
  `date` datetime NOT NULL,
  PRIMARY KEY (`quiz_result_id`)
)

user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user` varchar(255) NOT NULL,
  `username` varchar(255) NOT NULL,
  `user_email` varchar(255) NOT NULL,
  `user_gender` varchar(255) NOT NULL,
  `refrence` varchar(255) NOT NULL,
  `join_date` varchar(255) NOT NULL,
  `status` tinyint(1) NOT NULL,
  `banned` tinyint(1) NOT NULL,
  PRIMARY KEY (`user_id`)
)

Now I want the report like this:

UserIDUserName   Attended(questions)   Wrong   Correct
1 A

On Jan 16, 3:49 am, misa.si...@gmail.com (Misa Simic) wrote:
> It seems question is not clear...
>
> I could not determine what should be in column Attended, and based on
> what should define passed/failed
>
> But quick tip would be
>
> SELECT name, CASE status WHEN 1 THEN 'Passed' ELSE 'Failed' END FROM
> UserTable INNER JOIN result ON UserTable.id = result.user_id
>
> Sent from my Windows Phone
> From: Alok Thakur
> Sent: 15/01/2012 22:08
> To: pgsql-...@postgresql.org
> Subject: [SQL] sql query problem
> Dear All,
>
> I have two tables one contains details of user and other contains
> result. The details are:
> 1. UserTable - id, name, phone
> 2. result - id, question_id, user_id, status (0 or 1)
>
> I want the list like this:
> User Id   Name   Attended   Failed   Passed
>
> but i could not find the way to do this.
>
> Please help
>
> --
> Sent via pgsql-sql mailing list (pgsql-...@postgresql.org)
> To make changes to your 
> subscription:http://www.postgresql.org/mailpref/pgsql-sql
>
> --
> Sent via pgsql-sql mailing list (pgsql-...@postgresql.org)
> To make changes to your 
> subscription:http://www.postgresql.org/mailpref/pgsql-sql


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Wrong query plan when using a left outer join

2012-01-17 Thread Filip Rembiałkowski 
On Tue, Jan 17, 2012 at 7:54 AM, Feike Steenbergen
 wrote:
> I have the following setup:
>
> A table called hand:
>
>
>                                        Table "stage.hand_meta"
>    Column     |           Type           |
> Modifiers
> ---+--+-
>  hand_id       | integer                  | not null default
> nextval('hand_meta_hand_id_seq'::regclass)
>  hand_no       | bigint                   | not null
>  site_id       | smallint                 | not null
>  game_id       | smallint                 | not null
>  time          | timestamp with time zone | not null
>  tournament_id | bigint                   |
> Indexes:
>    "hand_meta_pkey" PRIMARY KEY, btree (hand_id) CLUSTER
>    "hand_meta_hand_no_site_unq" UNIQUE, btree (hand_no, site_id)
>    "hand_meta_time_idx" btree ("time")
>    "hand_meta_tournament_id_idx" btree (tournament_id)
> Referenced by:
>    TABLE "handhistory_plain" CONSTRAINT
> "handhistory_plain_hand_id_fkey" FOREIGN KEY (hand_id) REFERENCES
> hand_meta(hand_id)
>    TABLE "handhistory_staged" CONSTRAINT "staged_hand_hand_id_fkey"
> FOREIGN KEY (hand_id) REFERENCES hand_meta(hand_id)
>
> Getting the max hand_id (primary key) results in using an index:
>
>
> feiketracker=> explain analyze select max(hand_id) from stage.hand;
>
>  QUERY PLAN
> ---
>  Result  (cost=0.03..0.04 rows=1 width=0) (actual time=0.379..0.383
> rows=1 loops=1)
>   InitPlan 1 (returns $0)
>     ->  Limit  (cost=0.00..0.03 rows=1 width=4) (actual
> time=0.337..0.340 rows=1 loops=1)
>           ->  Index Scan Backward using hand_meta_pkey on hand_meta
> (cost=0.00..82667.12 rows=2479440 width=4) (actual time=0.319..0.319
> rows=1 loops=1)
>                 Index Cond: (hand_id IS NOT NULL)
>  Total runtime: 0.823 ms
> (6 rows)
>
>
> Now, if i create a view which left outer joins another table and
> select max hand_id it uses a seq_scan, which I think it should'nt use,
> as it only needs to query hand_meta and then use the index:
>
>
> feiketracker=> create view seqscan_example as (select * from hand_meta
> left join handhistory_plain using(hand_id));
> CREATE VIEW
> Time: 72.736 ms
>
> feiketracker=> explain analyze select max(hand_id) from seqscan_example;
>                                                         QUERY PLAN
> -
>  Aggregate  (cost=49261.00..49261.01 rows=1 width=4) (actual
> time=34672.052..34672.054 rows=1 loops=1)
>   ->  Seq Scan on hand_meta  (cost=0.00..43062.40 rows=2479440
> width=4) (actual time=0.180..16725.109 rows=2479440 loops=1)
>  Total runtime: 34672.874 ms
> (3 rows)
>
>
> feiketracker=> select version();
>                                                              version
> 
>  PostgreSQL 9.0.6 on armv5tejl-unknown-linux-gnueabi, compiled by GCC
> gcc (GCC) 3.4.4 (release) (CodeSourcery ARM 2005q3-2), 32-bit
> (1 row)
>
>
> I cannot think of a reason to use a seqscan, the left join should
> indicate all results from hand_meta should be used, hand_id is the
> primary key, so selecting max(hand_id) from the table or the view
> should result in the same execution plan or am I thinking wrong?
>

it's not always so simple for the planner to eliminate left join...
imagine that the view on the right side of join has some side effects.

so postgres will never "cut off" the right join side. but postgres
will still try to choose best execution plan. seq scan may simply be
faster here. breaking point is somewhere near 50% selectivity.

when handhistory_plain starts geting much bigger, plan will change.

try to experiment with SET enable_seqscan TO false; - and see what happens.


BTW, add a foreign key and index on handhistory_plain.hand_id (unless
you have it already).
BTW2, if you really don't care on handhistory you can just use
original query with no join.


Filip

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] sql query problem

2012-01-17 Thread Andreas Kretschmer 
Alok Thakur  wrote:

> Dear,
> 
> I am trying to provide you as much details as possible.
> 
> answer` (
>   `id` int(10) NOT NULL AUTO_INCREMENT,
>   `question_id` int(10) NOT NULL,
>   `user_id` int(10) NOT NULL,
>   `answer` int(10) NOT NULL,  ->
>   `status` tinyint(1) NOT NULL,  --> Status will be 0 or 1 means wrong
> or right answer
>   `date` datetime NOT NULL,
>   PRIMARY KEY (`quiz_result_id`)
> )
> 
> user` (
>   `user_id` int(11) NOT NULL AUTO_INCREMENT,
>   `user` varchar(255) NOT NULL,
>   `username` varchar(255) NOT NULL,
>   `user_email` varchar(255) NOT NULL,
>   `user_gender` varchar(255) NOT NULL,
>   `refrence` varchar(255) NOT NULL,
>   `join_date` varchar(255) NOT NULL,
>   `status` tinyint(1) NOT NULL,
>   `banned` tinyint(1) NOT NULL,
>   PRIMARY KEY (`user_id`)
> )

That's MySQL (i guess), please join a mysql-list.



Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] sql query problem

2012-01-17 Thread David Johnston 
On Jan 17, 2012, at 8:35, Andreas Kretschmer  wrote:

> Alok Thakur  wrote:
> 
>> Dear,
>> 
>> I am trying to provide you as much details as possible.
>> 
>> answer` (
>>  `id` int(10) NOT NULL AUTO_INCREMENT,
>>  `question_id` int(10) NOT NULL,
>>  `user_id` int(10) NOT NULL,
>>  `answer` int(10) NOT NULL,  ->
>>  `status` tinyint(1) NOT NULL,  --> Status will be 0 or 1 means wrong
>> or right answer
>>  `date` datetime NOT NULL,
>>  PRIMARY KEY (`quiz_result_id`)
>> )
>> 
>> user` (
>>  `user_id` int(11) NOT NULL AUTO_INCREMENT,
>>  `user` varchar(255) NOT NULL,
>>  `username` varchar(255) NOT NULL,
>>  `user_email` varchar(255) NOT NULL,
>>  `user_gender` varchar(255) NOT NULL,
>>  `refrence` varchar(255) NOT NULL,
>>  `join_date` varchar(255) NOT NULL,
>>  `status` tinyint(1) NOT NULL,
>>  `banned` tinyint(1) NOT NULL,
>>  PRIMARY KEY (`user_id`)
>> )
> 
> That's MySQL (i guess), please join a mysql-list.
> 
> 

DB aside the query you are looking for is very simple SQL.  The only real trick 
is using

SUM(case when status = 1/0 then 1 else 0 end 

to obtain the proper counts.  

Any reference materials covering table joining and group by will give you the 
syntax and examples needed to write your query.

The lack of response is because most people are not going to bother answering 
very simple queries that beginner reference materials cover adequately.  Plus, 
you didn't display any effort in attempting to solve the question yourself; you 
can do this by showing and and asking what you did wrong as opposed to simply 
asking for an answer.

David J.
-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] UPDATE COMPATIBILITY

2012-01-17 Thread Adrian Klaver 
On Tuesday, January 17, 2012 12:49:44 am Thomas Kellerer wrote:
> Gera Mel Handumon, 17.01.2012 07:31:

> 
> None as far as I know.
> 
> You need to rewrite it to:
> 
> UPDATE accounts
>SET contact_last_name = s.last_name,
>contact_first_name = s.first_name
> FROM salesmen s
> WHERE s.id = accounts.sales_id

For completeness, you could also do:

 UPDATE accounts
SET (contact_last_name,contact_first_name)= 
(s.last_name,s.first_name)
 FROM salesmen s
 WHERE s.id = accounts.sales_id

Gets you a little closer to what you want:)

-- 
Adrian Klaver
adrian.kla...@gmail.com

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] UPDATE COMPATIBILITY

2012-01-17 Thread Thomas Kellerer 

Adrian Klaver, 17.01.2012 16:19:

You need to rewrite it to:

UPDATE accounts
SET contact_last_name = s.last_name,
contact_first_name = s.first_name
FROM salesmen s
WHERE s.id = accounts.sales_id


For completeness, you could also do:

  UPDATE accounts
 SET (contact_last_name,contact_first_name)=
(s.last_name,s.first_name)
  FROM salesmen s
  WHERE s.id = accounts.sales_id



Nice one!



--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Wrong query plan when using a left outer join

2012-01-17 Thread Feike Steenbergen 
> BTW, add a foreign key and index on handhistory_plain.hand_id (unless> you 
> have it already).
It's there already:

feiketracker=# \d+ handhistory_plain;
Table "stage.handhistory_plain"
 Column  |  Type   | Modifiers | Storage  | Description
-+-+---+--+-
 hand_id | integer | not null  | plain|
 history | text| not null  | extended |
Indexes:
"handhistory_plain_pkey" PRIMARY KEY, btree (hand_id) CLUSTER
Foreign-key constraints:
"handhistory_plain_hand_id_fkey" FOREIGN KEY (hand_id) REFERENCES
hand_meta(hand_id)

> BTW2, if you really don't care on handhistory you can just use
> original query with no join.

Well, sometimes I do, sometimes I don't. For easier application access
I wanted to create a view that joins both these tables together:
easier application design and better performance, as the analyzer
should know best when not to use the handhistory_plain table.


The design is as follows:

hand_meta - holds all metadata for a pokerhand
handhistory_plain holds the history for a pokerhand

hand_meta is going to be used the most, it is around 165 bytes per tuple
handhistory_plain is not going to be used often (it is there as a
reference); it is around 5000 bytes per tuple.

They both hold the same column as primary key, handhistory_plain holds
a fraction of the tuples of hand_meta, the split was only made to make
sure the processed data (hand_meta) is smaller in size and should
therefore require less I/O and thus increase performance.

I'm not sure what to make of:
> imagine that the view on the right side of join has some side effects.
I can see some side effects may occur, but as it is a left join, the
left hand side will always be part of the returning set (there is no
where clause), so the index should be used.
Even though I don't understand, you seem to be right, a natural join
is 30 times faster:

feiketracker=# explain analyze select max(hand_id) from hand_meta left
join handhistory_plain using(hand_id);
 QUERY
PLAN
-
 Aggregate  (cost=1049261.00..1049261.01 rows=1 width=4)
(actual time=31179.238..31179.241 rows=1 loops=1)
   ->  Seq Scan on hand_meta  (cost=100.00..1043062.40
rows=2479440 width=4) (actual time=0.131..16039.886 rows=2479440
loops=1)
 Total runtime: 31179.725 ms
(3 rows)

Time: 31185.088 ms

feiketracker=# explain analyze select max(hand_id) from hand_meta join
handhistory_plain using(hand_id);

 QUERY PLAN
--
 Aggregate  (cost=53043.61..53043.62 rows=1 width=4) (actual
time=962.242..962.245 rows=1 loops=1)
   ->  Nested Loop  (cost=0.00..53029.93 rows=5470 width=4) (actual
time=0.400..920.582 rows=5470 loops=1)
 ->  Index Scan using handhistory_plain_pkey on
handhistory_plain  (cost=0.00..14494.27 rows=5470 width=4) (actual
time=0.215..101.177 rows=5470 loops=1)
 ->  Index Scan using hand_meta_pkey on hand_meta
(cost=0.00..7.03 rows=1 width=4) (actual time=0.100..0.115 rows=1
loops=5470)
   Index Cond: (hand_meta.hand_id = handhistory_plain.hand_id)
 Total runtime: 962.968 ms


> try to experiment with SET enable_seqscan TO false; - and see what happens.
Didn't make a difference; therefore I think postgres determines it is
unable to use the index, is that correct?


Thank you for now: I'll use the inner join (or natural join in this
case) for this specific view

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Wrong query plan when using a left outer join

2012-01-17 Thread Feike Steenbergen 
oops, but ofcourse, a natural view will not give the correct answer,
back to the drawing board ...

On Tue, Jan 17, 2012 at 19:53, Feike Steenbergen
 wrote:
>> BTW, add a foreign key and index on handhistory_plain.hand_id (unless> you 
>> have it already).
> It's there already:
>
> feiketracker=# \d+ handhistory_plain;
>            Table "stage.handhistory_plain"
>  Column  |  Type   | Modifiers | Storage  | Description
> -+-+---+--+-
>  hand_id | integer | not null  | plain    |
>  history | text    | not null  | extended |
> Indexes:
>    "handhistory_plain_pkey" PRIMARY KEY, btree (hand_id) CLUSTER
> Foreign-key constraints:
>    "handhistory_plain_hand_id_fkey" FOREIGN KEY (hand_id) REFERENCES
> hand_meta(hand_id)
>
>> BTW2, if you really don't care on handhistory you can just use
>> original query with no join.
>
> Well, sometimes I do, sometimes I don't. For easier application access
> I wanted to create a view that joins both these tables together:
> easier application design and better performance, as the analyzer
> should know best when not to use the handhistory_plain table.
>
>
> The design is as follows:
>
> hand_meta - holds all metadata for a pokerhand
> handhistory_plain holds the history for a pokerhand
>
> hand_meta is going to be used the most, it is around 165 bytes per tuple
> handhistory_plain is not going to be used often (it is there as a
> reference); it is around 5000 bytes per tuple.
>
> They both hold the same column as primary key, handhistory_plain holds
> a fraction of the tuples of hand_meta, the split was only made to make
> sure the processed data (hand_meta) is smaller in size and should
> therefore require less I/O and thus increase performance.
>
> I'm not sure what to make of:
>> imagine that the view on the right side of join has some side effects.
> I can see some side effects may occur, but as it is a left join, the
> left hand side will always be part of the returning set (there is no
> where clause), so the index should be used.
> Even though I don't understand, you seem to be right, a natural join
> is 30 times faster:
>
> feiketracker=# explain analyze select max(hand_id) from hand_meta left
> join handhistory_plain using(hand_id);
>                                                                 QUERY
> PLAN
> -
>  Aggregate  (cost=1049261.00..1049261.01 rows=1 width=4)
> (actual time=31179.238..31179.241 rows=1 loops=1)
>   ->  Seq Scan on hand_meta  (cost=100.00..1043062.40
> rows=2479440 width=4) (actual time=0.131..16039.886 rows=2479440
> loops=1)
>  Total runtime: 31179.725 ms
> (3 rows)
>
> Time: 31185.088 ms
>
> feiketracker=# explain analyze select max(hand_id) from hand_meta join
> handhistory_plain using(hand_id);
>
>     QUERY PLAN
> --
>  Aggregate  (cost=53043.61..53043.62 rows=1 width=4) (actual
> time=962.242..962.245 rows=1 loops=1)
>   ->  Nested Loop  (cost=0.00..53029.93 rows=5470 width=4) (actual
> time=0.400..920.582 rows=5470 loops=1)
>         ->  Index Scan using handhistory_plain_pkey on
> handhistory_plain  (cost=0.00..14494.27 rows=5470 width=4) (actual
> time=0.215..101.177 rows=5470 loops=1)
>         ->  Index Scan using hand_meta_pkey on hand_meta
> (cost=0.00..7.03 rows=1 width=4) (actual time=0.100..0.115 rows=1
> loops=5470)
>               Index Cond: (hand_meta.hand_id = handhistory_plain.hand_id)
>  Total runtime: 962.968 ms
>
>
>> try to experiment with SET enable_seqscan TO false; - and see what happens.
> Didn't make a difference; therefore I think postgres determines it is
> unable to use the index, is that correct?
>
>
> Thank you for now: I'll use the inner join (or natural join in this
> case) for this specific view

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Wrong query plan when using a left outer join

2012-01-17 Thread Rosser Schwarz 
2012/1/17 Filip Rembiałkowski :

> postgres will still try to choose best execution plan. seq scan may simply be
> faster here. breaking point is somewhere near 50% selectivity.

The tipping point is usually far lower than that; in fact, it's more
often around 10%.  Random IO is *very* expensive, as compared to
sequential IO (at least on spinning rust; SSDs are a different matter,
of course).  It's usually vastly cheaper to read in an entire table
and filter the rows you want than to seek left and right (and then
left, left, and right again) to cherry-pick the pages you need.

rls

-- 
:wq

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] UPDATE COMPATIBILITY

2012-01-17 Thread Samuel Gendler 
On Tue, Jan 17, 2012 at 12:49 AM, Thomas Kellerer wrote:

> Gera Mel Handumon, 17.01.2012 07:31:
>
>  What version of postgresql that the update compatibility below will be
>> implemented?
>>
>> UPDATE COMPATIBILITY
>>
>>
>> UPDATE accounts SET (contact_last_name, contact_first_name) =
>> (SELECT last_name, first_name FROM salesmen
>>  WHERE salesmen.id = accounts.sales_id);
>>
>
> None as far as I know.
>
> You need to rewrite it to:
>
> UPDATE accounts
>  SET contact_last_name = s.last_name,
>  contact_first_name = s.first_name
> FROM salesmen s
> WHERE s.id = accounts.sales_id
>
>
You can put a query in the from clause instead of a table, I believe, too:

UPDATE accounts
SET contact_last_name = q.last_name,
contact_first_name = q.first_name
FROM (select last_name, first_name from salesmen where s.id =
accounts.sales_id)

Not any more functional than the previous example, in this case, but very
useful if you need a complicated join or aggregation/grouping.

Some useful info from the postgresql documentation on the UPDATE statement (
http://www.postgresql.org/docs/9.0/static/sql-update.html ):

"When a FROM clause is present, what essentially happens is that the target
table is joined to the tables mentioned in the fromlist, and each output
row of the join represents an update operation for the target table. When
using FROM you should ensure that the join produces at most one output row
for each row to be modified. In other words, a target row shouldn't join to
more than one row from the other table(s). If it does, then only one of the
join rows will be used to update the target row, but which one will be used
is not readily predictable.

Because of this indeterminacy, referencing other tables only within
sub-selects is safer, though often harder to read and slower than using a
join."