Hey John, and thanks for the input.
On Wed, Oct 16, 2013 at 11:00 AM, John Melesky wrote:
> On Wed, Oct 16, 2013 at 12:20 AM, Ken Tanzer wrote:
>
>> First and foremost (and primarily directed to people who are kind enough
>> to provide help on this list), is a script li
on, since
it would be the most portable and presumably available on all systems. I
haven't fully thought that through, so I'm wondering if anyone sees reasons
that wouldn't work, or if some other language would be a better or more
natural choice.
Cheers,
Ken
--
AGENCY Software
But I'd hate to be frowned on by the bashers (or bashed by the
frowners), so I changed them to lower case.
> All in all - looks pretty good.
>
>
Thanks!
Cheers,
Ken
--
AGENCY Software
A data system that puts you in control
100% Free Software
*http://agency-software.org/*
ken.t
>
> p.s., This script runs fine on my computer (Ubuntu 13.04), but on a
>> Fedora 11 machine it dies with
>>
>> pg_analyze_info.sh: line 18: unexpected EOF while looking for matching `)'
>> pg_analyze_info.sh: line 57: syntax error: unexpected end of file
>>
>> If anyone knows why, or encounters
s for any views or custom functions--I don't think they're in the
analyze output, but if they are please let me know.
Any comments or suggestions for improvement would be most welcome. Thanks.
Ken
p.s., This script runs fine on my computer (Ubuntu 13.04), but on a Fedora
11 machi
This is a known limitation of partitioning. One solution is to use a
recursive stored proc, which can use indexes. Such a solution is
discussed here:
http://archives.postgresql.org/pgsql-performance/2009-09/msg00036.php
Regards,
Ken
http://archives.postgresql.org/pgsql-performance/2009-09
a CLUSTER the same whether one row
is out of place or the table is completely disordered?
Thanks,
Ken
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED]
he same whether one row is out of place or the table is completely disordered?
Thanks,
Ken
Discover Yahoo!
Find restaurants, movies, travel & more fun for the weekend. Check it out!
Richard,
What do you mean by summary table? Basically a cache of the query into a
table with replicated column names of all the joins? I'd probably have to
whipe out the table every minute and re-insert the data for each carrier in
the system. I'm not sure how expensive this operation would b
after all of this time but we are still
using the servers we started with.
But remember, the biggest bottleneck is almost *always* the I/O. So put
more & faster disks into the system first.
I will price that raid setup you recommended. That will probably be the
first adjustment to our server if we don't just replace the entire thing.
Thanks again,
Ken
---(end of broadcast)---
TIP 8: explain analyze is your friend
Josh,
I did everything you said and my query does perform a bit better. I've been
getting speeds from 203 to 219 to 234 milliseconds now. I tried increasing
the work mem and the effective cache size from the values you provided, but
I didn't see any more improvement. I've tried to looking in
I'm not sure what the answer is but maybe I can help? Would clustering the
name index make this faster? I thought that would bunch up the pages so the
names were more or less in order, which would improve search time. Just a
guess though.
Ken
- Original Message -
From: &quo
2) Force PG to drop the merge join via SET ENABLE_MERGEJOIN = FALSE;
Actually, it was 312 milliseconds, so it got worse.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
eries on is
just a simple Athon XP 2100+ on WinXP with 1GB of RAM. The production
server is a faster P4, but the rest is the same. Here are the 4 values in
my configuration, but 3 of them were commented:
shared_buffers = 1000
#work_mem = 1024
#effective_cache_size = 1000
#rando
more riskier than mine.
I'm not sure why this query is any different. Is there a configuration
setting I can use to make things speed up perhaps?
Anyhow, thanks for taking the time helping me out John. I'm going to play
with more sub-selects and see if I find a combination that work
e still in this query after a few days).
I guess I'm wondering if tuning the query is futile and I should get the
requirements changed, or is there something I can do to really speed it up?
Thanks again,
Ken
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
I took John's advice and tried to work with sub-selects. I tried this
variation, which actually seems like it would make a difference
conceptually since it drives on the person table quickly. But to my
surprise, the query runs at about 375 milliseconds. I think it's because
it's going over t
If so, I'd have to take the EXPLAIN ANALYZE results with a big grain of
salt, because what it's trying to do is add up a lot of
mostly-sub-millisecond intervals. What would essentially happen is that
whichever plan node had control at a particular millisecond boundary
would get charged for the who
"Ken Egervari" <[EMAIL PROTECTED]> writes:
Okay, here is the explain analyze I managed to get from work.
What platform is this on? It seems very strange/fishy that all the
actual-time values are exact integral milliseconds.
regards, tom lane
My machine is WinXP professional, atho
000 rows=0 loops=1)
-> Index Scan using
carrier_to_person_person_id_idx on carrier_to_person ctp (cost=0.00..3.04
rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=1)
Index Cond: (355 =
person_id)
Total runtime: 312.000 ms
Ken
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
left join is for eager loading so that I don't have to run a seperate
query
to fetch the children for each shipment. This really does improve
performance because otherwise you'll have to make N+1 queries to the
database, and that's just too much overhead.
are you saying that you are actually doing
ered/joined. Is there anyway to force
postgres to pick person? The reason I ask is because this would really
reduce the number of rows it pulls out from the shipment table.
Thanks for comments. I'll try making that date explicit and change the
query to use between to see if that does anyth
status_shipment_id_idx ON shipment_status USING btree
(shipment_id);
Thanks for your responses everyone. I'll try and get you that explain
analyze. I'm just not at work at the moment but this is a problem that I'm
simply puzzled and worried about. I'm getting all of this from CVS on my
work server.
Ken
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
; (0.125)
cs.date >= current_date - 31 (.10)
p.id = ? (0.003)
s.is_purged = false (.98)
I really hope someone can help since I'm pretty
much stuck.
Best regards and many thanks,
Ken
Thanks again for your response. I'll try and clarify some metrics that I
took a few days to figure out what would be the best join order.
By running some count queries on the production database, I noticed there
were only 8 rows in release_code. The filtered column is unique, so that
means th
Well, postgres does what you asked. It will be slow, because you have a
full table join. LIMIT does not change this because the rows have to be
sorted first.
I am aware that limit doesn't really affect the execution time all that
much. It does speed up ORM though and keeps the rows to a managea
Yes, I'm very well aware of VACUUM and VACUUM
ANALYZE. I've even clusted the date index and so on to ensure faster
performance.
- Original Message -
From:
David
Parker
To: Ken Egervari ; pgsql-performance@postgresql.org
Sent: Saturday, January 29, 2005
? I'm not really interested in this specific
case (I just made it up). I'm more interested in general solutions to this
general problem of big table sizes with bad filters and where join orders don't
seem to help much.
Thank you very much for your
help.
Best Regards,
Ken Egervari
On Fri, 2005-01-21 at 08:14, Greg Stark wrote:
> ken <[EMAIL PROTECTED]> writes:
>
> > >From my understanding, all the data for these columns in all the child
> > tables will be stored in this one parent table
>
> No, all the data is stored in the child ta
it and child
B has 2 rows in it. Will a query on child table B be slowed down by the
fact that it inherits from the same table as A. I'm sure the answer is
absolutely yes, and so I guess I'm just looking for corroboration.
Maybe I'll be surprised!
Thanks a bunch,
Ken
---
On Wed, 2004-10-13 at 02:21, Robin Ericsson wrote:
> Hi,
>
> I sent this to general earlier but I was redirected to performance.
>
> The query have been running ok for quite some time, but after I did a
> vacuum on the database, it's very very slow.
Did you do a VACUUM FULL ANALYZE on the datab
lseg()) condition and voila it would be
done. However, even if I *drop* the rtree index on the boundingbox
column, so that it can't use that index, the optimizer does not choose
the other index. Instead it reverts to doing a sequential scan of the
entire table and its really slow.
Again, sorr
by two orders of magnitude in guessing how many rows there are going to
be in this case and thus is not using my index. How can I fix that?
Ken
On Wed, 2004-06-09 at 14:29, Rod Taylor wrote:
> > ... and here is the plan with statistics set to 1000 ...
> >
> > Seq Scan on nr
On Wed, 2004-06-09 at 13:56, Rod Taylor wrote:
> On Wed, 2004-06-09 at 16:50, ken wrote:
> > Thanks Rod,
> >
> > This setting has no effect however. If I set statistics to 1000, or
>
> Okay.. but you never did send EXPLAIN ANALYZE output. I want to know
> what it
Thanks Rod,
This setting has no effect however. If I set statistics to 1000, or
even 0, (and then reanalyze the table) I see no change in the behaviour
of the query plans. i.e. there is still the odd transtion in the plans
at diagonalSize = 50.
Ken
On Wed, 2004-06-09 at 13:12, Rod Taylor
rimary key index is used at this same
diagonalSize cutoff and the 5-part double precision clause is used as a
filter to the index scan and the result is again a very slow query.
I can provide those queries and results but that would only complicate
this already lengthy email and the above see
I'm running 7.4.2 on an older Linux box (450MHzAMD K-6-III, 450M RAM)
running kernel 2.6.5. My client is a Java/JDBC program on Windows.
I'm having trouble seeing where the bottleneck in my performance is.
The client uses about 30% CPU. The server uses 70% CPU plus 1.5% I/O
wait. The I/O wai
Richard Huxton wrote:
On Tuesday 06 April 2004 21:25, Ken Geis wrote:
I am trying to find an efficient way to draw a random sample from a
complex query. I also want it to be easy to use within my application.
So I've defined a view that encapsulates the query. The id in the
"driving
ld be that very
helpful. I'm considering just making a sample table and creating an
analogous view around that. I'd like to be able to keep this as simple
as possible though.
Ken
---(end of broadcast)---
TIP 2: you can get off all l
index scan method
took maybe 6.5x as long, but the estimate was that it would take 1400x
as long. I think instead of trying out his patch I might actually work
on my application!
Ken
---(end of broadcast)---
TIP 7: don't forget to increase your
Bruno Wolff III wrote:
I haven't come up with any great ideas for this one. It might be interesting
to compare the explain analyze output from the distinct on query with
and without seqscans enabled.
Can't do that comparison. Remember, with seqscan it fails. (Oh, and
that nested loops solution I
ober?
http://members.aon.at/pivot/pg/16-correlation.diff
If so, I'll try it out and report my results.
Ken
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so
need to
go through the table once and don't have to hit the index blocks.
It is still odd that you didn't get a big speed up for just the min though.
I found I'm suffering from an effect detailed in a previous thread titled
Does "correlation" mislead the o
Ken Geis wrote:
When run on 7.3.3, forcing an index scan by setting
enable_seqscan=false, the query took 55 minutes to run. The index is
about 660M in size, and the table is 1G. As I mentioned before, with
table scans enabled, it bombs, running out of temporary space.
Man, I should wait a
Ken Geis wrote:
I went through the code (7.4 beta2) that estimates the cost of an index
scan path. What I need to be sure of is that when running a query in
pgsql that uses only the columns that are in an index, the underlying
table need not be accessed. I know that Oracle does this.
Thinking
scan the index and load a random page for each entry
Ken
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
ly had performance issues with pgsql before. And I've been
running this database for a couple of years now, but I haven't run these
queries against it.
Ken
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Bruno Wolff III wrote:
On Thu, Aug 28, 2003 at 20:46:00 -0700,
Ken Geis <[EMAIL PROTECTED]> wrote:
It is not the table or the query that is wrong. It is either the db
parameters or the optimizer itself.
...
It is still odd that you didn't get a big speed up for just the min though.
Bruno Wolff III wrote:
On Thu, Aug 28, 2003 at 20:00:32 -0700,
Ken Geis <[EMAIL PROTECTED]> wrote:
Bruno Wolff III wrote:
Not according to the optimizer! Plus, this is not guaranteed to return
the correct results.
For it to be fast you need an index on (stock_id, price_date) so that
y
table. And yes, I *am* analyzed!
The answers are guarenteed to be correct. See:
http://developer.postgresql.org/docs/postgres/sql-select.html#SQL-DISTINCT
That's good to know. Thanks!
Ken
---(end of broadcast)---
TIP 2: you can get off all
Bruno Wolff III wrote:
On Thu, Aug 28, 2003 at 17:10:31 -0700,
Ken Geis <[EMAIL PROTECTED]> wrote:
The query I want to run is
select stock_id, min(price_date) from day_ends group by stock_id;
The fast way to do this is:
select distinct on (stock_id) stock_id, price_date
order by st
e same way? Are MIN and MAX used often enough to justify special
treatment, and could that be cleanly implemented? Perhaps the aggregate
function can request the data in a certain order, be told that it is
being passed data in a certain order, and return before seeing the
entire set of data.
52 matches
Mail list logo
