On Monday 20 July 2009 06:45:40 Phoenix Kiula wrote:
> explain analyze select * from sites where user_id = 'phoenix' order by
> id desc limit 10;
>
> QUERY PLAN
> ---
>---
Hey Tom,
I was trying to use 'US/Pacific-New' as my long, unabbreviated timezone
and it wasn't working. I thought postgres wasn't accepting the unabbreviated,
geopolitical, daylight savings time, time zones. Turns out, the server that I
was on, (not my own box), didn't have that in the '/u
On Sun, Jul 19, 2009 at 9:45 PM, Phoenix Kiula wrote:
> Hi.
>
> I have a query that should be very fast because it's using all
> indexes, but it takes a lot of time.
>
>
> explain analyze select * from sites where user_id = 'phoenix' order by
> id desc limit 10;
>
>
Hi.
I have a query that should be very fast because it's using all
indexes, but it takes a lot of time.
explain analyze select * from sites where user_id = 'phoenix' order by
id desc limit 10;
QUERY PLAN
---
William Scott Jordan wrote:
> Hi Andrew,
>
> That's a very good guess. We are in fact updating this table multiple
> times within the same triggered function, which is being called on an
> INSERT. Essentially, we're using this to keep a running total of the
> number of rows being held in an
Rafael Martinez wrote:
> Does the use of pg_stat_reset() affects the statistics autovacuum uses
> to find out what to do and when this should be done?
Yes. You should do a manual ANALYZE after resetting stats to keep
autovacuum in sync with reality. (In principle ANALYZE is only
concerned with
Dennis Gearon writes:
> What I want is to be able to insert into my project's database, times
> given by anybody anywhere on the planet (the SUBMITTER), add the appropriate
> timezone in the insert statement so that it in 'GMT/UMT' neutral'. I believe
> this is the way Postgres does it, st
Good Idea Adrian!
What I want is to be able to insert into my project's database, times
given by anybody anywhere on the planet (the SUBMITTER), add the appropriate
timezone in the insert statement so that it in 'GMT/UMT' neutral'. I believe
this is the way Postgres does it, storing times
On Sun, Jul 19, 2009 at 7:08 PM, Martijn van
Oosterhout wrote:
> On Sun, Jul 19, 2009 at 10:16:17AM +0800, Phoenix Kiula wrote:
> Look through the archives, there are scripts that will scan all your
> text fields for UTF-8 problems. If you run them once you can clear out
> all the problems prior t
On Sun, Jul 19, 2009 at 08:56:08PM -0400, Robert James wrote:
> Thanks, Chris. Is there a way to do this deterministically, or at least
> programatically? I have code to create the tables and cluster them
> automatically?
As Chris said, the index used by the primary key uses the table name
with "
On Sunday 19 July 2009 6:41:24 pm Dennis Gearon wrote:
> None of the examples of converting a string to_timestamp() show using a
> time zone input as an input.Does it allow full length timezones for
> daylight savings time at the timestamp instant in time, or just an
> abbreviation for a fixed offs
None of the examples of converting a string to_timestamp() show using a time
zone input as an input.Does it allow full length timezones for daylight savings
time at the timestamp instant in time, or just an abbreviation for a fixed
offset?
--- On Sun, 7/19/09, Adrian Klaver wrote:
> From:
Robert James wrote:
Thanks, Chris. Is there a way to do this deterministically, or at least
programatically? I have code to create the tables and cluster them
automatically?
From a quick test, it seems the naming convention is 'tablename_pkey':
# create table a(blah text primary key);
NOTICE
On Sun, Jul 19, 2009 at 6:10 PM, Robert James wrote:
> UNION was better, but still 5 times as slow as either query done
> individually.
> set enable_seqscan=off didn't help at all - it was totally ignored
> Is there anything else I can do?
Did you try union, or union all?
--
Sent via pgsql-gener
Is there anyway to tell Postgres "Run these two queries, and union their
results, but don't change the plan as to a UNION - just run them
separately"?
Something seems funny to me that running a UNION should be twice as slow as
running the two queries one after the other.
On Sun, Jul 19, 2009 at 8:
Thanks, Chris. Is there a way to do this deterministically, or at least
programatically? I have code to create the tables and cluster them
automatically?
On Sun, Jul 19, 2009 at 8:21 PM, Chris wrote:
> Robert James wrote:
>
>> I would like to CLUSTER a table on its PRIMARY KEY. Now, I haven't
Krishna Komarpant wrote:
Hello,
I am Java Solutions Developer. I have my servers that run PostgreSQL
v8.1.11 and they are quit stable.
Recently i was supposed to install a Database Replication tool for the
Databases. I realized that i might have to upgrade my database from v8.1
.11 to v8.4
Robert James wrote:
I would like to CLUSTER a table on its PRIMARY KEY. Now, I haven't
explicitly defined and named an index for this table - but the primary
key defines one. How can I tell Postgres to CLUSTER on it?
Get the index name:
\d tablename
Right at the bottom it will have the in
On Sunday 19 July 2009 4:56:09 pm Dennis Gearon wrote:
> I read it better, and it makes more sense now.
>
> But,
> I'd like it to show how to insert:
> 'strings' - which it does
> timestampz value -->using to_timestampz(...)
For above:
http://www.postgresql.org/docs/8.4/interactive/functio
UNION was better, but still 5 times as slow as either query done
individually.
set enable_seqscan=off didn't help at all - it was totally ignored
Is there anything else I can do?
On Sun, Jul 19, 2009 at 7:47 PM, Tom Lane wrote:
> Robert James writes:
> > Hi. I notice that when I do a WHERE x,
I would like to CLUSTER a table on its PRIMARY KEY. Now, I haven't
explicitly defined and named an index for this table - but the primary key
defines one. How can I tell Postgres to CLUSTER on it?
Also: If I define an index on a PK, will Postgres make a second one, or
realize its redundnant?
Tha
I read it better, and it makes more sense now.
But,
I'd like it to show how to insert:
'strings' - which it does
timestampz value -->using to_timestampz(...)
integers::timestampz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subsc
Robert James wrote:
Hi. I notice that when I do a WHERE x, Postgres uses an index, and
when I do WHERE y, it does so as well, but when I do WHERE x OR y, it
doesn't. Why is this so? And how can I shut this off?
maybe its because you have no index on (X OR Y) ? or maybe because the
analyzer
Robert James writes:
> Hi. I notice that when I do a WHERE x, Postgres uses an index, and when I
> do WHERE y, it does so as well, but when I do WHERE x OR y, it
> doesn't.
It can use indexes for OR conditions, but not for arbitrary OR
conditions...
> select * from dict
> where
> word in (sele
On Sun, Jul 19, 2009 at 11:59 PM, Robert James wrote:
> PS Running "PostgreSQL 8.2.1 on i686-pc-mingw32, compiled by GCC gcc.exe
> (GCC) 3.4.2 (mingw-special)"
>
> On Sun, Jul 19, 2009 at 6:58 PM, Robert James
> wrote:
>>
>> Hi. I notice that when I do a WHERE x, Postgres uses an index, and when
PS Running "PostgreSQL 8.2.1 on i686-pc-mingw32, compiled by GCC gcc.exe
(GCC) 3.4.2 (mingw-special)"
On Sun, Jul 19, 2009 at 6:58 PM, Robert James wrote:
> Hi. I notice that when I do a WHERE x, Postgres uses an index, and when I
> do WHERE y, it does so as well, but when I do WHERE x OR y, it
Hi. I notice that when I do a WHERE x, Postgres uses an index, and when I
do WHERE y, it does so as well, but when I do WHERE x OR y, it doesn't. Why
is this so? And how can I shut this off?
select * from dict
where
word in (select substr('moon', 0, generate_series(3,length('moon' --
this is
Hello,
I recently upgraded to version 8.4 and now full text search with russian
configuration is not working:
template1=# create database test encoding='win1251';
test=# create table test ("test" varchar(255));
test=# insert into test values ('тест');
test=# select * from test where to_tsvect
On Sun, Jul 19, 2009 at 01:29:14PM -0700, Dennis Gearon wrote:
> INSERTing timestampz, 'to_timestamp', output formatting, input formatting,
> SERVER TIME, USER_LOCAL_TIME, multi timezone applications.
>
> Anyone wonder how those all work? **I** sure do!!!
The official docs[1,2] have generally be
On Sunday 19 July 2009 1:29:14 pm Dennis Gearon wrote:
> INSERTing timestampz, 'to_timestamp', output formatting, input formatting,
> SERVER TIME, USER_LOCAL_TIME, multi timezone applications.
>
> Anyone wonder how those all work? **I** sure do!!!
>
> Is there a tutorial anywhere on how to do all t
INSERTing timestampz, 'to_timestamp', output formatting, input formatting,
SERVER TIME, USER_LOCAL_TIME, multi timezone applications.
Anyone wonder how those all work? **I** sure do!!!
Is there a tutorial anywhere on how to do all those? Can anyone who is
knowledgeable about this:
(1) Po
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
First of all *thank* you very much to all that replied! :)
Sam's suggestion actually did the trick!
I created the multi-column index and the stalling went away. Yay!
Best regards,
Pedro Doria Meunier
GSM: +351 96 17 20 188
Skype: pdoriam
Tom L
On Sun, Jul 19, 2009 at 01:20:27PM -0400, James B. Byrne wrote:
> I was sending off a where clause that looked somewhat like this:
>
> WHERE 'date_of_interest' <= 2009-07-18
>
> Now, as the date of interest was, in all but one case, prior to 1970
> this appeared to work. However, in one case th
"James B. Byrne" writes:
> My error was in not encapsulating a programmically inserted date
> string within quotation marks. This meant that I was sending off a
> where clause that looked somewhat like this:
> WHERE 'date_of_interest' <= 2009-07-18
Ah. You are apparently using a pre-8.3 PG re
Hello,
I am Java Solutions Developer. I have my servers that run PostgreSQL v8.1.11
and they are quit stable.
Recently i was supposed to install a Database Replication tool for the
Databases. I realized that i might have to upgrade my database from v8.1 .11
to v8.4
*1) Would there be any compatibi
I encountered a situation wrt date expressions that, although I
eventually resolved, has left me with a few unanswered questions
regarding how PG handles dates.
My error was in not encapsulating a programmically inserted date
string within quotation marks. This meant that I was sending off a
wher
My scenario was the following. I got the following error message:
postgres=# create trusted language plpythonu;
ERROR: could not load library "C:/Program
Files/PostgreSQL/lib/plpython.dll": unknown error 126
I had Python 3.1 installed and I did not know that PostgreSQL does not
support this. The
Andreas Kretschmer writes:
> Sam Mason wrote:
>> Based on your query, I think you want a multi-column index---probably on
>> (id,zulu_timestamp).
>>
>> The problem with just having an index on either column is that it's
>> difficult to combine them and PG hence just thinks that it will be
> Sin
On Sun, Jul 19, 2009 at 02:56:04PM +0200, Andreas Kretschmer wrote:
> Sam Mason wrote:
> > The problem with just having an index on either column is that it's
> > difficult to combine them and PG hence just thinks that it will be
>
> Since 8.1 PG can do an bitmap index scan using both indexes...
Thanks Pavel, that rocks.
> SELECT * FROM pg_stat_all_tables;
>
> regards
> Pavel Stehule
>
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Sam Mason wrote:
> On Sun, Jul 19, 2009 at 11:15:39AM +0100, Pedro Doria Meunier wrote:
> > I have a table ~18M rows with a 'timestamp with time zone' column. It's
> > indexed thus:
> >
> > CREATE INDEX my_table_timestamp_idx
> > ON my_table
> > USING btree
> > (zulu_timestamp);
>
> Based
On Sun, Jul 19, 2009 at 11:15:39AM +0100, Pedro Doria Meunier wrote:
> I have a table ~18M rows with a 'timestamp with time zone' column. It's
> indexed thus:
>
> CREATE INDEX my_table_timestamp_idx
> ON my_table
> USING btree
> (zulu_timestamp);
Based on your query, I think you want a mult
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi All,
I *really* need some help with this one...
I have a table ~18M rows with a 'timestamp with time zone' column. It's
indexed thus:
CREATE INDEX my_table_timestamp_idx
ON my_table
USING btree
(zulu_timestamp);
whenever I issue a command
Pedro Doria Meunier wrote:
> Hi All,
>
> I *really* need some help with this one...
>
> I have a table ~18M rows with a 'timestamp with time zone' column. It's
> indexed thus:
>
> CREATE INDEX my_table_timestamp_idx
> ON my_table
> USING btree
> (zulu_timestamp);
>
> whenever I issue a
Hi All,
I *really* need some help with this one...
I have a table ~18M rows with a 'timestamp with time zone' column. It's
indexed thus:
CREATE INDEX my_table_timestamp_idx
ON my_table
USING btree
(zulu_timestamp);
whenever I issue a command like:
SELECT speed, digital_input_1, digital_in
On Sun, Jul 19, 2009 at 04:35:43AM -0500, Abraham, Danny wrote:
> We do need LATIN1.
>
> The error message is:
>
> initdb: encoding mismatch
> The encoding you selected (LATIN1) and the encoding that the
> selected locale uses (WIN1252) do not match. This would lead to
> misbehavior in various c
On Sun, Jul 19, 2009 at 10:16:17AM +0800, Phoenix Kiula wrote:
> If so, how can I check for them in my old database, which is 8.2.9?
> I'm now moving first to 8.3 (then to the 84).
>
> Really, PG absolutely needs a way to upgrade the database without so
> much data related downtime and all these s
Hello
2009/7/19 Sim Zacks :
> Are there any statistics generated as to how many times an object
> (table/view/function) is accessed?
>
SELECT * FROM pg_stat_all_tables;
regards
Pavel Stehule
> I would like to know what in the database system is not being used.
>
>
> Thank you
>
> Sim
>
>
> --
>
Are there any statistics generated as to how many times an object
(table/view/function) is accessed?
I would like to know what in the database system is not being used.
Thank you
Sim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
h
We do need LATIN1.
The error message is:
initdb: encoding mismatch
The encoding you selected (LATIN1) and the encoding that the
selected locale uses (WIN1252) do not match. This would lead to
misbehavior in various character string processing functions.
Rerun initdb and either do not specify an
50 matches
Mail list logo
