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?
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
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
On Sun, Mar 1, 2009 at 4:32 AM, Robert Haas robertmh...@gmail.com 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
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
Mario Splivalo mario.spliv...@megafon.hr 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
Tom Lane wrote:
Mario Splivalo mario.spliv...@megafon.hr 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
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 leaf of
a
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
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 the
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
On Mon, Mar 30, 2009 at 9:34 AM, Mario Splivalo
mario.spliv...@megafon.hr 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)
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,
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
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
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
On Mon, Mar 30, 2009 at 1:50 PM, d...@sidhe.org 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, d...@sidhe.org 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 2:42 PM, d...@sidhe.org wrote:
On Mon, Mar 30, 2009 at 1:50 PM, d...@sidhe.org 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
On Mon, Mar 30, 2009 at 12:42 PM, d...@sidhe.org wrote:
On Mon, Mar 30, 2009 at 1:50 PM, d...@sidhe.org 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
On Mon, Mar 30, 2009 at 1:42 PM, d...@sidhe.org wrote:
On Mon, Mar 30, 2009 at 12:42 PM, d...@sidhe.org 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
On Mon, Mar 30, 2009 at 4:02 PM, d...@sidhe.org wrote:
On Mon, Mar 30, 2009 at 1:42 PM, d...@sidhe.org wrote:
On Mon, Mar 30, 2009 at 12:42 PM, d...@sidhe.org wrote:
Arguably in this case the actual query should run faster than the
EXPLAIN
ANALYZE version, since the cache is hot. (Though
On Mon, Mar 30, 2009 at 4:02 PM, d...@sidhe.org wrote:
On Mon, Mar 30, 2009 at 1:42 PM, d...@sidhe.org wrote:
On Mon, Mar 30, 2009 at 12:42 PM, d...@sidhe.org wrote:
Arguably in this case the actual query should run faster than the
EXPLAIN
ANALYZE version, since the cache is hot. (Though
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 the
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
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? My bet is that you're
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? My bet is that you're
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 to go
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 to
go
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
On 3/30/09 2:34 PM, d...@sidhe.org 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 DBD::Pg
31 matches
Mail list logo
