[SQL] How to speed up the database query?

[Abdul Wahab Dahalan]

Hi everyone!

I'm looking for solution to speed up the database query, means that to get 
resultset as quicker as we can.

For example if I've 700 records in the table it will take longer time compared 
if I've only 20 records. How do we speed up the query?. Any query technique 
that can be applied?.

Thus wild card query like : select * from tableA will cause query time 
increased compare to say select a,b from tableA.

any help, prettymuch appreciated.


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

[SQL] Complex Query - Data from 3 tables simultaneously

[Amit_Wadhwa]

All,


 
Using Postgres 8.0 on Windows Server 2003 - 16GB Ram, 
3Ghz X 2 Xeons
Accessing through JDBC / JSP
 
I have 3 shipment tables.
Table A - Records arrived 
Shipments.
Table B - Records Materials (maybe more than one per 
shipment) in the shipment.
Table C - Records Issuances of material (maybe more 
than one Issuance per line item of material) in Table B.
 
eg. 
Table A (PK = Shipment ID)
---
shipment 
ID    Recd 
Date
12005-XX-XX
10  2005-XX-XX
 
 
Table B (PK = Material ID, FK = Shipment ID, references 
Table A (shipment ID))
-
shipment 
ID 
Material ID Material Bond Date
10    
1  2005-XX-XX
10    
2  2005-XX-XX
 
Table C (PK = Issue ID, FK = Material ID, references 
Table B (Material ID))

Material 
ID    Issue 
ID    Issue 
Date

1  
1  2005-05-XX
1  2  2005-05-XX
 
I want to get data (under criteria of recvd date in 
table A) the following records:
 
shipment 
ID    Recd 
Date    
MaterialID    Bond 
Date    Issue 
ID    Issue Date
 
1    
2005-XX-XX   -    
-    
-    
-
10  2005-XX-XX   
1 2005-XX-XX   1    
2005-05-XX

10  2005-XX-XX   
1 2005-XX-XX   2    
2005-05-XX
10  2005-XX-XX   2 2005-XX-XX   -    
-
 
 
Basically I want a raw dump of data
- Should have all the shipments regardless of whether 
they have any material items entered or not
- Should have all Material Items for Every Shipment 
regardless of whether it was issued or not.
 
I know I need an outer join (Do I Not?), but am 
confused as to how to implement it.
Because this seems to be a requirement of a reversed 
outer join (??)
 
Please assist, 
Thanks in advance.
 
 

Re: [SQL] How to speed up the database query?

[Andreas Kretschmer]

Abdul Wahab Dahalan <[EMAIL PROTECTED]> schrieb:

> Hi everyone!
> 
> I'm looking for solution to speed up the database query, means that to
> get resultset as quicker as we can.

09:41 < akretschmer> ??tuning
09:41 < rtfm_please> For information about tuning
09:41 < rtfm_please> see http://www.powerpostgresql.com
09:41 < rtfm_please> or http://www.powerpostgresql.com/PerfList
09:41 < rtfm_please> or http://www.varlena.com/varlena/GeneralBits/116.php


> 
> For example if I've 700 records in the table it will take longer time
> compared if I've only 20 records. How do we speed up the query?. Any
> query technique that can be applied?.

Read the links above. You should use Indexe for searching. And you
should use 'explain select ...' to see how the planer works.

09:43 < akretschmer> ??explain
09:43 < rtfm_please> For information about explain
09:43 < rtfm_please> see 
http://techdocs.postgresql.org/oscon2005/robert.treat/OSCON_Explaining_Explain_Public.sxi
09:43 < rtfm_please> or http://www.gtsm.com/oscon2003/toc.html
09:43 < rtfm_please> or 
http://www.postgresql.org/docs/current/static/sql-explain.html


Regards, Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

