K C Lau <[EMAIL PROTECTED]> writes:
> Thanks. The side effect is that it would suppress other notices which might
> be useful.
There's been some discussion of subdividing the present "notice"
category into two subclasses, roughly defined as "only novices wouldn't
know this" and "maybe this is int
Mark Liberman <[EMAIL PROTECTED]> writes:
> I've got a set-returning function, defined as STABLE, that I reference twice
> within a single query, yet appears to be evaluated via two seperate function
> scans.
There is no guarantee, express or implied, that this won't be the case.
(Seems like we
When grilled further on (Wed, 11 Jan 2006 10:33:03 -0500),
Tom Lane <[EMAIL PROTECTED]> confessed:
> The planner understands about transitivity of equality, ie given a = b
> and b = c it can infer a = c. It doesn't do any such thing for
> inequalities though, nor does it deduce f(a) = f(b) for ar
At 09:26 06/01/12, you wrote:
On Jan 12, 2006, at 9:36 , K C Lau wrote:
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"cities_pkey" for table "cities"
Is there a way to suppress this notice when I create tables in a
script?
Set[1] your log_min_messages to WARNING or higher
=?iso-8859-1?Q?Jean-Philippe_C=F4t=E9?= <[EMAIL PROTECTED]> writes:
> Thanks a lot for this info, I was indeed exceeding the genetic
> optimizer's threshold. Now that it is turned off, I get
> a very stable response time of 435ms (more or less 5ms) for
> the same query. It is about three times slo
On Thu, Jan 12, 2006 at 10:26:58AM +0900, Michael Glaesemann wrote:
> On Jan 12, 2006, at 9:36 , K C Lau wrote:
> >>NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> >>"cities_pkey" for table "cities"
> >
> >Is there a way to suppress this notice when I create tables in a
> >scri
On Jan 12, 2006, at 9:36 , K C Lau wrote:
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"cities_pkey" for table "cities"
Is there a way to suppress this notice when I create tables in a
script?
Set[1] your log_min_messages to WARNING or higher[2].
[1](http://www.postgre
At 07:21 06/01/12, Michael Fuhr wrote:
On Wed, Jan 11, 2006 at 02:38:42PM -0800, Burak Seydioglu wrote:
> I do a load of sql joins using primary and foreign keys. What i would like
> to know if PostgreSQL creates indexes on these columns automatically (in
> addition to using them to maintain refe
On Tue, 2006-01-10 at 17:21 -0500, Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> > I think its OK to use the MCV, but I have a problem with the current
> > heuristics: they only work for randomly generated strings, since the
> > selectivity goes down geometrically with length.
>
> We
Hi,
I've got a set-returning function, defined as STABLE, that I reference twice
within a single query, yet appears to be evaluated via two seperate function
scans. I created a simple query that calls the function below and joins the
results to itself (Note: in case you wonder why I'd do suc
Hi,
I'm working on a project, whose implementation deals with PostgreSQL. A brief description of our application is given below.
I'm running version 8.0 on a dedicated server 1Gb of RAM.
my database isn't complex, it contains just 2 simple tables.
CREATE TABLE cookies (
domain varchar
How about the performance effect on SELECT statements joining multiple tables (LEFT JOINS)?
I have been reading all day and here is an excerpt from one article
that is located at
http://pgsql.designmagick.com/tutorial.php?id=19&pid=28
[quote]
The best reason to use an index is for joining multipl
If this is a query that will be executed more than once, you can also
avoid incurring the planning overhead multiple times by using PREPARE.
-- Mark Lewis
On Wed, 2006-01-11 at 18:50 -0500, Jean-Philippe Côté wrote:
> Thanks a lot for this info, I was indeed exceeding the genetic
> optimizer's th
Thanks a lot for this info, I was indeed exceeding the genetic
optimizer's threshold. Now that it is turned off, I get
a very stable response time of 435ms (more or less 5ms) for
the same query. It is about three times slower than the best
I got with the genetic optimizer on, but the overall aver
On Wed, 2006-01-11 at 16:37, Jean-Philippe Côté wrote:
> Hi,
>
> I'm running version 8.1 on a dedicated Sun v20 server (2 AMD x64's)
> with 4Gb of RAM. I have recently noticed that the performance of
> some more complex queries is extremely variable and irregular.
> For example, I currently have a
On Wed, Jan 11, 2006 at 02:38:42PM -0800, Burak Seydioglu wrote:
> I do a load of sql joins using primary and foreign keys. What i would like
> to know if PostgreSQL creates indexes on these columns automatically (in
> addition to using them to maintain referential integrity) or do I have to
> crea
Burak Seydioglu <[EMAIL PROTECTED]> writes:
> I do a load of sql joins using primary and foreign keys. What i would like
> to know if PostgreSQL creates indexes on these columns automatically (in
> addition to using them to maintain referential integrity) or do I have to
> create an index manually
=?iso-8859-1?Q?Jean-Philippe_C=F4t=E9?= <[EMAIL PROTECTED]> writes:
> I'm running version 8.1 on a dedicated Sun v20 server (2 AMD x64's)
> with 4Gb of RAM. I have recently noticed that the performance of
> some more complex queries is extremely variable and irregular.
> For example, I currently ha
I do a load of sql joins using primary and foreign keys. What i would
like to know if PostgreSQL creates indexes on these columns
automatically (in addition to using them to maintain referential
integrity) or do I have to create an index manually on these columns as
indicated below?
CREATE TABLE c
Hi,
I'm running version 8.1 on a dedicated Sun v20 server (2 AMD x64's)
with 4Gb of RAM. I have recently noticed that the performance of
some more complex queries is extremely variable and irregular.
For example, I currently have a query that returns a small number
of rows (5) by joining a dozen
On Wed, Jan 11, 2006 at 04:05:18PM -0600, Dave Dutcher wrote:
> I've looked around through the docs, but can't seem to find an answer to
> this. If I change a column's statistics with "Alter table alter column
> set statistics n", is there a way I can later go back and see what the
> number is for
Hi,
I’ve looked around through the docs, but can’t
seem to find an answer to this. If
I change a column’s statistics with “Alter table alter column set
statistics n”, is there a way I can later go back and see what the number
is for that column? I want to be
able to tell which columns
The sort is definitively the culprit. When I removed it the query was
instant. I tried setting work_mem = 131072 but it did not seem to
help. I really don't understand this :-( Any other ideas?
Thanks!
On Jan 11, 2006, at 9:23 PM, Jim C. Nasby wrote:
I'd try figuring out if the join is th
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> On Wed, Jan 11, 2006 at 09:39:47PM +0100, Andrea Arcangeli wrote:
>> The index only helps the above query with = 0 and not the one with != 0,
>> but it seems not needed in practice.
> I suspect this is because of a lack of stats for functional indexes.
On Wed, Jan 11, 2006 at 09:39:47PM +0100, Andrea Arcangeli wrote:
> > CREATE INDEX indexname ON tablename ( position(' PREEMPT ' in
> > kernel_version) );
>
> The index only helps the above query with = 0 and not the one with != 0,
> but it seems not needed in practice.
Hrm. If you need indexing
On Wed, Jan 11, 2006 at 09:39:47PM +0100, Andrea Arcangeli wrote:
> On Wed, Jan 11, 2006 at 12:40:32PM -0600, Jim C. Nasby wrote:
> > On Tue, Jan 10, 2006 at 02:44:47AM +0100, Andrea Arcangeli wrote:
> > > "cooperative" runs "WHERE kernel_version NOT LIKE '%% PREEMPT %%'", while
> > > "preempt" run
On Wed, Jan 11, 2006 at 12:40:32PM -0600, Jim C. Nasby wrote:
> On Tue, Jan 10, 2006 at 02:44:47AM +0100, Andrea Arcangeli wrote:
> > "cooperative" runs "WHERE kernel_version NOT LIKE '%% PREEMPT %%'", while
> > "preempt" runs "WHERE kernel_version LIKE '%% PREEMPT %%'. The only
> > difference
>
I'd try figuring out if the join is the culprit or the sort is (by
dropping the ORDER BY). work_mem is probably forcing the sort to spill
to disk, and if your drives are rather busy...
You might also get a win if you re-order the joins to people, contacts,
addresses, if you know it will have the s
Yes, the rowcount estimates are real, however, it has been a long
time since the last VACUUM FULL (there is never a good time).
I have clustered the tables, reindexed, analyzed, vacuumed and the
plan now looks like this:
no_people=# explain SELECT r.id AS r_id, r.firstname || ' ' ||
r.l
On Wed, Jan 11, 2006 at 11:44:58AM -0500, Pallav Kalva wrote:
Some view you've got there... you might want to break that apart into
multiple views that are a bit easier to manage.
service_instance_with_status is a likely candidate, for example.
> View Definition
> ---
>
> create o
On Tue, Jan 10, 2006 at 02:44:47AM +0100, Andrea Arcangeli wrote:
> "cooperative" runs "WHERE kernel_version NOT LIKE '%% PREEMPT %%'", while
> "preempt" runs "WHERE kernel_version LIKE '%% PREEMPT %%'. The only difference
One thing you could do is change the like to:
WHERE position(' PREEMPT ' i
Did you originally post some problem queries? The settings look OK,
though 1G of memory isn't very much now-a-days.
On Mon, Jan 09, 2006 at 09:56:52AM +0200, Andy wrote:
> shared_buffers = 10240
> effective_cache_size = 64000
> RAM on server: 1Gb.
>
> Andy.
>
> - Original Message -
>
On Wed, Jan 11, 2006 at 08:02:37AM -0700, Robert Creager wrote:
> The query is wrong as stated, as it won't work when the interval
> crosses a year boundary, but it's a stop gap for now.
Yeah, I realized that shortly after I posted the original and posted
a correction.
http://archives.postgresql.
Hi Tom,
Thanks! for your input, the view was written first without using
the function but its an ugly big with all the joins and its much slower
that way. Below is the view without the function and its explain analzye
output , as you can see the it takes almost 2 min to run this query with
Pallav Kalva <[EMAIL PROTECTED]> writes:
>I am having problem optimizing this query,
Get rid of the un-optimizable function inside the view. You've
converted something that should be a join into an unreasonably large
number of function calls.
> -> Seq Scan on serviceinst
Bendik Rognlien Johansen <[EMAIL PROTECTED]> writes:
> Has anyone got any tips for speeding up this query? It currently
> takes hours to start.
Are the rowcount estimates close to reality? The plan doesn't look
unreasonable to me if they are. It might help to increase work_mem
to ensure that t
Robert Creager <[EMAIL PROTECTED]> writes:
> What I had thought is that PG would (could?) be smart enough to realize tha=
> t one query was restricted, and apply that restriction to the other based o=
> n the join. I know it works in other cases (using indexes on both tables u=
> sing the join)...
Hi ,
I am having problem optimizing this query, Postgres optimizer uses a
plan which invloves seq-scan on a table. And when I choose a option to
disable seq-scan it uses index-scan and obviously the query is much faster.
All tables are daily vacummed and analyzed as per docs.
Why cant p
When grilled further on (Wed, 11 Jan 2006 07:26:59 -0700),
Robert Creager <[EMAIL PROTECTED]> confessed:
>
> weather-# SELECT *, unmunge_time( time_group ) AS time,
> weather-# EXTRACT( doy FROM unmunge_time( time_group ) )
> weather-# FROM minute."windspeed"
> weather-# JOIN doy_agg ON( EXTRACT(
When grilled further on (Wed, 11 Jan 2006 00:56:55 -0700),
Michael Fuhr <[EMAIL PROTECTED]> confessed:
> On Tue, Jan 10, 2006 at 10:10:55PM -0700, Robert Creager wrote:
> > The query is now correct, but still is slow because of lack of
> > index usage. I don't know how to structure the query corr
People:
> All of these recent threads about fastest hardware and "who's better than
> who" has inspired me to create a new website:
>
> http://www.dbtuning.org
Well, time to plug my web site, too, I guess:
http://www.powerpostgresql.com
I've got a configuration primer up there, and the 8.0 Annot
Hello!
Has anyone got any tips for speeding up this query? It currently
takes hours to start.
PostgreSQL v8.x on (SuSe Linux)
Thanks!
no_people=# explain SELECT r.id AS r_id, r.firstname || ' ' ||
r.lastname AS r_name, ad.id AS ad_id, ad.type AS ad_type, ad.address
AS ad_address, ad.post
Tom Lane wrote:
Alessandro Baretta <[EMAIL PROTECTED]> writes:
I have no clue as to how or why the statistics were wrong
yesterday--as I vacuum-analyzed continuously out of lack of any better
idea--and I was stupid enough to re-timestamp everything before
selecting from pg_stats.
Too bad. I
On Wed, Jan 11, 2006 at 09:07:45AM +, Simon Riggs wrote:
> I would suggest we do this only when all of these are true
> - when accessing more than one table, so the selectivity could effect a
> join result
FWIW my problem only happens if I join: on the main table where the
kernel_version strin
On Tue, 2006-01-10 at 22:40 -0500, Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> > I meant use the same sampling approach as I was proposing for ANALYZE,
> > but do this at plan time for the query. That way we can apply the
> > function directly to the sampled rows and estimate select
On Wed, Jan 11, 2006 at 12:56:55AM -0700, Michael Fuhr wrote:
> WHERE ...
> AND doy >= EXTRACT(doy FROM now() - '24 hour'::interval)
> AND doy <= EXTRACT(doy FROM now())
To work on 1 Jan this should be more like
WHERE ...
AND (doy = EXTRACT(doy FROM now() - '24 hour'::interval) OR
do
On Tue, Jan 10, 2006 at 10:46:53AM -0500, Tom Lane wrote:
> Not with that data, but maybe if you increased the statistics target for
> the column to 100 or so, you'd catch enough values to get reasonable
> results.
Sorry, I'm not expert with postgresql, could you tell me how to increase
the statis
47 matches
Mail list logo
