On 3/30/09 2:34 PM, "d...@sidhe.org" wrote:
>> d...@sidhe.org escribió:
>>
So what's the "?" in the query you pasted earlier?
>>>
>>> The first ? (for architecture) is 1, the second ? (for branchid) is 0.
>>> They both should get passed to Postgres as $1 and $2, respectively,
>>> assuming
d...@sidhe.org escribió:
> Fair enough. (And sorry about the mis-read) Next time this occurs I'll try
> and duplicate this in psql. FWIW, a quick read of the C underlying the
> DBD::Pg module shows it using PQexecPrepared, so I'm pretty sure it is
> using prepared statements with placeholders, but
> d...@sidhe.org escribió:
>
>> > So what's the "?" in the query you pasted earlier?
>>
>> The first ? (for architecture) is 1, the second ? (for branchid) is 0.
>> They both should get passed to Postgres as $1 and $2, respectively,
>> assuming DBD::Pg does its substitution right. (They're both sup
d...@sidhe.org escribió:
> > So what's the "?" in the query you pasted earlier?
>
> The first ? (for architecture) is 1, the second ? (for branchid) is 0.
> They both should get passed to Postgres as $1 and $2, respectively,
> assuming DBD::Pg does its substitution right. (They're both supposed t
> d...@sidhe.org escribió:
>> > d...@sidhe.org escribió:
>> >
>> >> where libinstance.libdate <= 1238445044
>> >>and libinstance.enddate > 1238445044
>> >>and libinstance.libinstanceid = libobject.libinstanceid
>> >>and libinstance.architecture = ?
>> >
>> > How are you generating the e
d...@sidhe.org escribió:
> > d...@sidhe.org escribió:
> >
> >> where libinstance.libdate <= 1238445044
> >>and libinstance.enddate > 1238445044
> >>and libinstance.libinstanceid = libobject.libinstanceid
> >>and libinstance.architecture = ?
> >
> > How are you generating the explain? M
> d...@sidhe.org escribió:
>
>> where libinstance.libdate <= 1238445044
>>and libinstance.enddate > 1238445044
>>and libinstance.libinstanceid = libobject.libinstanceid
>>and libinstance.architecture = ?
>
> How are you generating the explain? My bet is that you're just
> substituting
d...@sidhe.org escribió:
> where libinstance.libdate <= 1238445044
>and libinstance.enddate > 1238445044
>and libinstance.libinstanceid = libobject.libinstanceid
>and libinstance.architecture = ?
How are you generating the explain? My bet is that you're just
substituting a literal in
> On Mon, Mar 30, 2009 at 4:02 PM, wrote:
>>> On Mon, Mar 30, 2009 at 1:42 PM, wrote:
> On Mon, Mar 30, 2009 at 12:42 PM, wrote:
>> Arguably in this case the actual query should run faster than the
>> EXPLAIN
>> ANALYZE version, since the cache is hot. (Though that'd only like
On Mon, Mar 30, 2009 at 4:02 PM, wrote:
>> On Mon, Mar 30, 2009 at 1:42 PM, wrote:
On Mon, Mar 30, 2009 at 12:42 PM, wrote:
> Arguably in this case the actual query should run faster than the
> EXPLAIN
> ANALYZE version, since the cache is hot. (Though that'd only likely
> On Mon, Mar 30, 2009 at 1:42 PM, wrote:
>>> On Mon, Mar 30, 2009 at 12:42 PM, wrote:
Arguably in this case the actual query should run faster than the
EXPLAIN
ANALYZE version, since the cache is hot. (Though that'd only likely
shave
a few dozen ms off the runtime)
>>>
On Mon, Mar 30, 2009 at 1:42 PM, wrote:
>> On Mon, Mar 30, 2009 at 12:42 PM, wrote:
>>> Arguably in this case the actual query should run faster than the
>>> EXPLAIN
>>> ANALYZE version, since the cache is hot. (Though that'd only likely
>>> shave
>>> a few dozen ms off the runtime)
>>
>> Joini
On Mon, Mar 30, 2009 at 12:42 PM, wrote:
>> On Mon, Mar 30, 2009 at 1:50 PM, wrote:
>>> I'm running a 64-bit build of Postgres 8.3.5 on AIX 5.3, and have a
>>> really
>>> strange, annoying transient problem with one particular query stalling.
>>>
>>> The symptom here is that when this query is
> On Mon, Mar 30, 2009 at 12:42 PM, wrote:
>> Arguably in this case the actual query should run faster than the
>> EXPLAIN
>> ANALYZE version, since the cache is hot. (Though that'd only likely
>> shave
>> a few dozen ms off the runtime)
>
> Joining a lot of tables together? Could be GEQO kickin
> On Mon, Mar 30, 2009 at 2:42 PM, wrote:
>>> On Mon, Mar 30, 2009 at 1:50 PM, wrote:
>> I'm not executing any of the EXPLAINs by hand, because I didn't want to
>> have to worry about typos or filling in temp tables with test data.
>> Inside
>> the app the SQL for the problematic query's stored
On Mon, Mar 30, 2009 at 2:42 PM, wrote:
>> On Mon, Mar 30, 2009 at 1:50 PM, wrote:
>>> I'm running a 64-bit build of Postgres 8.3.5 on AIX 5.3, and have a
>>> really
>>> strange, annoying transient problem with one particular query stalling.
>>>
>>> The symptom here is that when this query is m
> On Mon, Mar 30, 2009 at 1:50 PM, wrote:
>> I'm running a 64-bit build of Postgres 8.3.5 on AIX 5.3, and have a
>> really
>> strange, annoying transient problem with one particular query stalling.
>>
>> The symptom here is that when this query is made with X or more records
>> in
>> a temp table
On Mon, Mar 30, 2009 at 1:50 PM, wrote:
> I'm running a 64-bit build of Postgres 8.3.5 on AIX 5.3, and have a really
> strange, annoying transient problem with one particular query stalling.
>
> The symptom here is that when this query is made with X or more records in
> a temp table involved in
>
>
> 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 th
I'm running a 64-bit build of Postgres 8.3.5 on AIX 5.3, and have a really
strange, annoying transient problem with one particular query stalling.
The symptom here is that when this query is made with X or more records in
a temp table involved in the join (where X is constant when the problem
mani
Hello Matthew,
Another idea:
Are your objects limited to some smaller ranges of your whole interval ?
If yes you may possibly reduce the ranges to search for while using an
additional table with the min(start) max(end) of each object...
Marc Mamin
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,
On Mon, Mar 30, 2009 at 9:34 AM, Mario Splivalo
wrote:
> -> Bitmap Heap Scan on photo_info_data u (cost=2193.50..26798.74
> rows=109024 width=9) (actual time=0.025..0.030 rows=3 loops=2)
> Recheck Cond: ((u.field_name)::text = (t.key)::text)
> -> Bitmap Index
On Mon, 30 Mar 2009, Віталій Тимчишин wrote:
select
case when n == 1 then id1 else id2 end,
case when n == 2 then id1 else id2 end
from (
... a, (values (1),(2)) b(n)
Yeah, that's nice.
However, it is still the case that we can't trust the database to choose
the correct plan. It is curren
On Mon, 30 Mar 2009, Marc Mamin wrote:
But I often read that BETWEEN is faster than using 2 comparison operators.
http://www.postgresql.org/docs/current/static/functions-comparison.html
says otherwise.
a BETWEEN x AND y
is equivalent to
a >= x AND a <= y
There is no difference between th
Shouldn't Postgres favour a "between" index scan over an open-ended
one?
On Fri, 27 Mar 2009, Tom Lane wrote:
Currently the planner only notices that for a range check that involves
comparisons of the same variable expression to two constants (or
pseudoconstants anyway). In principle it might
>> WHERE (l2.start BETWEEN l1.start AND l1.end
>> OR
>> l1.start BETWEEN l2.start AND l2.end
>> )
>Yes, that's another way to calculate an overlap. However, it turns out to not
>be that fast.
>The problem is that OR there, which causes a bitmap index scan, as the le
Tom Lane wrote:
Mario Splivalo writes:
-> Bitmap Heap Scan on photo_info_data u (cost=39134.84..63740.08
rows=109024 width=50) (actual time=270.464..270.469 rows=3 loops=2)
Recheck Cond: ((u.field_name)::text = (t.key)::text)
-> Bitmap Index Scan on photo_info_data_p
Mario Splivalo writes:
> -> Bitmap Heap Scan on photo_info_data u (cost=39134.84..63740.08
> rows=109024 width=50) (actual time=270.464..270.469 rows=3 loops=2)
> Recheck Cond: ((u.field_name)::text = (t.key)::text)
> -> Bitmap Index Scan on photo_info_data_pk
> (cost=
I have two tables, like this:
Big table:
CREATE TABLE photo_info_data
(
photo_id integer NOT NULL,
field_name character varying NOT NULL,
field_value character varying,
CONSTRAINT photo_info_data_pk PRIMARY KEY (photo_id, field_name)
)
WITH (OIDS=FALSE);
CREATE INDEX user_info_data_ix_f
On Sun, Mar 1, 2009 at 4:32 AM, Robert Haas wrote:
> What do you have default_statistics_target set to? If it's less than
> 100, you should probably raise it to 100 and re-analyze (the default
> value for 8.4 will be 100, but for 8.3 and prior it is 10).
Changing it to 100 fixed the problem. Tha
On Fri, 27 Mar 2009, Marc Mamin wrote:
if your data are mostly static and you have a few mains objects,
maybe you can have some gain while defining conditional indexes for those plus
one for the rest
and then slicing the query:
Maybe. I thought about doing that. However, I am not convinced tha
On Fri, 27 Mar 2009, Dimitri Fontaine wrote:
Maybe it's just that I didn't devote enough time to reading your detailed
explanation above, but this part sounds like it could be done in an aggregate
you'd use in a correlated subquery containing the right ORDER BY, couldn't
it?
http://www.postgre
On Fri, 27 Mar 2009, Tom Lane wrote:
Notice the two different index conditions:
(l1.end > l2.start) AND (l1.start < l2.start) - "between"
(l1.end > l2.start) AND (l1.start >= l2.start) - open-ended
Both have a cost of (cost=0.00..123.10 rows=4809 width=12)
Currently the planner only
34 matches
Mail list logo