---(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: [SQL] How to speed up the database query?

[Christian Paul B. Cosinas]

Of course as long as your table increases its records, the longer will take
your query.

I think what you want is to minimize the run time even though you have large
tables. You should fine tune your database server (which I am still looking
for the best configuration for my server haha). And get the most logical
queries. Avoid unnecessary queries.

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Abdul Wahab Dahalan
Sent: Thursday, October 27, 2005 7:04 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] How to speed up the database query?

Hi everyone!

I'm looking for solution to speed up the database query, means that to get
resultset as quicker as we can.

For example if I've 700 records in the table it will take longer time
compared if I've only 20 records. How do we speed up the query?. Any query
technique that can be applied?.

Thus wild card query like : select * from tableA will cause query time
increased compare to say select a,b from tableA.

any help, prettymuch appreciated.


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


I choose Polesoft Lockspam to fight spam, and you?
http://www.polesoft.com/refer.html


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

   http://archives.postgresql.org

Re: [SQL] How to speed up the database query?

[Amit_Wadhwa]

Run an analyse on your query, create your indexes according to that, and
you have a better performing query. 
Getting a resultset display faster will depend on your driver/RAM/client

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Abdul Wahab Dahalan
Sent: Thursday, October 27, 2005 12:34 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] How to speed up the database query?

Hi everyone!

I'm looking for solution to speed up the database query, means that to
get resultset as quicker as we can.

For example if I've 700 records in the table it will take longer time
compared if I've only 20 records. How do we speed up the query?. Any
query technique that can be applied?.

Thus wild card query like : select * from tableA will cause query time
increased compare to say select a,b from tableA.

any help, prettymuch appreciated.


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


