Bill Moran | 1 Mar 01:24 2007

Re: why can't I increase shared buffers to higher value?

Dino Vliet <dino_vliet <at> yahoo.com> wrote:
>
> I have the following config but increasing the shared buffers to a value greater then 32 doesn't let the
database server start (I want a value of 256MB there because I will have a giant table of 12 million rows
which will be qeuried extremely). 
> 
> I have a 3GB RAM amd64 system running freebsd 6.1 with:
>  
> Maintenance_work_mem is 32MB
> Max_stack_depth is 3MB
> Shared_buffers is 32MB
> Temp_buffers is 8MB
> Work_mem is 32MB
>  Max_fsm_pages is 204800
>  Max_connections is 3
> 
> And I am doing this:
> 
> sysctl -w kern.ipc.shmmax=1954311424
> sysctl -w kern.ipc.shmall=16384
> 
> Why is my shared buffer value not increasing?

16384 is only 130M.  Try using 238000 for shmall.

--

-- 
Bill Moran
Collaborative Fusion Inc.

---------------------------(end of broadcast)---------------------------
(Continue reading)

Omar Eljumaily | 1 Mar 01:19 2007

Why does "group by" need to match select fields?

Sorry if this isn't exactly postgresql specific.  I periodically run 
into this problem, and I'm running into it now.  I'm wondering if 
there's something about "group by" that I don't understand.  As an 
example what I'd want to do is return the "id" value for the check to 
each payee that has the highest amount.  It seems like there's no 
problem with ambiguity in logic, but postgresql + other sql servers balk 
at it.  The group by fields need to explicitly match the select fields 
with the exception of the aggregate function(s?).

create table checks
{
    id serial,
    payee text,
    amount double
};

select max(amount), payee, id from checks group by payee;

Why won't the above work?  Is there another way to get the id for the 
record with the highest amount for each payee?

Thanks.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo <at> postgresql.org so that your
       message can get through to the mailing list cleanly

Paul Lambert | 1 Mar 01:44 2007
Picon

PG periodic Error on W2K

I'm running PG 8.2.3 on We doze 2000 Server. (Should I apologise for 
that up front to appease the masses?)

I am periodically getting errors pop up on the server console of the 
following nature:

The File or directory D:\PostgresQL\Data\global\pgstat.stat is corrupt 
and unreadable. Please run the Chkdsk utility.

and

The file or directory D: is corrupt and unreadable. Please run the 
Chkdsk utility.

Now, per the errors suggestion I have run the chkdsk utility with a /X 
/F switch to do a complete check on reboot before mounting the volume.

This showed no errors.

I can also open the mentioned file - pgstat.stat - using notepad or any 
other program without mention of corruption and the data within the file 
looks to be uniform suggesting it is fine.

Strangely enough, this error was being presented on the last server I 
had it running on, and was in fact one of the reasons I moved it - I 
assumed the error was due to dodgy disks but this seems a bit much of a 
coincidence.

I know these errors are not coming directly from Postgres, but does 
anyone else have problems (or has had previously) of a similar nature or 
(Continue reading)

Bill Moran | 1 Mar 01:47 2007

Re: Why does "group by" need to match select fields?

Omar Eljumaily <omar2 <at> omnicode.com> wrote:
>
> Sorry if this isn't exactly postgresql specific.  I periodically run 
> into this problem, and I'm running into it now.  I'm wondering if 
> there's something about "group by" that I don't understand.  As an 
> example what I'd want to do is return the "id" value for the check to 
> each payee that has the highest amount.  It seems like there's no 
> problem with ambiguity in logic, but postgresql + other sql servers balk 
> at it.  The group by fields need to explicitly match the select fields 
> with the exception of the aggregate function(s?).
> 
> create table checks
> {
>     id serial,
>     payee text,
>     amount double
> };
> 
> select max(amount), payee, id from checks group by payee;
> 
> Why won't the above work?  Is there another way to get the id for the 
> record with the highest amount for each payee?

