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
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
doy =
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 selectivity.
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 string
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
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 Annotated
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 correctly
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( doy
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
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)...
The
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 the
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 serviceinstance
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
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.
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 -
From:
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 ' in
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 or
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 || ' ' ||
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
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
One thing you
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 runs WHERE
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 then,
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.
No, it's
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
Hi,
Ive looked around through the docs, but cant
seem to find an answer to this. If
I change a columns 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 Ive
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 that
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
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
=?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 have a
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 on
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
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
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
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=19pid=28
[quote]
The best reason to use an index is for joining
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
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
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 could
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
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].
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
script?
Set[1]
=?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 slower
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
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
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
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
45 matches
Mail list logo
