Heikki Linnakangas hlinnakan...@vmware.com writes:
We should do this automatically. Or am I missing something?
Yes. This is not equality.
ALTER OPERATOR FAMILY integer_ops USING btree ADD
OPERATOR 3 @ (int4, int4range),
FUNCTION 1 btint4rangecmp(int4, int4range);
That will break
On 21.03.2013 06:07, Vasilis Ventirozos wrote:
On Thu, Mar 21, 2013 at 5:58 AM, Tom Lanet...@sss.pgh.pa.us wrote:
What I find more disturbing is that this is what I get from the example
in HEAD:
regression=# explain SELECT * FROM a WHERE ts@
tstzrange('2013-01-01','2013-01-01 00:10:00');
Well, no. @ is not a btree-indexable operator.
Yes, but it's equivalent to ( ( a = b1 or b1 is null ) and ( a b2 or
b2 is null ) ), which *is* btree-indexable and can use an index. So it
seems like the kind of optimization we could eventually make.
--
Josh Berkus
PostgreSQL Experts Inc.
Folks,
I just noticed that if I use a tstzrange for convenience, a standard
btree index on a timestamp won't get used for it. Example:
table a (
id int,
val text,
ts timestamptz
);
index a_ts on a(ts);
SELECT * FROM a WHERE ts @ tstzrange('2013-01-01','2013-01-01
Josh Berkus j...@agliodbs.com writes:
I just noticed that if I use a tstzrange for convenience, a standard
btree index on a timestamp won't get used for it. Example:
table a (
id int,
val text,
ts timestamptz
);
index a_ts on a(ts);
SELECT * FROM a WHERE ts @
can you run an analyze command first and then post here the results of:
select * FROM pg_stats WHERE tablename = 'delayed_jobs';
?
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/index-usage-for-min-vs-order-by-asc-limit-1-tp5002928p5004410.html
Sent from the PostgreSQL
I have two queries in PG 9.1. One uses an index like I would like, the other
does not. Is this expected behavior? If so, is there any way around it?
postgres=# explain analyze select min(id) from delayed_jobs where
strand='sis_batch:account:15' group by strand;
On Nov 17, 2011, at 5:12 PM, Ben Chobot wrote:
I have two queries in PG 9.1. One uses an index like I would like, the other
does not. Is this expected behavior? If so, is there any way around it?
I don't think you want the group by in that first query.
Cheers,
Steve
postgres=#
On Nov 17, 2011, at 5:20 PM, Steve Atkins wrote:
I don't think you want the group by in that first query.
Heh, I tried to simply the example, but in reality that = becomes an in clause
of multiple values. So the group by is needed.
postgres=# explain analyze select min(id) from
On Apr 27, 2011, at 11:11 PM, Joseph Shraibman j...@selectacast.net wrote:
On 04/27/2011 04:32 PM, Robert Haas wrote:
In the first case, PostgreSQL evidently thinks that using the indexes
will be slower than just ignoring them. You could find out whether
it's right by trying it with
On Fri, Apr 1, 2011 at 2:41 AM, Joseph Shraibman j...@selectacast.net wrote:
When I do a query on a table with child tables on certain queries pg
uses indexes and on others it doesn't. Why does this happen? For example:
[local]:playpen= explain analyze select * from vis where id 10747 ;
On 04/27/2011 04:32 PM, Robert Haas wrote:
In the first case, PostgreSQL evidently thinks that using the indexes
will be slower than just ignoring them. You could find out whether
it's right by trying it with enable_seqscan=off.
My point is that this is just a problem with inherited tables.
On Wed, Apr 27, 2011 at 2:11 PM, Joseph Shraibman j...@selectacast.netwrote:
On 04/27/2011 04:32 PM, Robert Haas wrote:
In the first case, PostgreSQL evidently thinks that using the indexes
will be slower than just ignoring them. You could find out whether
it's right by trying it with
Joseph Shraibman wrote:
In a 52 gig table I have a select id from table limit 1 order
by id desc returns instantly, but as soon as you declare a child table
it tries to seq scan all the tables.
This is probably the limitation that's fixed in PostgreSQL 9.1 by this
commit (following a few
I'm having trouble getting the query planner to use indexes. The situation
occurs when writing a query that uses functions for defining the parameters for
the conditions on the indexed columns. The system I'm running is Windows Server
2003, using version 8.4.2 of PostgreSQL.
This is the
Jeremy Palmer jpal...@linz.govt.nz writes:
This is the query that does not use the indexes:
SELECT
coo.nod_id,
640*radians(sqrt((coo.value1 - -41.0618)^2+((coo.value2 -
175.58461)*cos(radians(-41.0618)))^2)) as distance
FROM
crs_coordinate coo
WHERE
coo.value1 between
Palmer
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Index usage with functions in where condition
Jeremy Palmer jpal...@linz.govt.nz writes:
This is the query that does not use the indexes:
SELECT
coo.nod_id,
640*radians(sqrt((coo.value1 - -41.0618)^2+((coo.value2
Not like operation does not use index.
select * from vtiger_contactscf where lower(cf_1253) not like
lower('Former%')
I created index on lower(cf_1253).
How can I ensure index usage in not like operation?
Anyone please help.
On 18 February 2010 11:55, AI Rumman rumman...@gmail.com wrote:
Not like operation does not use index.
select * from vtiger_contactscf where lower(cf_1253) not like
lower('Former%')
I created index on lower(cf_1253).
How can I ensure index usage in not like operation?
Anyone please help.
On Thu, Feb 18, 2010 at 6:00 PM, Thom Brown thombr...@gmail.com wrote:
On 18 February 2010 11:55, AI Rumman rumman...@gmail.com wrote:
Not like operation does not use index.
select * from vtiger_contactscf where lower(cf_1253) not like
lower('Former%')
I created index on
In response to Thom Brown :
On 18 February 2010 11:55, AI Rumman rumman...@gmail.com wrote:
Not like operation does not use index.
select * from vtiger_contactscf where lower(cf_1253) not like
lower('Former%')
I created index on lower(cf_1253).
How can I ensure index usage in not
On Thu, Feb 18, 2010 at 01:18:10PM +0100, A. Kretschmer wrote:
In response to Thom Brown :
On 18 February 2010 11:55, AI Rumman rumman...@gmail.com wrote:
Not like operation does not use index.
select * from vtiger_contactscf where lower(cf_1253) not like
lower('Former%')
I
On 18 February 2010 12:18, A. Kretschmer
andreas.kretsch...@schollglas.com wrote:
In response to Thom Brown :
On 18 February 2010 11:55, AI Rumman rumman...@gmail.com wrote:
Not like operation does not use index.
select * from vtiger_contactscf where lower(cf_1253) not like
In response to Kenneth Marshall :
How many rows do you have in your table? If there are relatively few,
it probably guesses it to be cheaper to do a sequential scan and
calculate lower values on-the-fly rather than bother with the index.
That's one reason, an other reason, i think,
Hello,
I am doing some performances testing on Postgres I discovered the
following behavior, when using 2 different ways of writing selects (but
doing the same aggregations at the end):
1. test case 1, using outer join:
create table test2 as
select
soj_session_log_id, pv_timestamp,
Cc : pgsql-performance@postgresql.org
Objet : Re: [PERFORM] Index usage with sub select or inner joins
On Wed, Nov 12, 2008 at 02:22:47PM +0100, Julien Theulier wrote:
QUESTION: Why the planner choose seq scan in the first case indexes
scan in the second case? In a more general way, I observed
On Wed, Nov 12, 2008 at 02:22:47PM +0100, Julien Theulier wrote:
QUESTION: Why the planner choose seq scan in the first case indexes scan
in the second case? In a more general way, I observed that the planner has
difficulties to select index scans does in almost all the cases seq scan,
when
Tom Lane [EMAIL PROTECTED] writes:
Jeff Frost [EMAIL PROTECTED] writes:
Tom Lane wrote:
Huh. That does sound like it's a version-to-version difference.
There's nothing in the CVS log that seems related though. Are you
willing to post your test case?
It's a customer DB, so I'll contact
On Fri, 31 Oct 2008, Gregory Stark wrote:
Tom Lane [EMAIL PROTECTED] writes:
Jeff Frost [EMAIL PROTECTED] writes:
Tom Lane wrote:
Huh. That does sound like it's a version-to-version difference.
There's nothing in the CVS log that seems related though. Are you
willing to post your test
I've run across a strange problem with PG 8.3.3 not using indexes on a
particular table after building the table during a transaction.
You can see a transcript of the issue here:
http://gist.github.com/21154
Interestingly, if I create another temp table 'CREATE TEMP TABLE AS
SELECT * FROM act'
Jeff Frost [EMAIL PROTECTED] writes:
I've run across a strange problem with PG 8.3.3 not using indexes on a
particular table after building the table during a transaction.
This may be a HOT side-effect ... is pg_index.indcheckxmin set for
the index?
regards, tom lane
Jeff Frost [EMAIL PROTECTED] writes:
Tom Lane wrote:
This may be a HOT side-effect ... is pg_index.indcheckxmin set for
the index?
Yep, sure enough, the 'act' table's indexes have it set and jefftest and
jefftest2's indexes do not.
Okay. What that means is that the indexes were created on
Tom Lane wrote:
Okay. What that means is that the indexes were created on data that had
already been inserted and updated to some extent, resulting in
HOT-update chains that turned out to be illegal for the new indexes.
The way we deal with this is to mark the indexes as not usable by any
Jeff Frost [EMAIL PROTECTED] writes:
Tom Lane wrote:
Okay. What that means is that the indexes were created on data that had
already been inserted and updated to some extent, resulting in
HOT-update chains that turned out to be illegal for the new indexes.
The way we deal with this is to
On Thu, 30 Oct 2008, Tom Lane wrote:
Any idea why I don't see it on 8.3.4?
I think it's more likely some small difference in your test conditions
than any real version-to-version difference. In particular I think the
still see test might be influenced by the ages of transactions running
Jeff Frost [EMAIL PROTECTED] writes:
On Thu, 30 Oct 2008, Tom Lane wrote:
Any idea why I don't see it on 8.3.4?
I think it's more likely some small difference in your test conditions
than any real version-to-version difference. In particular I think the
still see test might be influenced
Tom Lane wrote:
Jeff Frost [EMAIL PROTECTED] writes:
On Thu, 30 Oct 2008, Tom Lane wrote:
Any idea why I don't see it on 8.3.4?
I think it's more likely some small difference in your test conditions
than any real version-to-version difference. In particular I think the
Jeff Frost [EMAIL PROTECTED] writes:
Tom Lane wrote:
Huh. That does sound like it's a version-to-version difference.
There's nothing in the CVS log that seems related though. Are you
willing to post your test case?
It's a customer DB, so I'll contact them and see if we can boil it down
I am using Postgres 8.2.5.
I have a table that has rows containing a variable length array with a known
maximum.
I was doing selects on the array elements using an ANY match. The performance
was not too good as my table got bigger. So I added an index on the array.
That didn't help since
andrew klassen [EMAIL PROTECTED] writes:
Is there any alternative to what am I currently doing other than creating a
row for
each array element,
Since (I think) 8.2, you could create a GIN index on the array column
and then array overlap () would be indexable. GIN has some
performance
andrew,
what are your queries ? Have you seen contrib/intarray,
GIN index ?
On Thu, 7 Feb 2008, andrew klassen wrote:
I am using Postgres 8.2.5.
I have a table that has rows containing a variable length array with a known
maximum.
I was doing selects on the array elements using an ANY
Hi Tom,
do you think it would be a good idea to ask GIN index team to
implement an int-based bitmap set indexing operator for GIN/GiST based
indexes? Or there will be a possibility to somehow optimally index
arrays of enumerations to implement such bitmap structures in 8.3 or
later postgresql
Hi,
I could not find and normal solution for that issue. But I am using
some workarounds for that issue.
The solution, that I am using now is to create an index for every bit
of your bitmap field.
So something like
CREATE INDEX idx_hobbybit_0_limited
ON versionA.user_fast_index
USING btree
What about saying?:
TBL1.CATEGORY = TBL2.CATEGORY
Are you sure you understood what was the question?
Is the TBL1.CATEGORY = TBL2.CATEGORY the same as TBL1.CATEGORY
TBL2.CATEGORY 0?
---(end of broadcast)---
TIP 6: explain analyze is your
On Mon, Sep 17, 2007 at 2:49 AM, in message
[EMAIL PROTECTED], valgog
[EMAIL PROTECTED] wrote:
What about saying?:
TBL1.CATEGORY = TBL2.CATEGORY
Are you sure you understood what was the question?
Is the TBL1.CATEGORY = TBL2.CATEGORY the same as TBL1.CATEGORY
TBL2.CATEGORY 0?
Kevin Grittner [EMAIL PROTECTED] writes:
On Mon, Sep 17, 2007 at 2:49 AM, in message
[EMAIL PROTECTED], valgog
[EMAIL PROTECTED] wrote:=20
Are you sure you understood what was the question?
Is the TBL1.CATEGORY = TBL2.CATEGORY the same as TBL1.CATEGORY
TBL2.CATEGORY 0?
Yes, given that
On Mon, Sep 17, 2007 at 8:37 AM, in message [EMAIL PROTECTED],
Tom Lane [EMAIL PROTECTED] wrote:
Kevin Grittner [EMAIL PROTECTED] writes:
On Mon, Sep 17, 2007 at 2:49 AM, in message
[EMAIL PROTECTED], valgog
[EMAIL PROTECTED] wrote:=20
Are you sure you understood what was the question?
Hi,
A little clarification. Actually, TBL1.CATEGORY and/or TBL2.CATEGORY may
hold a binary value having multiple binary(ies) '1'.
Each binary value column represent an business attribute.
If a binary value column is equal to '1', it means that the business
attribute is True,
otherwise it is
Hello,
My question is about index usage when bitwise operations are invoked.
Situation Context:
--
Lets suppose we have 2 tables TBL1 and TBL2 as the following:
TBL1 {
. ;
integer categoryGroup; // categoryGroup is declared as an index on TABL1
. ;
}
Hi,
I have a table in my database that is updated every minute with new acquired
data. Anyway there is a query to get latest values to be displayed on
screen. I have postgresql 7.4.2 that work very fine. The problem was that
after hdd crash I have rebuild database from the archive and...
On Mon, Apr 23, 2007 at 07:20:29PM +0200, Arkadiusz Raj wrote:
I have a table in my database that is updated every minute with new acquired
data. Anyway there is a query to get latest values to be displayed on
screen. I have postgresql 7.4.2 that work very fine.
You want _at least_ the
We're having a problem with one of our queries being slow. It appears to be due
to the index being used to go from tableA to tableB.
Here are the tables:
CREATE TABLE tableA
(
table_idA int8 NOT NULL DEFAULT nextval('tableA_id_seq'::regclass),
CONSTRAINT table_idA_pk PRIMARY KEY (table_idA),
)
Scott Matseas [EMAIL PROTECTED] writes:
If I enable sequential scan the Index Cond in
question gets replaced with a Seq scan.
What other planner parameters have you been fooling with?
With no data in the tables, I get a reasonably sane-looking plan,
so I'm thinking you've chosen bad values for
Tom Lane wrote:
What other planner parameters have you been fooling with?
Hi Tom,
The other parameters that have been changed are:
set join_collapse_limit to 1
set enable_sort to off
We are using version 8.1.3. We've noticed the query plan changing depending
on the amount of data in the tables
I have a table with 37000 rows, an integer column, and an index on that
column. I've got a function that returns an integer. When I do a select
where I restrict that column to being equal to a static number, explain
tells me the index will be used. When I do the same thing but use the
function
De : [EMAIL PROTECTED] [mailto:pgsql-performance-
[EMAIL PROTECTED] De la part de Ben
Envoyé : vendredi, juillet 28, 2006 15:21
À : pgsql-performance@postgresql.org
Objet : [PERFORM] index usage
I have a table with 37000 rows, an integer column, and an index on that
column. I've got
It's volatile, but it will always return an integer.
On Fri, 28 Jul 2006, Daniel Caune wrote:
De : [EMAIL PROTECTED] [mailto:pgsql-performance-
[EMAIL PROTECTED] De la part de Ben
Envoyé : vendredi, juillet 28, 2006 15:21
À : pgsql-performance@postgresql.org
Objet : [PERFORM] index usage
I
return an integer.
On Fri, 28 Jul 2006, Daniel Caune wrote:
De : [EMAIL PROTECTED] [mailto:pgsql-performance-
[EMAIL PROTECTED] De la part de Ben
Envoyé : vendredi, juillet 28, 2006 15:21
À : pgsql-performance@postgresql.org
Objet : [PERFORM] index usage
I have a table with 37000
Ben [EMAIL PROTECTED] writes:
It's volatile, but it will always return an integer.
If it's volatile then it can't be used for an index condition.
regards, tom lane
---(end of broadcast)---
TIP 9: In versions below 8.0, the
On Thu, 2006-02-02 at 09:12 +1300, Ralph Mason wrote:
Hi,
I have 2 tables both have an index on ID (both ID columns are an oid).
I want to find only only rows in one and not the other.
Select ID from TableA where ID not IN ( Select ID from Table B)
Have you considered this:
SELECT ID
On Wed, 2006-02-01 at 12:22 -0800, Jeffrey W. Baker wrote:
On Thu, 2006-02-02 at 09:12 +1300, Ralph Mason wrote:
Hi,
I have 2 tables both have an index on ID (both ID columns are an oid).
I want to find only only rows in one and not the other.
Select ID from TableA where ID not
Jeffrey W. Baker [EMAIL PROTECTED] writes:
On Thu, 2006-02-02 at 09:12 +1300, Ralph Mason wrote:
Select ID from TableA where ID not IN ( Select ID from Table B)
Have you considered this:
SELECT ID from TableA EXCEPT Select ID from Table B
Also, increasing work_mem might persuade the planner
On Thu, Feb 02, 2006 at 09:12:59 +1300,
Ralph Mason [EMAIL PROTECTED] wrote:
Hi,
I have 2 tables both have an index on ID (both ID columns are an oid).
I want to find only only rows in one and not the other.
Select ID from TableA where ID not IN ( Select ID from Table B)
This always
Select ID from TableA where not exists ( Select ID from Table B where ID
= TableA.ID)
might give you index scan. Of course, that is only useful is TableA is
very small table.
Not appropriate for 250k rows
on 2/1/2006 12:12 PM Ralph Mason said the following:
Hi,
I have 2 tables both have an
Hi, Pierre-Frédéric,
On Sat, 20 Nov 2004 17:12:43 +0100
Pierre-Frédéric Caillaud [EMAIL PROTECTED] wrote:
WHERE cd='ca' ORDER BY l_postcode;
Write :
WHERE cd='ca' ORDER BY cd, l_postcode;
You have a multicolumn index, so you should specify a multicolumn sort
exactly the same as
Hello,
I have the following query plan:
logigis=# explain SELECT geom, ref_in_id as ref, nref_in_id as nref, st_name as
name, substr(l_postcode,1,2) as postfirst, func_class as level FROM
schabi.streets WHERE cd='ca' ORDER BY l_postcode;
QUERY PLAN
Instead of :
WHERE cd='ca' ORDER BY l_postcode;
Write :
WHERE cd='ca' ORDER BY cd, l_postcode;
You have a multicolumn index, so you should specify a multicolumn sort
exactly the same as your index, and the planner will get it.
---(end of
Markus Schaber [EMAIL PROTECTED] writes:
But as it fetches all the rows through the index, why doesn't it
recognize that, fetching this way, the rows are already sorted by
l_postcode?
Tell it to ORDER BY cd, l_postcode.
Is Postgresql 8 more intelligend in this case?
No.
On Mon, 26 Apr 2004, Stephan Szabo wrote:
On Fri, 23 Apr 2004 [EMAIL PROTECTED] wrote:
I have a query which I think should be using an index all of the time but
postgres only uses the index part of the time. The index
(ticket_crm_map_crm_id_suppid) has the where clause column (crm_id)
scott.marlowe [EMAIL PROTECTED] writes:
There are tons of hints that it works this way in how they're written, but
nothing that just comes out and says that with pgsql's mvcc
implementation, an index scan still has to hit the pages that contain the
tuples, so often in pgsql a seq scan is a
Hi,
I have a query which I think should be using an index all of the time but
postgres only uses the index part of the time. The index
(ticket_crm_map_crm_id_suppid) has the where clause column (crm_id) listed
first followed by the selected column (support_person_id). Wouldn't the
most
When checking an index in postgres the original table has to be checked for
each result to find if the index entry is still valid? In which case you
can't blindly scan the whole index and assume the data is good. I was used
to Oracle behavior where the index is up to date so it can do the scan
72 matches
Mail list logo
