Sorry for late response, but may be you are still strugling.
It can be that some query(s) use a lot of work mem, either because of high
work_mem setting or because of planner error. In this case the moment query
runs it will need memory that will later be returned and become free.
Usually this
Понеділок, 9 липня 2012 р. користувач Misa Simic misa.si...@gmail.com
написав:
2012/7/9 Gregg Jaskiewicz gryz...@gmail.com
Use cursors.
By far the most flexible. offset/limit have their down sides.
Well, I am not aware what down sides there are in LIMIT OFFSET what does
not exist in any
Понеділок, 9 липня 2012 р. користувач Misa Simic misa.si...@gmail.com
написав:
2012/7/9 Gregg Jaskiewicz gryz...@gmail.com
Use cursors.
By far the most flexible. offset/limit have their down sides.
Well, I am not aware what down sides there are in LIMIT OFFSET what does
not exist in any
What language are you using? Usually there is iterator with chunked fetch
option (like setFetchSize in java jdbc). So you are passing query without
limit and then read as many results as you need. Note that query plan in
this case won't be optimized for your limit and I don't remember if
postgres
Hello.
Today I've found a query that I thought will be fast turned out to be slow.
The problem is correlated exists with join - it does not want to make
correlated nested loop to make exists check.
Even if I force it to use nested loop, it materialized join uncorrelated
and then filters it. It's
Glad to hear postgresql becomes better and better :)
2012/6/18 Tom Lane t...@sss.pgh.pa.us
=?KOI8-U?B?96bUwcymyiD0yc3eydvJzg==?= tiv...@gmail.com writes:
Today I've found a query that I thought will be fast turned out to be
slow.
The problem is correlated exists with join - it does not
Hello, all.
We've reached to the point when we would like to try SSDs. We've got a
central DB currently 414 GB in size and increasing. Working set does not
fit into our 96GB RAM server anymore.
So, the main question is what to take. Here what we've got:
1) Intel 320. Good, but slower then current
2012/5/11 Robert Klemme shortcut...@googlemail.com
On Fri, May 11, 2012 at 12:50 PM, Vidhya Bondre meetvbon...@gmail.com
wrote:
Is there any max limit set on sequences that can be created on the
database
? Also would like to know if we create millions of sequences in a single
db
what is
How about
with par_ as (select * from product_parent where parent_name like 'aa%' )
select distinct product_code from product p_
inner join par_ on p_.parent_id=par_.id
limit 2
?
2012/4/3 Istvan Endredy istvan.endr...@gmail.com
Hi,
i've ran into a planning problem.
select distinct
2012/4/16 Tomek Walkuski tomek.walku...@gmail.com
Hello group!
I have query like this:
SELECT
employments.candidate_id AS candidate_id,
SUM(TS_RANK(employers.search_vector, TO_TSQUERY('simple', 'One:* |
Two:* | Three:* | Four:*'), 2)) AS ts_rank
FROM
employments
INNER JOIN
Few words regarding small inserts and a lot of fsyncs:
If it is your problem, you can fix this by using battery-backed raid card.
Similar effect can be reached by turning synchronious commit off. Note
that the latter may make few last commits lost in case of sudden reboot.
But you can at least
'
27.12.2011 20:13 пользователь Tom Lane t...@sss.pgh.pa.us написал:
Jim Crate jim...@gmail.com writes:
My question is why does it do a seq scan when it flattens this
subquery into a JOIN?
Because it thinks there will be 3783 rows out of the msg scan, which if
true would make your desired
17.12.2011 18:25 пользователь Filip Rembiałkowski plk.zu...@gmail.com
написал:
Normally there is no chance it could work,
because (a) the planner does not know all possible values of a column,
and (b) btree indexes cannot search on not equal operator.
Why so? ab is same as (ab or ab), so,
In my tests it greatly depends on if index writes are random or sequential.
My test time goes down from few hours to seconds if I add to the end of
index.
As for me, best comparision would be to make two equal int4 columns with
same data as in int8, two indexes, then perform the test. My bet it
2011/4/23 Robert Haas robertmh...@gmail.com
On Apr 18, 2011, at 1:13 PM, Uwe Bartels uwe.bart...@gmail.com wrote:
Hi Robert,
thanks for your answer.
the aggregate function I was talking about is the function I need to use
for the non-group by columns like min() in my example.
There
2011/3/23 Tom Lane t...@sss.pgh.pa.us
Claudio Freire klaussfre...@gmail.com writes:
On Wed, Mar 23, 2011 at 5:29 PM, Josh Berkus j...@agliodbs.com wrote:
On 3/23/11 10:35 AM, Claudio Freire wrote:
* consider plan bailout: execute a tempting plan, if it takes too
long or its effective
27 лютого 2011 р. 19:59 Robert Haas robertmh...@gmail.com написав:
2011/2/4 Віталій Тимчишин tiv...@gmail.com:
Hi, all.
All this optimizer vs hint thread reminded me about crazy idea that got
to
my head some time ago.
I currently has two problems with postgresql optimizer
1
2011/2/10 Tobias Brox tobi...@gmail.com
On 4 February 2011 04:46, Josh Berkus j...@agliodbs.com wrote:
Optimizer hints are used to work around problems in the optimizer and
introduce upgrade and maintenance issues. We would rather have the
problems reported and fixed. We have discussed a
2011/2/4 Robert Haas robertmh...@gmail.com
On Fri, Feb 4, 2011 at 4:19 AM, Vitalii Tymchyshyn tiv...@gmail.com
wrote:
Why do you expect such a invasive code changes? I know little about
postgresql code layering, but what I propose (with changing delete to
truncate) is:
1) Leave tuple
Hi, all.
All this optimizer vs hint thread reminded me about crazy idea that got to
my head some time ago.
I currently has two problems with postgresql optimizer
1) Dictionary tables. Very usual thing is something like select * from
big_table where distionary_id = (select id from dictionary where
2011/2/3 da...@lang.hm
If the table is not large enough to fit in ram, then it will compete for
I/O, and the user will have to wait.
what I'm proposing is that as the records are created, the process doing
the creation makes copies of the records (either all of them, or some of
them if not
2011/2/4 Mladen Gogala mladen.gog...@vmsinfo.com
Josh Berkus wrote:
However, since this system wasn't directly compatible with Oracle Hints,
folks pushing for hints dropped the solution as unsatisfactory. This is
the discussion we have every time: the users who want hints specifically
want
4 лютого 2011 р. 09:32 da...@lang.hm написав:
when a copy command is issued, I assume that there is some indication of
how much data is going to follow. I know that it's not just 'insert
everything until the TCP connection terminates' because that would give you
no way of knowing if the
2011/1/28 Scott Carey sc...@richrelevance.com
On 1/28/11 9:28 AM, Stephen Frost sfr...@snowman.net wrote:
* Scott Marlowe (scott.marl...@gmail.com) wrote:
There's nothing wrong with whole table updates as part of an import
process, you just have to know to clean up after you're done, and
2011/1/18 masterchief esi...@theiqgroup.com
Tom Lane wrote:
The only really effective way the planner knows to optimize an
IN (sub-SELECT) is to turn it into a semi-join, which is not possible
here because of the unrelated OR clause. You might consider replacing
this with a UNION of
2010/12/7 Robert Haas robertmh...@gmail.com
On Tue, Dec 7, 2010 at 1:08 PM, Ivan Voras ivo...@freebsd.org wrote:
I'm not very familiar with PostgreSQL code but if we're
brainstorming... if you're only trying to protect against a small
number of expensive operations (like DROP, etc.) that
2010/12/4 Mathieu De Zutter math...@dezutter.org
For each page load I first create an entry in that table, e.g.:
INSERT INTO log_event (user_id, ip, action_id, object1_id, object2_id,
event_timestamp, comments) VALUES (1, '127.0.0.1', 96, null, null,
NOW(), 'TEST');
After that, I want to
2010/11/11 Robert Haas robertmh...@gmail.com
But thinking over what you've written here, I'm reminded of something
Peter said years ago, also about the optimizer. He was discussed the
ratio of the estimated cost to the actual cost and made an off-hand
remark that efforts had been made over
2010/10/10 Neil Whelchel neil.whelc...@gmail.com
On Saturday 09 October 2010 18:47:34 Scott Marlowe wrote:
On Sat, Oct 9, 2010 at 5:26 PM, Neil Whelchel neil.whelc...@gmail.com
wrote:
I know that there haven been many discussions on the slowness of
count(*)
even when an index is
Hello.
Today I've found out strange results for query below.
select version();
version
--
PostgreSQL 8.4.2 on amd64-portbld-freebsd8.0,
Hi.
Not strictly connected to your tests, but:
As of ZFS, we've had experience that it degrades over time after random
updates because of files becoming non-linear and sequential reads becomes
random.
Also there are Q about ZFS block size - setting it to 8K makes first problem
worse, setting it
2010/5/17 Jon Nelson jnelson+pg...@jamponi.netjnelson%2bpg...@jamponi.net
On Mon, May 17, 2010 at 5:10 AM, Pierre C li...@peufeu.com wrote:
- or use a JOIN delete with a virtual VALUES table
- or fill a temp table with ids and use a JOIN DELETE
What is a virtual VALUES table? Can you give
Hello.
I have a query that performs very poor because there is a limit on join
column that is not applied to other columns:
select * from company this_ left outer join company_tag this_1_ on
this_.id=this_1_.company_id left outer join company_measures companymea2_ on
16 квітня 2010 р. 11:31 Yeb Havinga yebhavi...@gmail.com написав:
Віталій Тимчишин wrote:
Hello.
I have a query that performs very poor because there is a limit on join
column that is not applied to other columns:
select * from company this_ left outer join company_tag this_1_ on
this_
16 квітня 2010 р. 11:25 Hannu Krosing ha...@2ndquadrant.com написав:
On Fri, 2010-04-16 at 11:02 +0300, Віталій Тимчишин wrote:
Hello.
I have a query that performs very poor because there is a limit on
join column that is not applied to other columns:
select * from company this_
16 квітня 2010 р. 16:21 Yeb Havinga yebhavi...@gmail.com написав:
Віталій Тимчишин wrote:
BTW: Changing slow query to inner joins do not make it fast
I'm interested to see the query andplan of the slow query with inner joins.
Here you are. The query:
select * from company this_ inner
2010/2/10 Tom Lane t...@sss.pgh.pa.us
Franck Routier franck.rout...@axege.com writes:
I am wondering if deferring foreign key constraints (instead of
disableing them) would increase performance, compared to non deferred
constraints
No, it wouldn't make any noticeable difference AFAICS.
2010/2/2 Mridula Mahadevan mmahade...@stratify.com
Hi,
I am running a bunch of queries within a function, creating some temp
tables and populating them. When the data exceeds say, 100k the queries
start getting really slow and timeout (30 min). when these are run outside
of a
30 січня 2010 р. 04:30 Greg Stark gsst...@mit.edu написав:
2010/1/28 Віталій Тимчишин tiv...@gmail.com
I've always thought that PostgreSQL would propagate constraint from
field1 to field2 if condition says field1=field2, but this does not seem the
case:
version?
PostgreSQL 8.3.7
Hello.
I've always thought that PostgreSQL would propagate constraint from field1
to field2 if condition says field1=field2, but this does not seem the case:
dict=# explain select * from domain_list,title.domains where processed_at is
not null and key=groupid and key 100 and groupid
2010/1/26 Matthew Wakeling matt...@flymine.org
On Tue, 26 Jan 2010, Richard Neill wrote:
SELECT SUM (case when id 120 and id 121 then 1 else 0 end)
from tbl_tracker;
Explain shows that this does a sequential scan.
I'd defer to Tom on this one, but really, for Postgres to work
27 січня 2010 р. 19:01 Matthew Wakeling matt...@flymine.org написав:
On Wed, 27 Jan 2010, Віталій Тимчишин wrote:
How about SELECT SUM (case when id 120 and id 121 then 1 end)
from tbl_tracker;
That is very interesting.
* All the functions should be noop for null input
How about
CREATE OR REPLACE VIEW value_codes_view AS
select * from (
SELECT value_codes.id_nbr,
value_codes.id_qfr,
(ARRAY[val_1_cd_1, ... , val_2_cd_12])[i] as value_code,
(ARRAY[val_1_amt_1, ... , val_2_amt_12])[i] as value_amount,
FROM value_codes, generate_series(1,24)
20 листопада 2009 р. 17:01 Jonathan Foy the...@gmail.com написав:
This seems to result in the same problem; should I attempt to pull for a
specific id_nbr/id_qfr, postgres uses the index without a problem. If I try
to join the two tables/views however, it insists on doing a sequential scan
2009/10/29 Andreas Hartmann andr...@apache.org
Hi everyone,
I want to model the following scenario for an online marketing application:
Users can create mailings. The list of recipients can be uploaded as
spreadsheets with arbitrary columns (each row is a recipient). I expect the
following
2009/10/14 Scott Marlowe scott.marl...@gmail.com
On Wed, Oct 14, 2009 at 1:20 AM, Pavel Stehule pavel.steh...@gmail.com
wrote:
2009/10/14 Thom Brown thombr...@gmail.com:
2009/10/14 Scott Marlowe scott.marl...@gmail.com:
Why not just do something like:
SELECT thisfield, thatfield
May be you have very bad disk access times (e.g. slow random access)? In
this case everything should be OK while data in cache and awful, when not.
Could you check disk IO speed IO wait while doing slow fast query.
BTW: In this case, increasing shared buffers may help. At least this will
2009/8/19 Kevin Kempter kev...@consistentstate.com
We do have an index on url_hits.time
not sure why timestamps were not used, I was not here for the design phase.
What's type of time column? I don't like it casts it to double in explain.
If it is integer, may be you need to change
and
27 липня 2009 р. 13:53 Robert Haas robertmh...@gmail.com написав:
Hmm. What you're suggesting here is that we could consider
implementing OR conditions by rescanning the inner side for each index
qual and then unique-ifying the results on the index column. That's
probably possible, but it
27 липня 2009 р. 15:02 Robert Haas robertmh...@gmail.com написав:
The problem, though, is that it won't ALWAYS be right to implement OR
using UNION, so you have to have some way of deciding which is better.
That's easy - you propose both ways to planner and it's up to it to decide.
Yes, it
27 липня 2009 р. 17:18 Tom Lane t...@sss.pgh.pa.us написав:
=?KOI8-U?B?96bUwcymyiD0yc3eydvJzg==?= tiv...@gmail.com writes:
Actually what I am talking about is to make OR with UNION (or UNION-like
because it's a little different depending on input rows uniqueness) as an
option. All of OR
2009/7/20 Robert James srobertja...@gmail.com
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?
It's not clever enough.
And how can I shut this off?
Use UNION/UNION ALL if
20 липня 2009 р. 11:02 Chris dmag...@gmail.com написав:
Віталій Тимчишин wrote:
2009/7/20 Robert James srobertja...@gmail.com mailto:
srobertja...@gmail.com
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
Sorry, it was an error in previous letter.
3 липня 2009 р. 14:22 Віталій Тимчишин tiv...@gmail.com написав:
2009/7/1 Mike Ivanov mi...@activestate.com
LEFT outer JOIN ville ON ville.uid = bien.ref_ville
LEFT outer JOIN freguesia_ville ON freguesia_ville.ref_ville =ville.uid
I'd prefer ALTER VIEW name SET ANALYZE=true; or CREATE/DROP ANALYZE SQL;
Also it should be possible to change statistics target for analyzed columns.
Such a statement would allow to analyze multi-table correlations. Note that
for view planner should be able to use correlation information even for
2009/6/2 Robert Haas robertmh...@gmail.com
On Mon, Jun 1, 2009 at 4:53 PM, Anne Rosset aros...@collab.net wrote:
On Mon, Jun 1, 2009 at 2:14 PM, Anne Rosset aros...@collab.net wrote:
SELECT SUM(1) FROM item WHERE is_deleted = 'f'; sum - 1824592
(1
row)
SELECT SUM(1) FROM item
2009/6/1 Koen Martens pg...@metro.cx
Now, when I split up the OR in two distinct queries, everything is nice and
fast. Both queries run in sub-second time.
Hi.
PostgreSQL simply do not like ORs (can't use indexes in this case), so
UNION/UNION ALL is your friend.
Best regards, Vitalii
OK, got to my postgres. Here you are:
create or replace function explode_array(in_array anyarray) returns setof
anyelement as
$$
select ($1)[s] from generate_series(1,array_upper($1, 1)) as s;
$$
language sql immutable;
SELECT s.* FROM score s
WHERE s.id IN (
select
-- Get the high
2009/4/9 Glenn Maynard glennfmayn...@gmail.com
(This is related to an earlier post on -sql.)
I'm querying for the N high scores for each game, with two tables:
scores and games.
CREATE TABLE game (id SERIAL NOT NULL PRIMARY KEY);
CREATE TABLE score (id SERIAL NOT NULL PRIMARY KEY, score
The outer nested join has the VALUES as the main loop, and the complicated
join as the leaf. So, the complicated overlap-finding join gets run twice.
That's weird. What do you have as statistics target? Planner is incorrect
few orders of magnitude, so increasing it may help.
BTW: One of
Hi.
Look, what I did mean by symmetric is that you don't need to make second
part of query because you will get just same results simply by
select
case when n == 1 then id1 else id2 end,
case when n == 2 then id1 else id2 end
from (
SELECT
l1.id AS id1,
l2.id AS id2
FROM
location l1,
Yeah, that's nice.
However, it is still the case that we can't trust the database to choose
the correct plan. It is currently only choosing the correct plan now by
chance, and some time later it may by chance switch to one that takes 40
minutes.
What is the bad plan? Is it like the first
Hello.
You could try addingAND l2.start l1.start to the first query. This
will drop symmetric half of intersections (the ones that will remain are l2
inside or to the left of l1), but you can redo results by
id1,id2 union all id2, id1 and may allow to use start index for between,
for my
2009/3/14 decibel deci...@decibel.org
On Mar 10, 2009, at 12:20 PM, Tom Lane wrote:
f...@redhat.com (Frank Ch. Eigler) writes:
For a prepared statement, could the planner produce *several* plans,
if it guesses great sensitivity to the parameter values? Then it
could choose amongst them at
2008/11/19 Stephan Szabo [EMAIL PROTECTED]
On Wed, 19 Nov 2008, [ISO-8859-5] Віталій Тимчишин wrote:
Query 1:
select * from t1 where id not in (select id from t2);
Query 2 (gives same result as Q1):
select * from t1 except all (select id from t2);
It gives the same result as long
2008/11/19 DANIEL CRISTIAN CRUZ [EMAIL PROTECTED]
Something weird with your example which doesn't have the same result, see
row count with explain analyze:
My fault. EXCEPT ALL would not work here, so this method with EXCEPT can be
used only when either operation is done on unique key on t1 or
2008/11/15 Tom Lane [EMAIL PROTECTED]
=?ISO-8859-5?B?svbi0Nv22SDC2Nzn2OjY3Q==?= [EMAIL PROTECTED] writes:
I am not. I can't see how materialize can multiply number of rows it gets
from sort by 100.
Is it the right-hand input of a merge join? If so you're looking at
mark/restore rescans,
Sorry, for delayed response - It was very busy week.
2008/11/7 David Wilson [EMAIL PROTECTED]
On Fri, Nov 7, 2008 at 4:14 AM, Віталій Тимчишин [EMAIL PROTECTED] wrote:
Merge Join (cost=518771.07..62884559.80 rows=1386158171 width=32)
(actual
time=30292.802..755751.242 rows=34749 loops=1
My main message is that I can see this in many queries and many times. But
OK, I can present exact example.
2008/11/5 Jeff Davis [EMAIL PROTECTED]
On Wed, 2008-11-05 at 13:12 +0200, Віталій Тимчишин wrote:
For a long time already I can see very poor OR performance in
postgres.
If one have
2008/11/6 Helio Campos Mello de Andrade [EMAIL PROTECTED]
For what i see in four OR-plan.txt tou are doing too much sequencial scan
. Create some indexes for those tables using the fields that you use an it
may help you.
OBS: If you already have lots of indexes in your tables it may be a
2008/11/6 Richard Huxton [EMAIL PROTECTED]
Віталій Тимчишин wrote:
As you can see from other plans, it do have all the indexes to perform
it's
work fast (when given part by part). It simply do not wish to use them.
My
question: Is this a configuration problem or postgresql optimizer
Hello.
For a long time already I can see very poor OR performance in postgres.
If one have query like select something from table where condition1 or
condition2 it may take ages to execute while
select something from table where condition1 and select something from
table where condition2 are
2008/10/17 Tory M Blue [EMAIL PROTECTED]
The real issue is my index growth and my requirement for weekly
re-indexing (which blocks and therefore is more or less a manual
process in a live production environment (fail over, changing vips
etc).
BTW: Can't you simply recreate indexes online?
BTW: Have just tried clean (without any foreign keys constraints)
peformance of
delete from tbl where field not in (select)
vs
create temporary table tmp(id) as select distinct field from tbl; delete
from tmp where id in (select); delete from tbl where field in (select id
from tmp).
both tbl and
OK, I did try you proposal and correlated subselect.
I have a database ~90 companies.
First try was to remove randomly selected 1000 companies
Uncorrelated subselect: 65899ms
Correlated subselect: 97467ms
using: 9605ms
my way: 104979ms. (without constraints recreate)
My is the worst because it
Hello.
I have a database with company table that have a bunch of related
(delete=cascade) tables.
Also it has 1-M relation to company_descr table.
Once we've found that ~half of our companies do not have any description and
we would like to remove them.
First this I've tried was
delete from
2008/10/2 Tom Lane [EMAIL PROTECTED]
=?ISO-8859-5?B?svbi0Nv22SDC2Nzn2OjY3Q==?= [EMAIL PROTECTED] writes:
delete from company where id not in (select company_id from
company_descr);
I've tried to analyze command, but unlike to other RDBM I've used it did
not
include cascade deletes/checks
77 matches
Mail list logo