---(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: [SQL] why vacuum

[Mario Splivalo]

On Wed, 2005-10-26 at 10:19 -0500, Scott Marlowe wrote:

> But, the next time someone says that slony is a toy add on, and MySQL
> has REAL replication, point them to THIS page on the same blog:
> 
> http://ebergen.net/wordpress/?p=70
> 
> In short, it basically shows that MySQL replication is incredibly
> fragile, and not fit for production on any real system.  The lack of
> system wide transaction support, like postgresql has, makes the problem
> he outlines that much worse.
> 
> The hoops people will jump through to use their favorite toys...

I see no point in blatantly putting 'other' products such shape. Pgsql
offers no replication at all, you need to use slony (wich is also a poor
replacement for a wannabe replication), or some other commercial
products. What about 2PC? What about linking the databases from
different servers?

I've been using MSSQL for over 6 years now, started with MSSQL7 and went
on with MSSQL2000. The replication it offers is superb! It runs
smoothly, you have click-me-click interface with wich you can create
publications and deploy them to subscribers with ease. Ok, there are
gotchas (and they-re ms-style funny), but it's all well documented, and
works most of the time as expected. So what?

Btw, I 'ported' the merge replication from MSSQL to postgres. It
basicaly adds triggers to every table that is 'published' for
replication. There is a separate table to store and calculate the change
differences from several servers (so you could do update on any of the
servers and change will be propagated to the others). I'm missing 2PC
badly here, I wrote some stupid python 'thingie' wich should act as 2PC
serializer, but that's slow as hell. And triggers slow down postgres
quite a bit.

So, to end this 'my father has bigger car than yours' debate, when will
postgres have two phase commit protocol implemented? I presume that
should come after you allow something like SELECT someCol FROM
myServer.myDatabase[Schema].myTable...

Mike
-- 
Mario Splivalo
Mob-Art
[EMAIL PROTECTED]

"I can do it quick, I can do it cheap, I can do it well. Pick any two."



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

   http://archives.postgresql.org

Re: [SQL] why vacuum

[Mario Splivalo]

On Wed, 2005-10-26 at 12:09 -0400, Jan Wieck wrote:
> > 
> 
> You must have missed the FAQ and other side notes about replication in 
> the MySQL manual. Essentially MySQL replication is nothing but a query 
> duplicating system, with the added sugar of taking care of now() and 
> some other non-deterministic things, but not all of them.
> 
> Non-deterministic user defined procedures, functions and triggers will 
> simply blow MySQL's sophisticated replication apart.
> 

That is just not true. I haven't tried MySQL 4 and above, but in 3.5.x
replication wos working as expected, and the load that replication posed
was insignificant. The only TRUE problem was that replication was
unidirectional. That SAME problem has Slony, and other 'replication
systems' available for postgres.

Mike
-- 
Mario Splivalo
Mob-Art
[EMAIL PROTECTED]

"I can do it quick, I can do it cheap, I can do it well. Pick any two."



---(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: [SQL] handling money type

[Richard Huxton]

padmanabha konkodi wrote:

hello developers,

i have facing one major problem handling sql money dataType in the
java

i have tried many permutation and combination but still i dint got
correct data type to use in java to pass money data


Have you tried PG's "numeric" type? What problems did you encounter with it?

--
  Richard Huxton
  Archonet Ltd

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

Re: [SQL] Complex Query - Data from 3 tables simultaneously

[Richard Huxton]

[EMAIL PROTECTED] wrote:
 
Basically I want a raw dump of data

- Should have all the shipments regardless of whether they have any
material items entered or not
- Should have all Material Items for Every Shipment regardless of
whether it was issued or not.
 
I know I need an outer join (Do I Not?), but am confused as to how to

implement it.
Because this seems to be a requirement of a reversed outer join (??)


SELECT s.*, m.*, i.*
FROM
  shipments s
LEFT JOIN
  materials m
ON s.www = m.xxx
LEFT JOIN
  issued i
ON m.yyy = i.zzz
WHERE
 s.whatever = something

--
  Richard Huxton
  Archonet Ltd

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

Re: [SQL] why vacuum

[Andrew Sullivan]

On Thu, Oct 27, 2005 at 10:22:41AM +0200, Mario Splivalo wrote:
> offers no replication at all, you need to use slony (wich is also a poor
> replacement for a wannabe replication), or some other commercial
> products. What about 2PC? What about linking the databases from

Slony is in fact a community-supported system; so I don't know why
you think that amounts to "no replication at all".  And since this is
a community-supported system, it'd be nice if you said why it's a
"poor replacement for wannabe replication".  What's wrong with it?

> Btw, I 'ported' the merge replication from MSSQL to postgres. It
> basicaly adds triggers to every table that is 'published' for
> replication. There is a separate table to store and calculate the change
> differences from several servers (so you could do update on any of the
> servers and change will be propagated to the others). I'm missing 2PC
> badly here, I wrote some stupid python 'thingie' wich should act as 2PC
> serializer, but that's slow as hell. And triggers slow down postgres
> quite a bit.

This is interesting.  Care to package it up for others, or write a
proof-of-concept outline for the lists or General Bits or something
like that?  This is a different sort of replication people are asking
for.  Note that you get 2PC in the next Postgres release.

A


-- 
Andrew Sullivan  | [EMAIL PROTECTED]
When my information changes, I alter my conclusions.  What do you do sir?
--attr. John Maynard Keynes

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

   http://archives.postgresql.org

Re: [SQL] Complex Query - Data from 3 tables simultaneously

[Amit_Wadhwa]

Thanks a lot, that worked for me! 

-Original Message-
From: Richard Huxton [mailto:[EMAIL PROTECTED] 
Sent: Thursday, October 27, 2005 2:47 PM
To: Wadhwa, Amit
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Complex Query - Data from 3 tables simultaneously

[EMAIL PROTECTED] wrote:
>  
> Basically I want a raw dump of data
> - Should have all the shipments regardless of whether they have any 
> material items entered or not
> - Should have all Material Items for Every Shipment regardless of 
> whether it was issued or not.
>  
> I know I need an outer join (Do I Not?), but am confused as to how to 
> implement it.
> Because this seems to be a requirement of a reversed outer join (??)

SELECT s.*, m.*, i.*
FROM
   shipments s
LEFT JOIN
   materials m
ON s.www = m.xxx
LEFT JOIN
   issued i
ON m.yyy = i.zzz
WHERE
  s.whatever = something

-- 
   Richard Huxton
   Archonet Ltd


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

Re: [SQL] why vacuum

[Mario Splivalo]

On Thu, 2005-10-27 at 06:21 -0400, Andrew Sullivan wrote:
> On Thu, Oct 27, 2005 at 10:22:41AM +0200, Mario Splivalo wrote:
> > offers no replication at all, you need to use slony (wich is also a poor
> > replacement for a wannabe replication), or some other commercial
> > products. What about 2PC? What about linking the databases from
> 
> Slony is in fact a community-supported system; so I don't know why
> you think that amounts to "no replication at all".  And since this is
> a community-supported system, it'd be nice if you said why it's a
> "poor replacement for wannabe replication".  What's wrong with it?

Postgres itself offers no replication. You could achive some sort of
replication by restoring the parts of WAL files, but that's rather
inconvinient. Then, if you want to replicate your data in any way, you
need to take slony, or whatever is existant out there, commercial or
open-free-source.
I appologize here if I insulted the slony developers, I ment nothing
like that :) I am sorry. I was just addressing the issue where
replication to one means 'just move my data here', and at others it
means 'merge my data'.

> > Btw, I 'ported' the merge replication from MSSQL to postgres. It
> > basicaly adds triggers to every table that is 'published' for
> > replication. There is a separate table to store and calculate the change
> > differences from several servers (so you could do update on any of the
> > servers and change will be propagated to the others). I'm missing 2PC
> > badly here, I wrote some stupid python 'thingie' wich should act as 2PC
> > serializer, but that's slow as hell. And triggers slow down postgres
> > quite a bit.
> 
> This is interesting.  Care to package it up for others, or write a
> proof-of-concept outline for the lists or General Bits or something
> like that?  This is a different sort of replication people are asking
> for.  Note that you get 2PC in the next Postgres release.

I'll be glad to, I'm just not that familiar (in fact, i'm not familiar
at all) with the 'proof-of-cocept' or 'General Bits' terms, so if you
could http-redirect me, I'll be  most thankfull.

Mentioning the 2PC, is it available in pg8.1beta4?

Mike
-- 
Mario Splivalo
Mob-Art
[EMAIL PROTECTED]

"I can do it quick, I can do it cheap, I can do it well. Pick any two."



---(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: [SQL] why vacuum

[Richard Huxton]

Mario Splivalo wrote:

On Wed, 2005-10-26 at 12:09 -0400, Jan Wieck wrote:

You must have missed the FAQ and other side notes about replication in 
the MySQL manual. Essentially MySQL replication is nothing but a query 
duplicating system, with the added sugar of taking care of now() and 
some other non-deterministic things, but not all of them.


Non-deterministic user defined procedures, functions and triggers will 
simply blow MySQL's sophisticated replication apart.


That is just not true. I haven't tried MySQL 4 and above, but in 3.5.x
replication wos working as expected, and the load that replication posed
was insignificant. The only TRUE problem was that replication was
unidirectional. That SAME problem has Slony, and other 'replication
systems' available for postgres.


I think you're missing the "non-deterministic" bit. Mysql replication is 
based on shipping statements AFAICT


http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
"The binary log contains all statements which updated data or 
potentially could have updated it (for example, a DELETE which matched 
no rows). Statements are stored in the form of “events” that describe 
the modifications."


So - if your statement contains something non-deterministic that isn't 
catered for in Mysql's code then it will break.


At it's simplest - if I write a function my_random() and then do:
  UPDATE foo SET a=1 WHERE b < my_random();
IF my_random() returns different results on different machines, then the 
replication will be broken. See the manual entry below:

  http://dev.mysql.com/doc/refman/5.0/en/replication-features.html

That's not to say the system is worthless - it works fine for many 
people. But it does have limitations.


--
  Richard Huxton
  Archonet Ltd


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

  http://archives.postgresql.org

Re: [SQL] why vacuum

[Jan Wieck]

On 10/27/2005 4:22 AM, Mario Splivalo wrote:


On Wed, 2005-10-26 at 12:09 -0400, Jan Wieck wrote:
> 

You must have missed the FAQ and other side notes about replication in 
the MySQL manual. Essentially MySQL replication is nothing but a query 
duplicating system, with the added sugar of taking care of now() and 
some other non-deterministic things, but not all of them.


Non-deterministic user defined procedures, functions and triggers will 
simply blow MySQL's sophisticated replication apart.




That is just not true. I haven't tried MySQL 4 and above, but in 3.5.x
replication wos working as expected, and the load that replication posed
was insignificant. The only TRUE problem was that replication was
unidirectional. That SAME problem has Slony, and other 'replication
systems' available for postgres.


Read again. "Non-deterministic user defined procedures ...". Please give
me *ONE* example of a user defined procedure in 3.5.x at all. You must
have missed the point that stored procedures are a new feature in 5.0.


Jan

--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #

---(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: [SQL] why vacuum

[Jan Wieck]

On 10/27/2005 4:22 AM, Mario Splivalo wrote:


I see no point in blatantly putting 'other' products such shape. Pgsql
offers no replication at all, you need to use slony (wich is also a poor
replacement for a wannabe replication), or some other commercial
products. What about 2PC? What about linking the databases from
different servers?


I agree that Scott's comment was a bit harsh. But would you please add a 
few details that explain what makes Slony a "poor replacement" in your 
opinion? And please don't repeat that stupid "not builtin". Any add-on 
is as good as its reliability and features. Or would you at the same 
time say that MySQL has only a poor replacement for wannabe transactions 
and foreign keys, because their storage engines are in fact add-ons?



Btw, I 'ported' the merge replication from MSSQL to postgres. It
basicaly adds triggers to every table that is 'published' for
replication. There is a separate table to store and calculate the change
differences from several servers (so you could do update on any of the
servers and change will be propagated to the others). I'm missing 2PC
badly here, I wrote some stupid python 'thingie' wich should act as 2PC
serializer, but that's slow as hell. And triggers slow down postgres
quite a bit.


Would you consider publishing that code under the BSD license? It sounds 
very much like one of the "other add-on replication systems" our users 
keep asking for. If you can't publish the code, do you have any design 
papers or a technical concept that could be used as a base for a new 
PostgreSQL community project?



Jan

--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #

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

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

[SQL] unsuscribe

[Ing. Jhon Carrillo-Venezuela]

unsuscribe


---(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: [SQL] handling money type

[codeWarrior]

The SQL data type "money" in postgreSQL was deprecated several versions 
ago... however -- it is still available in the system. The definitions is:

CREATE TYPE money
   (INPUT=cash_in, OUTPUT=cash_out, DEFAULT='',
   INTERNALLENGTH=4, ALIGNMENT=int4, STORAGE=PLAIN);
ALTER TYPE money OWNER TO postgres;
COMMENT ON TYPE money IS 'monetary amounts, $d,ddd.cc';

8.2. Monetary Types
Note
The money type is deprecated. Use numeric or decimal instead, in combination 
with the to_char function.
The money type stores a currency amount with a fixed fractional precision; 
see Table 8.3, "Monetary Types". Input is accepted in a variety of formats, 
including integer and floating-point literals, as well as "typical" currency 
formatting, such as '$1,000.00'. Output is generally in the latter form but 
depends on the locale.
When you "SELECT money('1000');"

the type cast returns: $1,000.00


HTH...

""padmanabha konkodi"" <[EMAIL PROTECTED]> wrote in message 
news:[EMAIL PROTECTED]
hello developers,

i have facing one major problem handling sql money dataType in the java

i have tried many permutation and combination but still i dint got correct 
data type to use in java to pass money data



 



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

   http://archives.postgresql.org

Re: [SQL] why vacuum

[Andrew Sullivan]

On Thu, Oct 27, 2005 at 02:34:13PM +0200, Mario Splivalo wrote:
> 
> Postgres itself offers no replication. You could achive some sort of
> replication by restoring the parts of WAL files, but that's rather
> inconvinient. Then, if you want to replicate your data in any way, you

Well, AFAIK Oracle itself offers no replication, either.  If you want
it, you have to buy a license for it.  Which means it's an add-on. 
Heck, most Linux distributions' kernels don't offer support for
network cards: they're an add-on.  We call them modules.  (In case
it's not clear, I don't buy the "itself/add-on" distinction.  The
point is that the whole system works together.  PostgreSQL most
definitely offers replication.  In fact, you can get warm-standby with
WAL shipping, or read-only capabilities with Slony or some other
tools.)

> like that :) I am sorry. I was just addressing the issue where
> replication to one means 'just move my data here', and at others it
> means 'merge my data'.

Yes: this multiple-meaning "replication" word causes a great deal of
confusion.  But just because one person has need A does not mean that
need B isn't a real one.  My employer, Afilias, sponsors the Slony
work, in the direction of our needs.  We did not initially have a
many-write-nodes scenario in mind, and we had a pressing need for a
"single master" system.  So that's the itch we scratched.

> I'll be glad to, I'm just not that familiar (in fact, i'm not familiar
> at all) with the 'proof-of-cocept' or 'General Bits' terms, so if you
> could http-redirect me, I'll be  most thankfull.

Proof of concept is just a description of what you did, how it
worked, design and limitations, &c.  Post it to the lists (uh,
-general or maybe -hackers, I suppose), or put it on a web page or
whatever.  General Bits is a fairly regular column that Elein Mustain
puts out.  I bet she'd include a submission on this topic, although
you'd have to ask her.  You can find GB at
.

> Mentioning the 2PC, is it available in pg8.1beta4?

Should be.

A
-- 
Andrew Sullivan  | [EMAIL PROTECTED]
The whole tendency of modern prose is away from concreteness.
--George Orwell

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

Re: [SQL] How to speed up the database query?

[Bruno Wolff III]

On Thu, Oct 27, 2005 at 15:03:36 +0800,
  Abdul Wahab Dahalan <[EMAIL PROTECTED]> wrote:
> Hi everyone!
> 
> I'm looking for solution to speed up the database query, means that to get 
> resultset as quicker as we can.
> 
> For example if I've 700 records in the table it will take longer time 
> compared if I've only 20 records. How do we speed up the query?. Any query 
> technique that can be applied?.
> 
> Thus wild card query like : select * from tableA will cause query time 
> increased compare to say select a,b from tableA.
> 
> any help, prettymuch appreciated.

Why don't you run explain analyze on the real query you are trying to speed up
and show us the output along with the query and relevant table and view
definitions.

Yes, specifying fewer columns will probably significantly speed things up,
as you will be transmitting less data over the network. There aren't magic
ways to speed up queries return every row in a table.

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

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

Re: [SQL] why vacuum

[Jan Wieck]

On 10/27/2005 8:34 AM, Mario Splivalo wrote:

Postgres itself offers no replication. 


Oracle itself offers no replication.
IBM DB2 itself offers no replication.

Yet most of the products out there for Oracle, DB2 and PostgreSQL are 
far better than what I read here:


http://dev.mysql.com/doc/refman/5.0/en/replication-features.html

What a great example of "offering replication itself" ... *cough*

I guess it'd be easier to port a well designed add-on like Slony-I to 
MySQL (now that they have triggers) than fixing their broken design. Not 
that I volunteer to do it, but Slony-I is released under BSD, anyone 
feel free to offer a good solution for MySQL.



Jan

--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #

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

  http://archives.postgresql.org

Re: [SQL] How to speed up the database query?

[Jim C. Nasby]

Have you taken a look at
http://www.postgresql.org/docs/8.0/interactive/performance-tips.html ?

On Thu, Oct 27, 2005 at 03:03:36PM +0800, Abdul Wahab Dahalan wrote:
> Hi everyone!
> 
> I'm looking for solution to speed up the database query, means that to get 
> resultset as quicker as we can.
> 
> For example if I've 700 records in the table it will take longer time 
> compared if I've only 20 records. How do we speed up the query?. Any query 
> technique that can be applied?.
> 
> Thus wild card query like : select * from tableA will cause query time 
> increased compare to say select a,b from tableA.
> 
> any help, prettymuch appreciated.
> 
> 
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
> 

-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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

[SQL] combining records from a single table and presenting them as one record

[Abhishek]

Hi Everyone
 
I have a table "TABLE1"  which has

Callguid  |   digits |  type

   123   'a'
   345   'b'
   678   'c'
 
type can have only 'a', 'b' or 'c' as its value.
I am tryng to write a query which returns me a record like this
 
---
CallGuid  |   a type digits   | b type digits | c type digits
---
    123 345   678
 
 
I do the query as this:
 
select callguid , ( select digits from TABEL1 where type='a' ), ( select digits from TABEL1 where type='b' ), ( select digits from TABEL1 where type='c' ) from TABLE1; 
 
result:

---
CallGuid  |   a type digits   | b type digits | c type digits
---
    123 345   678
    123 345   678
    123 345   678
 
Note that I get the records which i wanted, but I get them duplicated. If I use 'distinct' keyword I get the desired result with no duplication. But 'distinct' seems to be too expensive.
 
Is there any better way I can do this without compromising the performance ?
 
Your suggestions and input are very appreciated
 
Thanks
AJ
 
-- Abhishek Jain 

Re: [SQL] why vacuum

[Jim C. Nasby]

On Thu, Oct 27, 2005 at 02:21:15PM +0100, Richard Huxton wrote:
> So - if your statement contains something non-deterministic that isn't 
> catered for in Mysql's code then it will break.
> 
> At it's simplest - if I write a function my_random() and then do:
>   UPDATE foo SET a=1 WHERE b < my_random();
> IF my_random() returns different results on different machines, then the 
> replication will be broken. See the manual entry below:
>   http://dev.mysql.com/doc/refman/5.0/en/replication-features.html
> 
> That's not to say the system is worthless - it works fine for many 
> people. But it does have limitations.

And you can easily have multi-master syncronous replication in
PostgreSQL using the same idea; just see pgCluster.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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

   http://archives.postgresql.org

Re: [SQL] Merging lines with NULLs (with example data)

[MaXX]

Thank you, and sorry for the late answer, I was far away from a decent
internet connection...

I'll try both your solutions, EXPLAIN ANALYSE will elect the winner... In
any case that will be cleaner than my dirty hack (2 distinct queries) which
generate a lot of garbage...

Thanks again,
MaXX

Daryl Richter wrote:
> Harald Fuchs wrote:
>> Try something like that:
>>   SELECT to_date (tstamp,'-MM-DD') AS gday,
>>  sum(CASE WHEN proto = 'UDP' THEN 1 ELSE 0 END) AS count_udp,
>>  sum(CASE WHEN proto = 'TCP' THEN 1 ELSE 0 END) AS count_tcp
>>   FROM test
>>   WHERE tstamp >= now() - INTERVAL '$days DAYS'
>> AND dst_port = $port
>>   GROUP BY gday
>>   ORDER BY gday
> Or, via a subquery:
> select distinct to_date(tstamp,'-MM-DD') as gday,
>  ( select count(id) from test t1 where proto='UDP' and
> to_date(t1.tstamp,'-MM-DD') = to_date(test.tstamp,'-MM-DD') ) as
> count_udp,
>  ( select count(id) from test t1 where proto='TCP' and
> to_date(t1.tstamp,'-MM-DD') = to_date(test.tstamp,'-MM-DD') ) as
> count_tcp
> from test
>  where tstamp >= (now() - interval '6 days' )
>  and dst_port = 2290
>  order by gday;
> 
> Harald's solution is better for your particular case and will almost
> certainly be faster, but subqueries are good to know how to do. :)
-- 
MaXX

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

[SQL] Referencing

[lucas]

Hi.
Is there a way to references dynamic tables? I.E:
I have a table called "buy" that create some records in "financial" table, but
there is other table called "send" that create other records in "financial".
"Financial" table have the moneys' movements and needs to be referenciable by
"buy or send". IE:
 create table buy (
  id serial primary key,
  product_id integer,--references
  value money
 );
 create table send (
  id serial primary key,
  product_id integer, --references...
  value money
 );
 create table financial(
  id serial primary key,
  cred_deb smallint,
  value money,
  references integer, --<<-HERE IS THE PROBLEM, it will reference to buy OR send
table
 );
Well, I dont know if I was clean.
Thank you.

---(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: [SQL] combining records from a single table and presenting them as one record

[boinger]

On 10/27/05, Abhishek <[EMAIL PROTECTED]> wrote:
> I am tryng to write a query which returns me a record like this

> I do the query as this:
>
> select callguid , ( select digits from TABEL1 where type='a' ), ( select
> digits from TABEL1 where type='b' ), ( select digits from TABEL1 where
> type='c' ) from TABLE1;

> Note that I get the records which i wanted, but I get them duplicated. If I
> use 'distinct' keyword I get the desired result with no duplication. But
> 'distinct' seems to be too expensive.
>
> Is there any better way I can do this without compromising the performance ?

Just add 'LIMIT 1' to the end of the query.

---(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: [SQL] combining records from a single table and presenting

[Frank Bax]

At 02:00 PM 10/27/05, Abhishek wrote:

I have a table "TABLE1"  which has

Callguid  |   digits |  type

   123   'a'
   345   'b'
   678   'c'

type can have only 'a', 'b' or 'c' as its value.
I am tryng to write a query which returns me a record like this

---
CallGuid  |   a type digits   | b type digits | c 
type digits

---
123 345 
   678



1) You didn't say if 'a','b','c' records always exist for every callguid.
2) You didn't say if there is more than one record for a given callguid/type.

If (1) is 'yes' and (2) is 'no'
select a.callguid, a.digits as a_digits, b.digits as b_digits, c.digits as 
c_digits

from
(select callguid,digits from table1 where type='a') as a
join
(select callguid,digits from table1 where type='b') as b on 
a.callguid=b.callguid

join
(select callguid,digits from table1 where type='c') as c on 
a.callguid=c.callguid;


If (1) is 'no' and (2) is 'no'

select coalesce(a.callguid,b.callguid,c.callguid) as callguid,
a.digits as a_digits, b.digits as b_digits, c.digits as c_digits
from
(select callguid,digits from table1 where type='a') as a
full outer join
(select callguid,digits from table1 where type='b') as b on 
a.callguid=b.callguid

full outer join
(select callguid,digits from table1 where type='c') as c on 
a.callguid=c.callguid;


If (2) is 'yes', you're on your own. You can also try searching for 
"crosstab" and/or "pivot table" for more info. 



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

  http://archives.postgresql.org

Re: [SQL] converting epoch to timestamp

[Rajesh Kumar Mallah]

On 10/26/05, Richard Huxton  wrote:
> Rajesh Kumar Mallah wrote:
> > Hi,
> >
> > Can anyone tell me how to convert epoch to timestamp ?
> >
> > ie reverse of :
> >
> > SELECT EXTRACT( epoch FROM  now() );
>
> I'd start with either Google or the manuals.
>
> http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html


Firstly Thanks everyone for the response.

I did read this document but not too carefully hence missed.
I missed because i was not careful and partly because i was not expecting that
little note to be under documentation of EXTRACT which deals with getting
date/time sub fields. I am no documentation expert just trying to explain
why i could not find it.

PS: sorry for late reply

Regds
Mallah.











>
> Scroll down to the section on "epoch" here and see the example.
>
> --
>Richard Huxton
>Archonet Ltd
>

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

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