Because it's ambiguous.  If you're grabbing max() for amount, which
id tuple do you want?

Perhaps the way you're storing your data, those answers aren't ambiguous,
but the database doesn't know that.  Take this query as an example:

select max(amount), max(checknumber), payee from checks group by payee;
(Continue reading)

Joshua D. Drake | 1 Mar 01:52 2007

Re: PG periodic Error on W2K

Paul Lambert wrote:
> I'm running PG 8.2.3 on We doze 2000 Server. (Should I apologise for
> that up front to appease the masses?)

Probably ;)

> 
> I am periodically getting errors pop up on the server console of the
> following nature:
> This showed no errors.
> 
> I can also open the mentioned file - pgstat.stat - using notepad or any
> other program without mention of corruption and the data within the file
> looks to be uniform suggesting it is fine.

Try turning off stats. However you will need to run vacuum using some
other method.

Joshua D. Drake

> 
> Thoughts? Ideas? Suggestions? Should I bugger off?
> 

The Windows port is still young, we are here to help. Try the above and
see if the problem goes away :)

Sincerely,

Joshua D. Drake
(Continue reading)

Omar Eljumaily | 1 Mar 01:57 2007

Re: Why does "group by" need to match select fields?

OK, I see what's going on.  I can have more than one max(amount) with 
the same amount and payee.  Thanks so much.  Like I said, it's sort of 
dogged me off and on many times.

Thanks.

Bill Moran wrote:
> Omar Eljumaily <omar2 <at> omnicode.com> wrote:
>   
>> Sorry if this isn't exactly postgresql specific.  I periodically run 
>> into this problem, and I'm running into it now.  I'm wondering if 
>> there's something about "group by" that I don't understand.  As an 
>> example what I'd want to do is return the "id" value for the check to 
>> each payee that has the highest amount.  It seems like there's no 
>> problem with ambiguity in logic, but postgresql + other sql servers balk 
>> at it.  The group by fields need to explicitly match the select fields 
>> with the exception of the aggregate function(s?).
>>
>> create table checks
>> {
>>     id serial,
>>     payee text,
>>     amount double
>> };
>>
>> select max(amount), payee, id from checks group by payee;
>>
>> Why won't the above work?  Is there another way to get the id for the 
>> record with the highest amount for each payee?
>>     
(Continue reading)

Paul Lambert | 1 Mar 02:06 2007
Picon

Re: PG periodic Error on W2K

Joshua D. Drake wrote:
> Paul Lambert wrote:
>> I'm running PG 8.2.3 on We doze 2000 Server. (Should I apologise for
>> that up front to appease the masses?)
> 
> Probably ;)

I propound to all my sincerest of apologies for installing what I 
believe to be a marvel of human creation in Postgres on what most 
believe to be the ultimate travesty of what some claim to be an 
operating system in Macrohard Webloze.

