Re: [SQL] Does VACUUM reorder tables on clustered indices

[ipv]

Hi,

Utilize CLUSTER; (after vacuum) to reorder the data.

Regards


- Original Message - 
From: "Jim C. Nasby" <[EMAIL PROTECTED]>
To: "Martin Marques" ; "frank church" 
<[EMAIL PROTECTED]>; 

Sent: Tuesday, December 20, 2005 10:41 PM
Subject: Re: [SQL] Does VACUUM reorder tables on clustered indices



On Sun, Dec 18, 2005 at 07:01:39PM -0300, Alvaro Herrera wrote:

Martin Marques escribi?:
> On Sun, 18 Dec 2005, frank church wrote:
>
> >
> >Does VACUUMing reorder tables on clustered indices or is it only the
> >CLUSTER
> >command that can do that?
>
> Cluster does that. Vacuum only cleans dead tuples from the tables.

Note that while reordering, CLUSTER also gets rid of dead tuples, so if
you cluster you don't need to vacuum.


It also does a REINDEX...
--
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 6: explain analyze is your friend





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

Fw: [SQL] stored procedures for complex SELECTs

[ipv]

For better performance, try to utilize prepared statements;

example:

PREPARE CPrep_Clients (varchar(20)) AS
SELECT * FROM client WHERE nom = $1;

EXECUTE CPrep_Clients('Francio');

In my system, over 51ms difference between standard select (prepared 
by-pass

planification/optimization)

Regards



- Original Message - 
From: <[EMAIL PROTECTED]>

To: 
Sent: Thursday, January 19, 2006 12:17 AM
Subject: [SQL] stored procedures for complex SELECTs




Are there performance advantages that can be achieved by wrapping a
complex SELECT into a stored procedure?

Alex

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

  http://archives.postgresql.org







---(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] sorting by day of the week

[ipv]

Hi,

Try to use a calculated index:

(tested similar solution, but not this code)


CREATE OR REPLACE FUNCTION TestOrder (nameTable.weekDay%TYPE) RETURNS INT AS 
'


DECLARE

numWeekDay INT;

BEGIN

if ($1 = ''Wed'') then numWeekDay := 1;

if ($1 = ''Tue'') then numWeekDay := 2;

.

RETURN (numWeekDay);

END;

' LANGUAGE 'plpgsql' STRICT IMMUTABLE;


CREATE INDEX idx_TestOrder

ON nameTable USING btree (TestOrder(nameTable.weekDay));


SELECT * FROM trajecte ORDER BY TestOrder(nameTable.weekDay);



Regards


- Original Message - 
From: "Joseph Shraibman" 

To: 
Sent: Wednesday, January 25, 2006 2:23 AM
Subject: [SQL] sorting by day of the week


p8:owl=>SELECT to_char( logtime, 'Dy'),count(*) FROM logtab WHERE 
date_trunc('day', logtime) > current_date + '7 day ago'::interval group by 
to_char( logtime, 'Dy') ORDER BY to_char( logtime, 'Dy') DESC;

 to_char | count
-+---
 Wed |  1447
 Tue |   618
 Thu |  1161
 Sun |   230
 Sat |   362
 Mon |   760
 Fri |  1281
(7 rows)

The problem is that I want those results sorted in day of week order, not 
text order of the day name, so I tried this:


p8:owl=>SELECT to_char( logtime, 'Dy'),count(*) FROM sclog WHERE 
date_trunc('day', logtime) > current_date + '7 day ago'::interval group by 
to_char( logtime, 'Dy') ORDER BY to_char( logtime, 'D') DESC;
ERROR:  column "sclog.logtime" must appear in the GROUP BY clause or be 
used in an aggregate function


Now obviously I don't want to group by logtime (a timestamp) so how do I 
work around this?  What I really need is a function that converts from the 
char representation to a day of week number or vice versa.  I also have 
the same problem with month names.


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

  http://archives.postgresql.org





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