Re: [PERFORM] Index not used

> ​Or, better, persuade the app to label the value " ​ public.push_guid ​" since that is the column's type​...a type you haven't defined for us.  If you get to add explicit casts this should be easy...but I'm not familiar with the framework you are using.     push_guid was a CHARACTER(36)

Re: [PERFORM] Index not used

On Thu, Jun 16, 2016 at 11:05 AM, Tom Lane wrote: > meike.talb...@women-at-work.org writes: > > When I query this through pgsql, the queries are fast as expected. > > select * from push_topic where guid = > 'DD748CCD-B8A4-3B9F-8F60-67F1F673CFE5' > > Index Scan using

Re: [PERFORM] Index not used

meike.talb...@women-at-work.org writes: > When I query this through pgsql, the queries are fast as expected. > select * from push_topic where guid = 'DD748CCD-B8A4-3B9F-8F60-67F1F673CFE5' > Index Scan using push_topic_idx_topicguid on push_topic (cost=0.42..8.44 > rows=1 width=103) (actual

Re: [PERFORM] Index not used

gresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of meike.talb...@women-at-work.org Sent: Thursday, June 16, 2016 12:59 AM To: pgsql-performance@postgresql.org Subject: [PERFORM] Index not used Hello,   I've a basic table with about 100K rows:   CREATE TABLE "public"."p

[PERFORM] Index not used

Hello,   I've a basic table with about 100K rows:   CREATE TABLE "public"."push_topic" (  "id" Serial PRIMARY KEY,  "guid" public.push_guid NOT NULL,  "authenticatorsending" Varchar(32) NOT NULL,  "authenticatorsubscription" Varchar(32) NOT NULL,  "countpushed" Integer NOT NULL,  "datecreated"

Re: [PERFORM] index not used again

Stephan Szabo schrieb: Did you reset the table contents between these two (remember that explain analyze actually runs the query)? The second appears to be changing no rows from the output. I for myself did not, but as there are runnig automatic jobs periodically I can't tell, if one ran in

Re: [PERFORM] index not used again

On Sun, 2 Apr 2006, Jan Kesten wrote: Stephan Szabo schrieb: Did you reset the table contents between these two (remember that explain analyze actually runs the query)? The second appears to be changing no rows from the output. I for myself did not, but as there are runnig automatic

[PERFORM] index not used again

Hi folks! I have just a issue again with unused indexes. I have a database with a couple of tables and I have to do an sync job with them. For marking which row has to be transfered I added a new column token (integer, I will need some more tokens in near future) to every table. Before

Re: [PERFORM] index not used again

On Fri, 31 Mar 2006, Jan Kesten wrote: Hi folks! I have just a issue again with unused indexes. I have a database with a couple of tables and I have to do an sync job with them. For marking which row has to be transfered I added a new column token (integer, I will need some more tokens in

Re: [PERFORM] Index isn't used during a join.

On Wed, Jan 11, 2006 at 12:56:55AM -0700, Michael Fuhr wrote: WHERE ... AND doy = EXTRACT(doy FROM now() - '24 hour'::interval) AND doy = EXTRACT(doy FROM now()) To work on 1 Jan this should be more like WHERE ... AND (doy = EXTRACT(doy FROM now() - '24 hour'::interval) OR doy =

Re: [PERFORM] Index isn't used during a join.

When grilled further on (Wed, 11 Jan 2006 00:56:55 -0700), Michael Fuhr [EMAIL PROTECTED] confessed: On Tue, Jan 10, 2006 at 10:10:55PM -0700, Robert Creager wrote: The query is now correct, but still is slow because of lack of index usage. I don't know how to structure the query correctly

Re: [PERFORM] Index isn't used during a join.

When grilled further on (Wed, 11 Jan 2006 07:26:59 -0700), Robert Creager [EMAIL PROTECTED] confessed: weather-# SELECT *, unmunge_time( time_group ) AS time, weather-# EXTRACT( doy FROM unmunge_time( time_group ) ) weather-# FROM minute.windspeed weather-# JOIN doy_agg ON( EXTRACT( doy

Re: [PERFORM] Index isn't used during a join.

Robert Creager [EMAIL PROTECTED] writes: What I had thought is that PG would (could?) be smart enough to realize tha= t one query was restricted, and apply that restriction to the other based o= n the join. I know it works in other cases (using indexes on both tables u= sing the join)... The

Re: [PERFORM] Index isn't used during a join.

On Wed, Jan 11, 2006 at 08:02:37AM -0700, Robert Creager wrote: The query is wrong as stated, as it won't work when the interval crosses a year boundary, but it's a stop gap for now. Yeah, I realized that shortly after I posted the original and posted a correction.

Re: [PERFORM] Index isn't used during a join.

When grilled further on (Wed, 11 Jan 2006 10:33:03 -0500), Tom Lane [EMAIL PROTECTED] confessed: The planner understands about transitivity of equality, ie given a = b and b = c it can infer a = c. It doesn't do any such thing for inequalities though, nor does it deduce f(a) = f(b) for

Re: [PERFORM] Index isn't used during a join.

When grilled further on (Mon, 9 Jan 2006 22:58:18 -0700), Michael Fuhr [EMAIL PROTECTED] confessed: On Mon, Jan 09, 2006 at 09:23:38PM -0700, Robert Creager wrote: I'm working with a query to get more info out with a join. The base query works great speed wise because of index usage. When

Re: [PERFORM] Index isn't used during a join.

Ok, I'm back, and in a little better shape. The query is now correct, but still is slow because of lack of index usage. I don't know how to structure the query correctly to use the index. Taken individually: weather=# explain analyze select * from doy_agg where doy = extract( doy from now()

Re: [PERFORM] Index isn't used during a join.

On Tue, Jan 10, 2006 at 10:10:55PM -0700, Robert Creager wrote: The query is now correct, but still is slow because of lack of index usage. I don't know how to structure the query correctly to use the index. Have you tried adding restrictions on doy in the WHERE clause? Something like this, I

[PERFORM] Index isn't used during a join.

Hey folks, I'm working with a query to get more info out with a join. The base query works great speed wise because of index usage. When the join is tossed in, the index is no longer used, so the query performance tanks. Can anyone advise on how to get the index usage back? weather=#

Re: [PERFORM] Index isn't used during a join.

On Mon, Jan 09, 2006 at 09:23:38PM -0700, Robert Creager wrote: I'm working with a query to get more info out with a join. The base query works great speed wise because of index usage. When the join is tossed in, the index is no longer used, so the query performance tanks. The first query

[PERFORM] Index not used on group by

Hello all, I have table ma_data, that contain above 30 rows. This table has primary key id, and field alias_id. I create index (btree)on this field. Set statistic: ALTER TABLE public.ma_data ALTER COLUMN alias_id SET STATISTICS 998; So, when I do something like

Re: [PERFORM] Index not used on group by

Andrey Repko wrote: I have table ma_data, that contain above 30 rows. This table has primary key id, and field alias_id. I create index (btree)on this field. Set statistic: ALTER TABLE public.ma_data ALTER COLUMN alias_id SET STATISTICS 998; So, when I do something

Re: [PERFORM] Index not used on group by

Здравствуйте Richard, Tuesday, September 27, 2005, 1:48:15 PM, Вы писали: RH Andrey Repko wrote: I have table ma_data, that contain above 30 rows. This table has primary key id, and field alias_id. I create index (btree)on this field. Set statistic: ALTER TABLE

Re: [PERFORM] Index not used on group by

Здравствуйте Richard, Tuesday, September 27, 2005, 2:08:31 PM, Вы писали: sart_ma=# EXPLAIN ANALYZE SELECT alias_id FROM ma_data GROUP BY alias_id; QUERY PLAN

Re: [PERFORM] Index not used on group by

Андрей Репко wrote: RH What happens if you use something like RHSELECT DISTINCT alias_id FROM ma_data; sart_ma=# EXPLAIN ANALYZE SELECT DISTINCT alias_id FROM ma_data; QUERY PLAN

Re: [PERFORM] Index not used on group by

Андрей Репко wrote: Здравствуйте Richard, Tuesday, September 27, 2005, 2:08:31 PM, Вы писали: sart_ma=# EXPLAIN ANALYZE SELECT alias_id FROM ma_data GROUP BY alias_id; QUERY PLAN

[PERFORM] Index not used with prepared statement

Hi. I have a performance problem with prepared statements (JDBC prepared statement). This query: PreparedStatement st = conn.prepareStatement(SELECT id FROM dga_dienstleister WHERE plz like '45257'); does use an index. This query: String plz = 45257; PreparedStatement

Re: [PERFORM] Index not used with prepared statement

Guido Neitzer schrob: I have a performance problem with prepared statements (JDBC prepared statement). This query: PreparedStatement st = conn.prepareStatement(SELECT id FROM dga_dienstleister WHERE plz like '45257'); does use an index. This query: String plz = 45257;

Re: [PERFORM] Index not used with prepared statement

On 11.09.2005, at 11:03 Uhr, Andreas Seltenreich wrote: I'm not perfectly sure, but since the index could only be used with a subset of all possible parameters (the pattern for like has to be left-anchored), I could imagine the planner has to avoid the index in order to produce an universal

[PERFORM] Index not used on join with inherited tables

Hi all, I'm having another problem with a query that takes to long, because the appropriate index is not used. I found some solutions to this problem, but I think Postgres should do an index scan in all cases. To show the problem I've attached a small script with a testcase. Thanks in

Re: [PERFORM] Index not used on join with inherited tables

Sebastian, I'm having another problem with a query that takes to long, because the appropriate index is not used. PostgreSQL is not currently able to push down join criteria into UNIONed subselects. It's a TODO. Also, if you're using inherited tables, it's unnecessary to use UNION; just

Re: [PERFORM] Index not used on join with inherited tables

Josh Berkus wrote: Sebastian, I'm having another problem with a query that takes to long, because the appropriate index is not used. PostgreSQL is not currently able to push down join criteria into UNIONed subselects. It's a TODO. And the appends in a SELECT * from parent are UNIONs,

[PERFORM] index not used

Hi folks, I'm doing a simple lookup in a small table by an unique id, and I'm wondering, why explains tells me seqscan is used instead the key. The table looks like: id bigint primary key, a varchar, b varchar, c varchar and I'm quering: select * from foo

Re: [PERFORM] index not used

If id is PK, the query shoudl return 1 row only... --- Enrico Weigelt [EMAIL PROTECTED] wrote: Hi folks, I'm doing a simple lookup in a small table by an unique id, and I'm wondering, why explains tells me seqscan is used instead the key. The table looks like: idbigint

Re: [PERFORM] index not used

On Thu, 21 Apr 2005, Enrico Weigelt wrote: I'm doing a simple lookup in a small table by an unique id, and I'm wondering, why explains tells me seqscan is used instead the key. The table looks like: idbigint primary key, a varchar, b varchar, c

[PERFORM] Index not used with or condition

Hi all, I am facing a strange problem when I run EXPLAIN against a table having more than 10 records. The query have lot of OR conditions and when parts of the query is removed it is using index. To analyse it I created a table with a single column, inserted 10 records(random number)

Re: [PERFORM] Index not used with or condition

On Mon, Feb 07, 2005 at 04:44:07PM +0530, Antony Paul wrote: On more investigation I found that index scan is not used if the query have a function in it like lower() and an index exist for lower() column. What version are you using? 8.0 had fixes for this situation. /* Steinar */ --

Re: [PERFORM] Index not used with or condition

It depends on many circumstances, but, at first, simple question: Did you run vacuum analyze? I am satisfied with functional indexes - it works in my pg 7.4.x. Antony Paul wrote: On more investigation I found that index scan is not used if the query have a function in it like lower() and an

Re: [PERFORM] Index not used with or condition

I ran analyze; several times. rgds Antony Paul On Mon, 07 Feb 2005 12:53:30 +0100, Jan Poslusny pajout@gingerall.cz wrote: It depends on many circumstances, but, at first, simple question: Did you run vacuum analyze? I am satisfied with functional indexes - it works in my pg 7.4.x. Antony

Re: [PERFORM] index not used if using IN or OR

Mario Ivankovits wrote: Hello ! Sorry if this has been discussed before, it is just hard to find in the archives using the words or or in :-o I use postgres-8.0 beta4 for windows. I broke down my problem to a very simple table - two columns primary_key and secondary_key. Creates and Insert you

Re: [PERFORM] index not used if using IN or OR

Mario Ivankovits [EMAIL PROTECTED] writes: After populating the table with 8920 records and analyze the scenario gets even worser: select * from tt where seckey = 1; Seq Scan on tt (cost=0.00..168.50 rows=1669 width=12) (actual time=0.000..15.000 rows=1784 loops=1) Filter: (seckey = 1)

[PERFORM] index not used if using IN or OR

Hello ! Sorry if this has been discussed before, it is just hard to find in the archives using the words or or in :-o I use postgres-8.0 beta4 for windows. I broke down my problem to a very simple table - two columns primary_key and secondary_key. Creates and Insert you will find below. If I

Re: [PERFORM] Index not used in query. Why?

: [EMAIL PROTECTED] Sent: Tuesday, October 19, 2004 7:52 PM Subject: Re: [PERFORM] Index not used in query. Why? Andrei Bintintan [EMAIL PROTECTED] writes: Hi to all! I have the following query. The execution time is very big, it doesn't use the indexes and I don't understand why... Indexes

[PERFORM] Index not used in query. Why?

Hi to all! I have the following query. The execution time is very big, it doesn't use the indexes and I don't understand why... SELECT count(o.id) FROM orders o INNER JOIN report r ON o.id=r.id_order INNER JOIN status s ON

Re: [PERFORM] Index not used in query. Why?

Andrei Bintintan [EMAIL PROTECTED] writes: Hi to all! I have the following query. The execution time is very big, it doesn't use the indexes and I don't understand why... Indexes are not necessarily the best way to do a large join. If I use the following query the indexes are used: The key

Re: [PERFORM] index not used when using function

Maybe add an order by artist to force a groupaggregate ? Hi all, a small question: I've got this table songs and an index on column artist. Since there's about one distinct artist for every 10 rows, it would be nice if it could use this index when counting artists. It doesn't however:

[PERFORM] index not used when using function

Hi all, a small question: I've got this table songs and an index on column artist. Since there's about one distinct artist for every 10 rows, it would be nice if it could use this index when counting artists. It doesn't however: lyrics= EXPLAIN ANALYZE SELECT count(DISTINCT artist) FROM songs;