I would be delighted to offer my assurance that such a farce would never 
again take place, but I have about forty such installations to do when 
my development is complete :(

> 
>> I am periodically getting errors pop up on the server console of the
>> following nature:
>> This showed no errors.
>>
>> I can also open the mentioned file - pgstat.stat - using notepad or any
>> other program without mention of corruption and the data within the file
>> looks to be uniform suggesting it is fine.
> 
> Try turning off stats. However you will need to run vacuum using some
> other method.
> 
> Joshua D. Drake
> 
(Continue reading)

Naz Gassiep | 1 Mar 01:38 2007
Picon

Re: Select retrieval slowdown after db drop/reload. Suggestions?

You have to run ANALYZE; on your db after a drop/reload to recollect the stats. In the rest db, jus run ANALYZE; and then see how fast it is. I'd guess that this is your issue.
Regards,
- Naz.

Andrew Edson wrote:
I have a select statement, used in a Perl program, which is supposed to find all records related to those in one table which have a delete_dt field value of four years or older. 
 
This is the select statement:
SELECT t2.dist_id, t1.clnt_seq, t2.cntrct_seq, t2.cntrct_id, t3.aunit_seq, t1.person_seq, t1.addr_seq, t3.addr_seq, t4.frst_nm || ' ' || t4.lst_nm AS name, t5.addr_1, t6.acntdel_dt FROM t1, t2, t3, t4, t5, t6 WHERE t1.clnt_seq = t2.clnt_seq AND t2.cntrct_seq = t3.cntrct_seq AND t3.aunit_seq = t6.aunit_seq AND t1.person_seq = t4.person_seq AND t3.addr_seq = t5.addr_seq AND t1.active_flg =0 AND t2.active_flg =0 AND t3.active_flg = 0 AND t6.acntdel_dt < now() - '4 years'::interval order by t2.cntrct_id asc;
 
I'm working on a test box at the moment; the db I am using was made by dumping the production db and copying it over to the test box to be loaded into a newly-created db there.  It took a while for me to get the original Perl program working, as I don't really understand Perl, but after I did so, I dropped the db and reloaded it again off of the original files, so I could try another test run and pay attention to what's happening.
 
On the original load of the test db, the query above had a run time of roughly 3, 3.5 minutes before giving results.  Considering the size of the db it's searching through, I feel that's fairly reasonable, especially since that's about what the production db does on the same query.  Now, after the drop/recreate, the test db is taking somewhat longer to give back its results; just shy of 7 minutes, if I've done the math correctly.  (Timing results - Time: 417531.436 ms)
 
I'm the only person working on this particular box at this point.  This problem did not start until I reloaded the db from the original files.  Obviously, I managed to miss something in the drop/reload process, but I have no clue what.  I'm running a vacuum full analyze at the moment; if anyone has any other suggestions as to what I could do to solve this (admittedly minor) problem, I would be grateful to hear them.
 
Thank you for your consideration.
 

No need to miss a message. Get email on-the-go
with Yahoo! Mail for Mobile. Get started.
Bruno Wolff III | 1 Mar 02:19 2007
Picon

Re: [GENERAL] Esay question, about the numeric format

On Thu, Feb 22, 2007 at 12:20:12 +0100,
  Rafa Comino <rafacomino <at> gmail.com> wrote:
> Hi every body
> I have this query
> SELECT 20.00::numeric(38,2)
> and postgre gives me 20, i need that postgre gives me 20.00
> What can i do? i suppose this must be easy, but i dont find how to do ir
> thanks every body

If the exact output format matters, you should probably use to_char to convert
the number to a string.

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

Shaun Johnston | 1 Mar 02:25 2007

Assistance with Query Optimisation?

Hi

Apologies in advance for the verbosity of my explanation for this 
problem, but I think it's all pertinent.

I have a fairly simple query which postgresql's query planner seems to 
be interpreting / optimising in interesting ways:

Query:

SELECT *
FROM account_transaction
WHERE account_id = 10783
ORDER BY account_transaction_timestamp asc
OFFSET 0
LIMIT 100

Table "public.account_transaction"

(I have stripped the default values etc for the sake of formatting)

            Column             |           Type           |
-------------------------------+--------------------------+
 account_transaction_id        | bigint                   |
 account_transaction_timestamp | timestamp with time zone |
 account_id                    | integer                  |
 other_account_transaction_id  | bigint                   |
 transaction_reason            | text                     |
 transaction_amount            | numeric(15,2)            |
 transaction_exchange_rate     | numeric(20,10)           |
 transaction_base_amount       | numeric(15,2)            |
 transaction_locked_until      | timestamp with time zone |
 transaction_approved          | boolean                  |
Indexes:
    "account_transaction_pkey" PRIMARY KEY, btree (account_transaction_id),
     tablespace "indexes"
    "account_transaction_ai" btree (account_id), tablespace "indexes"
    "account_transaction_timestamp" btree (account_transaction_timestamp),
     tablespace "indexes"
    "account_transaction_tlu" btree (transaction_locked_until),
     tablespace "indexes"
Foreign-key constraints:
    "$1" FOREIGN KEY (account_id) REFERENCES
      account(account_id)
    "$2" FOREIGN KEY (other_account_transaction_id) REFERENCES
      account_transaction(account_transaction_id) ON UPDATE CASCADE

Query Plans:

With Sort and Limit
---------------------------------------------------------------------------------
                                    QUERY 
PLAN                                       
---------------------------------------------------------------------------------
 Limit  (cost=0.00..12297.59 rows=100 width=120)
 (actual time=23.537..275476.496 rows=100 loops=1)
   ->  Index Scan Backward using account_transaction_timestamp on
       account_transaction 
       (cost=0.00..640704.23 rows=5210 width=120)
       (actual time=23.529..275475.781 rows=100 loops=1)
         Filter: (account_id = 10783)
 Total runtime: 275476.944 ms

With Sort but no Limit
--------------------------------------------------------------------------------
                                    QUERY 
PLAN                                       
--------------------------------------------------------------------------------
 Sort  (cost=18777.41..18790.43 rows=5210 width=120)
 (actual time=1081.226..1082.170 rows=308 loops=1)
   Sort Key: account_transaction_timestamp
   ->  Index Scan using account_transaction_ai on account_transaction 
       (cost=0.00..18455.77 rows=5210 width=120)
       (actual time=47.731..1070.788 rows=308 loops=1)
         Index Cond: (account_id = 10783)
 Total runtime: 1083.182 ms

With Limit but no Sort
---------------------------------------------------------------------------------
                                    QUERY 
PLAN                                       
---------------------------------------------------------------------------------
 Limit  (cost=0.00..354.24 rows=100 width=120)
 (actual time=0.029..1.070 rows=100 loops=1)
   ->  Index Scan using account_transaction_ai on account_transaction 
       (cost=0.00..18455.77 rows=5210 width=120)
       (actual time=0.022..0.467 rows=100 loops=1)
         Index Cond: (account_id = 10783)
 Total runtime: 1.422 ms

With Limit and Sort, but sorted by transaction_base_amount
---------------------------------------------------------------------------------
                                    QUERY 
PLAN                                       
---------------------------------------------------------------------------------
 Limit  (cost=18777.41..18777.66 rows=100 width=120)
 (actual time=55.294..56.221 rows=100 loops=1)
   ->  Sort  (cost=18777.41..18790.43 rows=5210 width=120)
       (actual time=55.285..55.600 rows=100 loops=1)
         Sort Key: transaction_base_amount
         ->  Index Scan using account_transaction_ai on 
account_transaction 
             (cost=0.00..18455.77 rows=5210 width=120)
             (actual time=0.057..53.187 rows=308 loops=1)
               Index Cond: (account_id = 10783)
 Total runtime: 56.597 ms

The table currently contains about 10 million records. 

If I sort by account_transaction_timestamp then limit, the planner 
performs a backward index scan based on account_transaction_timestamp 
then limits.

If I sort but don't limit, it performs a forward scan on the table, then 
a sort on the results.

If I limit but don't sort, it performs the forward scan then limits.

If I limit and sort, but sort by transaction_base_amount instead, it 
performs a forward index scan, then sorts the results, then limits - in 
stark contrast to sorting and limiting using account_transaction_timestamp.

As shown, there is a vast difference in efficiency between sorting and 
limiting by account_transaction_timestamp, and doing the same using 
transaction_base_amount (in favour of transaction_base_amount by a 
factor of about 4860).  This seems to be due to the way the planner is 
optimising the query.

Could this optimisation peculiarity be due to the combination of indexed 
columns in the query?  Also, Is there a way I can 'force' the planner to 
perform an 'index scan -> sort -> limit' or even better an 'index scan 
-> limit -> sort'?

Any pointers / assistance appreciated.

Kind Regards,

Shaun Johnston

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo <at> postgresql.org so that your
       message can get through to the mailing list cleanly


Gmane