Re: [PERFORM] Query planner gaining the ability to replanning after start of query execution.

performance@postgresql.org Subject: Re: [PERFORM] Query planner gaining the ability to replanning after start of query execution. > Can you be more elaborate how you'd want to go about it? My initial approach would be to try to identify places in the plan where selectivity is serious

Re: [PERFORM] Query planner gaining the ability to replanning after start of query execution.

Hello, I'd love to have some sort of dynamic query feedback, yet it's very complicated to do it right. I am not convinced that changing the plan during a single execution is the right way to do it, not only because it sounds intrusive to do crazy things in the executor, but also because don't u

Re: [PERFORM] Query planner gaining the ability to replanning after start of query execution.

> You can't just restart from scratch, because we may already have shipped > rows to the client For v1, replanning wouldn't be an option if rows have already been shipped, or for DML statements. > parallel plans and most importantly cursors? Parallel plans look do-able with the same approach, b

Re: [PERFORM] Query planner gaining the ability to replanning after start of query execution.

Oliver Mattos writes: >> Can you be more elaborate how you'd want to go about it? > ... If another candidate plan is now lower cost, the current plan would be > terminated[1] by setting a flag instructing each execnode to return as > if it had reached the end of the input, although still caching

Re: [PERFORM] Query planner gaining the ability to replanning after start of query execution.

> Can you be more elaborate how you'd want to go about it? My initial approach would be to try to identify places in the plan where selectivity is seriously over or underestimated. I would reuse the instrumentation infrastructure's counts of filtered and returned tuples for each execnode, and pe

Re: [PERFORM] Query planner chooses index scan backward instead of better index option

After Jeff Janes' reply, I have tried a couple of limit values and found at the current state of data, 90 was a change on the query planner. explain (analyze, buffers) select booking0_.* from booking booking0_ where (booking0_.customer_id in (select customer1_.id from customer cust

Re: [PERFORM] Query planner chooses index scan backward instead of better index option

Thank you, Jeff for your reply. Yes, we tested with CTE as well but we are using Hibernate to generate the query and there are some more conditions that can be added if certain parameters supplied. For my knowledge, Hibernate is still not supporting CTE structures yet. That's why I will keep this

Re: [PERFORM] Query planner chooses index scan backward instead of better index option

On Mon, Nov 14, 2016 at 4:01 AM, Seckin Pulatkan wrote: > Hi, > > On our production environment (PostgreSQL 9.4.5 on > x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat > 4.8.5-4), 64-bit), one of our queries runs very slow, about 5 minutes . We > noticed that it does not us

Re: [PERFORM] Query planner wants to use seq scan

Yes, the three fields index AND vacuum solve the issue. Regards, Bertrand 2015-10-29 13:27 GMT+01:00 Alex Ignatov : > > > On 27.10.2015 23:56, Bertrand Paquet wrote: > > So, > > Tonight, the index on the three field is used, may be my yesterday vacuum > updated stats. > > Thx you for your help.

Re: [PERFORM] Query planner wants to use seq scan

On 27.10.2015 23:56, Bertrand Paquet wrote: So, Tonight, the index on the three field is used, may be my yesterday vacuum updated stats. Thx you for your help. Regards, Bertrand 2015-10-27 18:33 GMT+01:00 Bertrand Paquet mailto:bertrand.paq...@doctolib.fr>>: Hi tom, I did

Re: [PERFORM] Query planner wants to use seq scan

On 10/27/15 3:56 PM, Bertrand Paquet wrote: Tonight, the index on the three field is used, may be my yesterday vacuum updated stats. BTW, you can run just ANALYZE, which is *far* faster than a VACUUM on a large table. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in

Re: [PERFORM] Query planner wants to use seq scan

So, Tonight, the index on the three field is used, may be my yesterday vacuum updated stats. Thx you for your help. Regards, Bertrand 2015-10-27 18:33 GMT+01:00 Bertrand Paquet : > Hi tom, > > I did the test yesterday with an index on the three fields, and with a > partial index on organiz

Re: [PERFORM] Query planner wants to use seq scan

Hi tom, I did the test yesterday with an index on the three fields, and with a partial index on organization and status and where is null condition on handled. I saw no modification on query plan. May be I forgot to analyze vacuum after. I will retry tonight. I use a btree index. Is it the good s

Re: [PERFORM] Query planner wants to use seq scan

Hi tom, I did the test yesterday with an index on the three fields, and with a partial index on organization and status and where is null condition on handled. Le mardi 27 octobre 2015, Tom Lane a écrit : > Bertrand Paquet writes: > > We have a slow query. After analyzing, the planner decision

Re: [PERFORM] Query planner wants to use seq scan

show random_page_cost ; random_page_cost -- 4 (1 row) 2015-10-27 12:30 GMT+01:00 Alex Ignatov : > > > On 27.10.2015 14:19, Bertrand Paquet wrote: > > relname | n_live_tup | n_dead_tup | last_vacuum > |last_autovacuum|

Re: [PERFORM] Query planner wants to use seq scan

Bertrand Paquet writes: > We have a slow query. After analyzing, the planner decision seems to be > discutable : the query is faster when disabling seqscan. See below the two > query plan, and an extract from pg_stats. > Any idea about what to change to help the planner ? Neither one of those pl

Re: [PERFORM] Query planner wants to use seq scan

On 27.10.2015 14:19, Bertrand Paquet wrote: relname | n_live_tup | n_dead_tup | last_vacuum | last_autovacuum| last_analyze | last_autoanalyze +++---+---

Re: [PERFORM] Query planner wants to use seq scan

relname | n_live_tup | n_dead_tup | last_vacuum |last_autovacuum| last_analyze | last_autoanalyze +++---+---+---

Re: [PERFORM] Query planner wants to use seq scan

On 27.10.2015 14:10, Bertrand Paquet wrote: Yes, I have run VACUUM ANALYZE, no effect. Bertrand 2015-10-27 12:08 GMT+01:00 Alex Ignatov >: On 27.10.2015 12:35, Bertrand Paquet wrote: Hi all, We have a slow query. After analyzing, the plan

Re: [PERFORM] Query planner wants to use seq scan

Yes, I have run VACUUM ANALYZE, no effect. Bertrand 2015-10-27 12:08 GMT+01:00 Alex Ignatov : > On 27.10.2015 12:35, Bertrand Paquet wrote: > >> Hi all, >> >> We have a slow query. After analyzing, the planner decision seems to be >> discutable : the query is faster when disabling seqscan. See b

Re: [PERFORM] Query planner wants to use seq scan

On 27.10.2015 12:35, Bertrand Paquet wrote: Hi all, We have a slow query. After analyzing, the planner decision seems to be discutable : the query is faster when disabling seqscan. See below the two query plan, and an extract from pg_stats. Any idea about what to change to help the planner ?

Re: [PERFORM] Query planner not using indexes with JOIN query and OR clause

> > Try refactoring to: > > select ai.position, i.filename as image_filename, p.filename as > panorama_filename > from album_items ai > left join image2 i on i.imageid = ai.image_id > left join panoramas p on p.id = ai.panorama_id > where i.filename in ('pano360--v471.jpg', 'pano360-2--v474.jpg') >

Re: [PERFORM] Query planner not using indexes with JOIN query and OR clause

On Mon, Jul 13, 2015 at 4:01 PM, Merlin Moncure wrote: > On Mon, Jul 13, 2015 at 3:54 PM, Ben Hoyt wrote: >> Hi folks, >> >> I have a fairly simple three-table query (pasted below) with two LEFT JOINs >> and an OR in the WHERE clause that for some reason is doing sequential scans >> on all three

Re: [PERFORM] Query planner not using indexes with JOIN query and OR clause

On Mon, Jul 13, 2015 at 3:54 PM, Ben Hoyt wrote: > Hi folks, > > I have a fairly simple three-table query (pasted below) with two LEFT JOINs > and an OR in the WHERE clause that for some reason is doing sequential scans > on all three tables (two of them large -- several million rows), even though

Re: [PERFORM] QUERY PLANNER - Indexe mono column VS composite Index

On Fri, Jul 10, 2015 at 2:34 AM, Nicolas Paris wrote: > > > =3: without a constraint on tval_char => seq > scan > > > EXPLAIN ANALYSE select f.patient_num > from i2b2data_multi_nomi.observation_fact f > where > f.co

Re: [PERFORM] QUERY PLANNER - Indexe mono column VS composite Index

Ok, here is the problem (it's different than what I explained before) ==INDEX == CREATE INDEX of_idx_modifier ON i2b2data_multi_nomi.observation_fact USING btree (concept_cd COLLATE pg_catalog."default", modifier_cd COLLATE pg_catalog."default", valtype_cd COLLATE pg_catalog."default", tval_c

Re: [PERFORM] QUERY PLANNER - Indexe mono column VS composite Index

2015-07-09 22:34 GMT+02:00 Nicolas Paris : > Hello, > > My 9.4 database is used as datawharehouse. I can't change the queries > generated. > > first index : INDEX COL (A,B,C,D,E) > > > In case of query based on COL A, the query planner sometimes go to a seq > scan instead of using the first comp

Re: [PERFORM] Query planner ignoring constraints on partitioned tables when joining

On 18 April 2013 22:42, Tom Lane wrote: > One could imagine adding planner logic that would make inferences of a > similar sort for equalities combined with inequalities, but it would be > vastly more complicated, and would provide useful results in vastly > fewer queries, than the equality-propa

Re: [PERFORM] Query planner ignoring constraints on partitioned tables when joining

Michael Okner writes: > I've been able to reproduce the issue in a generic environment and posted the > code to create this environment on my GitHub at > https://github.com/mikeokner/pgsql_test. The query plans demonstrating this > issue are pasted here: http://bpaste.net/show/92138/. I've poke

Re: [PERFORM] Query planner doesn't use index scan on tsvector GIN index if LIMIT is specifiedQuery planner doesn't use index scan on tsvector GIN index if LIMIT is specified

On 2012-01-10 18:04, Tom Lane wrote: darklow writes: But the performance problems starts when i do the same query specifying LIMIT. *SELECT id FROM cache WHERE tsv @@ to_tsquery('test:*') limit 20;* By some reason index is not used. It apparently thinks there are enough matches that it might a

Re: [PERFORM] Query planner doesn't use index scan on tsvector GIN index if LIMIT is specifiedQuery planner doesn't use index scan on tsvector GIN index if LIMIT is specified

darklow writes: > But the performance problems starts when i do the same query specifying > LIMIT. > *SELECT id FROM cache WHERE tsv @@ to_tsquery('test:*') limit 20;* > By some reason index is not used. It apparently thinks there are enough matches that it might as well just seqscan the table an

Re: [PERFORM] Query planner suggestion, for indexes with similar but not exact ordering.

On Mon, Nov 14, 2011 at 5:22 PM, Andrew Barnham wrote: > I wonder, if it is possible and worthwhile, to setup the query planner to > recognize that because of the stats I indicate above, that a sort by partnum > is almost exactly the same as a sort by partnum+franchise.  And doing a > Index scan o

Re: [PERFORM] query planner uses sequencial scan instead of index scan

Daniel Ferreira writes: > has we can see the query planner, decided to do sequencial scan in > "a_mov_rcb" table and "recibos", when i set the flag "enable_seqscan" to > false all goes well. It's not really the seqscan that's the problem. The problem is this rowcount misestimate: > "

Re: [PERFORM] Query planner making bad decisions

Cory Coager wrote: > I'm running version 8.1.11 on SLES 10 SP2. I'm trying to improve this > query and unfortunately I cannot change the application. For some > reason the planner is making a bad decision sometimes after an analyze > of table objectcustomfieldvalues. > > The query is: > SELECT D

Re: [PERFORM] Query planner making bad decisions

Tom Lane said the following on 05/11/2009 07:02 PM: where we're off by a factor of 1500+ :-( I think most likely the ~~ operator is the biggest problem. Unfortunately 8.1's estimator for ~~ is not terribly bright. You could try increasing your statistics target but I don't think it will help mu

Re: [PERFORM] Query planner making bad decisions

Cory Coager writes: > Even better yet, if I turn off enable_nestloop the query runs in > 3499.970 ms: The reason it prefers a nestloop inappropriately is a mistaken estimate that some plan node is only going to yield a very small number of rows (like one or two --- there's not a hard cutoff, but

Re: [PERFORM] Query planner cost estimate less than the sum of its parts?

I'll have to think a bit about that given that the query had run for 20 hours of 250MB/sec-ish disk reads and wasn't done. Luckily, thats not even 35% disk utilization on this system, and the 'right' query with fewer tables does things properly with a hash and takes seconds rather than hours (days

Re: [PERFORM] Query planner cost estimate less than the sum of its parts?

"Scott Carey" <[EMAIL PROTECTED]> writes: > Certainly, a cost estimate that is ... LESS than one of the sub sections of > the query is wrong. This was one hell of a broken query, but it at least > should have taken an approach that was not a nested loop, and I'm curious if > that choice was due

Re: [PERFORM] query planner and scanning methods

On Tue, Sep 23, 2008 at 3:57 PM, Richard Broersma <[EMAIL PROTECTED]> wrote: > SELECT A."dimension_book"."call", SUM( B."dimension_book"."call" ) AS > OrderedRowNbr > FROM ( your_above_query_without_the_limits ) AS A > INNER JOIN ( your_above_query_without_the_limits ) AS B > ON A."dimension_book".

Re: [PERFORM] query planner and scanning methods

On Tue, Sep 23, 2008 at 3:25 PM, Colin Copeland <[EMAIL PROTECTED]> wrote: >>> dimension=# EXPLAIN ANALYZE >>> SELECT DISTINCT ON ("dimension_book"."call") >>> "dimension_book"."title" >>> FROM "dimension_book" >>> INNER JOIN "dimension_library_books" >>> ON ("dimension_b

Re: [PERFORM] query planner and scanning methods

On Sep 23, 2008, at 6:07 PM, Richard Broersma wrote: On Tue, Sep 23, 2008 at 2:22 PM, Colin Copeland <[EMAIL PROTECTED] > wrote: dimension=# EXPLAIN ANALYZE SELECT DISTINCT ON ("dimension_book"."call") "dimension_book"."title" FROM "dimension_book" INNER JOIN "dimension_lib

Re: [PERFORM] query planner and scanning methods

On Tue, Sep 23, 2008 at 2:22 PM, Colin Copeland <[EMAIL PROTECTED]> wrote: > dimension=# EXPLAIN ANALYZE > SELECT DISTINCT ON ("dimension_book"."call") > "dimension_book"."title" > FROM "dimension_book" > INNER JOIN "dimension_library_books" > ON ("dimension_book"."i

Re: [PERFORM] query planner not using the correct index

Just for closure I ended up doing ALTER TABLE bars ALTER COLUMN bars_id SET STATISTICS 500; On Thu, Aug 7, 2008 at 7:11 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > "Joshua Shanks" <[EMAIL PROTECTED]> writes: >> How do I increase the stats target for just one column? > > Look under ALTER TABLE. > >

Re: [PERFORM] query planner not using the correct index

"Joshua Shanks" <[EMAIL PROTECTED]> writes: > How do I increase the stats target for just one column? Look under ALTER TABLE. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.

Re: [PERFORM] query planner not using the correct index

Yeah with default_statistics_target at 500 most_common_vals had 4 values with the fourth having a frequency of 1.5% and distinct have 250+ in it. How do I increase the stats target for just one column? On Thu, Aug 7, 2008 at 6:48 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > "Joshua Shanks" <[EMAIL P

Re: [PERFORM] query planner not using the correct index

"Joshua Shanks" <[EMAIL PROTECTED]> writes: > SELECT null_frac, n_distinct, most_common_vals, most_common_freqs FROM > pg_stats WHERE tablename = 'bars' AND attname='bars_id'; > null_frac | n_distinct | most_common_vals | most_common_freqs > ---++--+

Re: [PERFORM] query planner not using the correct index

On Thu, Aug 7, 2008 at 5:38 PM, Gregory Stark <[EMAIL PROTECTED]> wrote: > Measuring n_distinct from a sample is inherently difficult and unreliable. > When 98% of your table falls into those categories it's leaving very few > chances for the sample to find many other distinct values. > > I haven't

Re: [PERFORM] query planner not using the correct index

"Joshua Shanks" <[EMAIL PROTECTED]> writes: > Those 3 values in reality and in the stats account for 98% of the > rows. actual distinct values are around 350 Measuring n_distinct from a sample is inherently difficult and unreliable. When 98% of your table falls into those categories it's leaving

Re: [PERFORM] query planner not using the correct index

On Thu, Aug 7, 2008 at 4:12 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > Well, you haven't told us how big any of these tables are, so it's > hard to tell if the n_distinct value is wrong or not ... but in > any case I don't think that the stats on attr1 have anything to do > with your problem. The r

Re: [PERFORM] query planner not using the correct index

"Joshua Shanks" <[EMAIL PROTECTED]> writes: > It seems obvious that the stats on attr1 at the current level are > inaccurate as there are over 100,000 unique enteries in the table. Well, you haven't told us how big any of these tables are, so it's hard to tell if the n_distinct value is wrong or n

Re: [PERFORM] query planner not using the correct index

On Wed, Aug 6, 2008 at 10:24 PM, Craig Ringer <[EMAIL PROTECTED]> wrote: > OK, that's interesting. There are ways to examine Pg's statistics on > columns, get an idea of which stats might be less than accurate, etc, > but I'm not really familiar enough with it all to give you any useful > advice on

Re: [PERFORM] query planner not using the correct index

Joshua Shanks wrote: >> - Have you changed the random page cost on either installation? > > This is whatever the default is for both boxes (commented config file says > 4.0) > >> - Have both installations had VACUUM ANALYZE run recently? > > This is the first thing I did and didn't seem to do a

Re: [PERFORM] query planner not using the correct index

> - Have you changed the random page cost on either installation? This is whatever the default is for both boxes (commented config file says 4.0) > - Have both installations had VACUUM ANALYZE run recently? This is the first thing I did and didn't seem to do anything. Oddly enough I just went a

Re: [PERFORM] query planner not using the correct index

Joshua Shanks wrote: > This query is run on a test system just after a backup of the database > has been restored and it does exactly what I expect it to do [snip] Obvious questions: - Have you changed the random page cost on either installation? - Have both installations had VACUUM ANALYZE run

Re: [PERFORM] Query planner unaware of possibly best plan

Tom Lane <[EMAIL PROTECTED]> wrote: > The point here is that you've repeated the same example N times > without actually making a case that it's interesting to support. We > have to think about the intellectual complexity that would be added > to the planner to support this case, and the cycles t

Re: [PERFORM] Query planner unaware of possibly best plan

Denes Daniel <[EMAIL PROTECTED]> writes: > Simon Riggs <[EMAIL PROTECTED]> wrote: >> Make the case. **I** want it is not sufficient... > Sorry, I can't understand that... I'm far from perfect in english. The point here is that you've repeated the same example N times without actually making a cas

Re: [PERFORM] Query planner unaware of possibly best plan

Simon Riggs <[EMAIL PROTECTED]> wrote: > On Fri, 2007-09-21 at 21:20 +0200, Dániel Dénes wrote: > > > The costs may be different because I've tuned the query planner's > > parameters. > > OK, understood. > > > > Ordering by parent, child is fairly common but the variation you've > > > got here

Re: [PERFORM] Query planner unaware of possibly best plan

In reply to Alvaro Herrera: > The best thing to do is paste them in a text file and send it as an > attachment. Okay, it's attached. > Why did you set enable_sort=off? It's not like sorting 9 rows is going > to take any noticeable amount of time anyway. Of course it's no problem for 9 rows, bu

Re: [PERFORM] Query planner unaware of possibly best plan

Denes Daniel wrote: > I've just inserted some newlines, so it's better to read than when my > email-client wraps the lines automatically. Did not touch the information > itself. But here is the normal output of EXPLAIN ANALYZE: The best thing to do is paste them in a text file and send it as an

Re: [PERFORM] Query planner unaware of possibly best plan

Simon Riggs <[EMAIL PROTECTED]> írta: > Ordering by parent, child is fairly common but the variation you've got > here isn't that common. You'd need to make a case considering all the > alternatives; nobody will agree without a balanced case that includes > what is best for everyone. > > Your EXP

Re: [PERFORM] Query planner unaware of possibly best plan

On Fri, 2007-09-21 at 17:36 +0200, Denes Daniel wrote: > Even though I forced the nested loop plan using both indexes (that > returns the rows in the correct order), there is a needless sort step on > the top, consuming half of the time even on such small tables. > Now it's clear why the planner

Re: [PERFORM] Query Planner

On Mon, Feb 26, 2007 at 05:19:05PM +0530, Gauri Kanekar wrote: > I have a Query. So when i do explain analyse on it , it shows me many Hash > Joins. > So is it possible to indicate the Query Planner not to consider Hash Join. set enable_hashjoin = false; This is very often the wrong solution, tho

Re: [PERFORM] query planner: automatic rescribe of LIKE to BETWEEN ?

Ulrich Habel <[EMAIL PROTECTED]> writes: > Anythings speeks against this hack? Only that it was done years ago. As Alvaro mentions, if you are using a non-C locale then you need non-default index opclasses to get it to work. Non-C locales usually have index sort orders that don't play nice with

Re: [PERFORM] query planner: automatic rescribe of LIKE to BETWEEN ?

Thomas Samson wrote: > On 8/22/06, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > >Ulrich Habel wrote: > >> Hello all, > >> had an idea of optimizing a query that may work generally. > >> > >> In case a 'column' is indexed, following two alterations could be done > >> I think: > >> > >> A) > >> > >>

Re: [PERFORM] query planner: automatic rescribe of LIKE to BETWEEN ?

On 8/22/06, Alvaro Herrera <[EMAIL PROTECTED]> wrote: Ulrich Habel wrote: > Hello all, > had an idea of optimizing a query that may work generally. > > In case a 'column' is indexed, following two alterations could be done > I think: > > A) > > select ... where column ~ '^Foo' --> Seq Scan

Re: [PERFORM] query planner: automatic rescribe of LIKE to BETWEEN ?

Ulrich Habel wrote: > Hello all, > had an idea of optimizing a query that may work generally. > > In case a 'column' is indexed, following two alterations could be done > I think: > > A) > > select ... where column ~ '^Foo' --> Seq Scan This is not true. You can make this query use an i

Re: Spotting planner errors (was Re: [PERFORM] Query planner is using

Tom Lane wrote: Richard Huxton writes: Tom - does the planner/executor know it's got row estimates wrong? That is, if I'm not running an EXPLAIN ANALYSE is there a point at which we could log "planner estimate for X out by factor of Y"? Not at the moment, but you could certainly imagine chan

Re: Spotting planner errors (was Re: [PERFORM] Query planner is using wrong index.)

Richard Huxton writes: > Tom - does the planner/executor know it's got row estimates wrong? That > is, if I'm not running an EXPLAIN ANALYSE is there a point at which we > could log "planner estimate for X out by factor of Y"? Not at the moment, but you could certainly imagine changing the exec

Spotting planner errors (was Re: [PERFORM] Query planner is using wrong index.)

Tom Lane wrote: Brian Herlihy <[EMAIL PROTECTED]> writes: Before I go, I have a question - From discussions on the Postgresql irc channel, and from reading the TODO list on the website, I am under the impression that there are no plans to allow optimizer hints, such as "use index table_pkey". I

Re: [PERFORM] Query planner is using wrong index.

Brian Herlihy <[EMAIL PROTECTED]> writes: > Before I go, I have a question - From discussions on the Postgresql irc > channel, and from reading the TODO list on the website, I am under the > impression that there are no plans to allow optimizer hints, such as "use > index > table_pkey". Is this r

Re: [PERFORM] Query planner is using wrong index.

--- Tom Lane <[EMAIL PROTECTED]> wrote: > Brian Herlihy <[EMAIL PROTECTED]> writes: > > My options seem to be > > - Fudge the analysis results so that the selectivity estimate changes. I > > have tested reducing n_distinct, but this doesn't seem to help. > > - Combine the columns into one col

Re: [PERFORM] Query planner is using wrong index.

Brian Herlihy <[EMAIL PROTECTED]> writes: > My options seem to be > - Fudge the analysis results so that the selectivity estimate changes. I > have tested reducing n_distinct, but this doesn't seem to help. > - Combine the columns into one column, allowing postgres to calculate the > combined

Re: [PERFORM] Query planner is using wrong index.

--- Dave Dutcher <[EMAIL PROTECTED]> wrote: > > -Original Message- > > To: pgsql-performance@postgresql.org > > Subject: Re: [PERFORM] Query planner is using wrong index. > [Snip] > > I am really surprised that I have to go through such contortions just

Re: [PERFORM] Query planner is using wrong index.

> -Original Message- > From: [EMAIL PROTECTED] [mailto:pgsql-performance- > [EMAIL PROTECTED] On Behalf Of Brian Herlihy > Sent: Thursday, April 06, 2006 6:56 PM > To: pgsql-performance@postgresql.org > Subject: Re: [PERFORM] Query planner is using wrong index. [S

Re: [PERFORM] Query planner is using wrong index.

--- Ragnar <[EMAIL PROTECTED]> wrote: > On fös, 2006-04-07 at 00:01 +1000, Brian Herlihy wrote: > > Index Scan using p2_p3_idx on t (cost=0.00..6.02 rows=1 width=102) > (actual > > time=2793.247..2793.247 rows=0 loops=1) > >Index Cond: (((p2)::text = 'uk.altavista.com'::text) AND ((p3)::tex

Re: [PERFORM] Query planner is using wrong index.

On fös, 2006-04-07 at 00:01 +1000, Brian Herlihy wrote: > --- Ragnar <[EMAIL PROTECTED]> wrote: > > > On fim, 2006-04-06 at 19:27 +1000, Brian Herlihy wrote: > > > > > Yes, the primary key is far better. I gave it the ultimate test - I > > dropped > > > the (p2, p3) index. It's blindingly fast

Re: [PERFORM] Query planner is using wrong index.

--- Ragnar <[EMAIL PROTECTED]> wrote: > On fim, 2006-04-06 at 19:27 +1000, Brian Herlihy wrote: > > > Yes, the primary key is far better. I gave it the ultimate test - I > dropped > > the (p2, p3) index. It's blindingly fast when using the PK, > > I have problems understanding exactly how an

Re: [PERFORM] Query planner is using wrong index.

On fim, 2006-04-06 at 19:27 +1000, Brian Herlihy wrote: > --- Ragnar <[EMAIL PROTECTED]> wrote: > > > On fim, 2006-04-06 at 12:35 +1000, Brian Herlihy wrote: > > ... > > > PRIMARY KEY (p1, p2, p3) ... > > > > > > I have also created an index on (p2, p3), as some of my lookups are on > > > these

Re: [PERFORM] Query planner is using wrong index.

--- Ragnar <[EMAIL PROTECTED]> wrote: > On fim, 2006-04-06 at 12:35 +1000, Brian Herlihy wrote: > > > I have a problem with the choice of index made by the query planner. > > > > My table looks like this: > > > > CREATE TABLE t > > ( > > p1 varchar not null, > > p2 varchar not null, > >

Re: [PERFORM] Query planner is using wrong index.

On fim, 2006-04-06 at 12:35 +1000, Brian Herlihy wrote: > I have a problem with the choice of index made by the query planner. > > My table looks like this: > > CREATE TABLE t > ( > p1 varchar not null, > p2 varchar not null, > p3 varchar not null, > i1 integer, > i2 integer, > i3 in

Re: [PERFORM] Query planner issue

Mark Lewis <[EMAIL PROTECTED]> writes: > You really, really want to upgrade as soon as possible, No, sooner than that. Show your boss the list of known data-loss-causing bugs in 7.2.1, and refuse to take responsibility if the database eats all your data before the "in good time" upgrade. The rel

Re: [PERFORM] Query planner issue

On Mon, Jan 30, 2006 at 03:26:23PM -0800, Mark Lewis wrote: > You have lots of dead rows. Do a vacuum full to get it under control, > then run VACUUM more frequently and/or increase your FSM settings to > keep dead rows in check. In 7.2 vacuum is pretty intrusive; it will be > much better behaved

Re: [PERFORM] Query planner issue

with Postgresql 7.2.1 you will need to do BOTH vacuum and reindex and with a table that gets many updates/deletes, you should run vacuum more than daily. Both issues have been solved in 8.1. Jim -- Original Message --- From: Emmanuel Lacour <[EMAIL PROTECTED]> To: pgsql-perfo

Re: [PERFORM] Query planner issue

You have lots of dead rows. Do a vacuum full to get it under control, then run VACUUM more frequently and/or increase your FSM settings to keep dead rows in check. In 7.2 vacuum is pretty intrusive; it will be much better behaved once you can upgrade to a more recent version. You really, really

Re: [PERFORM] Query planner problem

Russell Smith <[EMAIL PROTECTED]> writes: > The Index does not store NULL values This is false. Though the fact that NULL values are indexed in postgres doesn't help with this poster's actual problem. -- greg ---(end of broadcast)--- TIP 2: yo

Re: [PERFORM] Query planner problem

Ryan VanMiddlesworth <[EMAIL PROTECTED]> writes: > And here is the query and EXPLAIN from the version that I believe the planner > should reduce to be logically equivalent: > SELECT case_id FROM case_data > WHERE (('2004-09-16' IS NULL) OR (case_filed_date > '2004-09-16')) >AND (('2004-09-20

Re: [PERFORM] Query planner problem

On Sat, 2 Oct 2004 08:06 am, Ryan VanMiddlesworth wrote: [snip] > > > Here is the query and EXPLAIN that runs quickly: > SELECT case_id FROM case_data > WHERE case_filed_date > '2004-09-16' > AND case_filed_date < '2004-09-20' > >QUERY PLAN > --

Re: [PERFORM] Query planner plans very inefficient plans

>I have somewhere around 3M rows in the image table, and 37K rows in the >ancestry table. The following is representative of some of the common >queries I issue: > >select * from image natural join ancestry where ancestorid=100 and >(state & 7::bigint) = 0::bigint; >

Re: [PERFORM] Query planner plans very inefficient plans

"Robert Wille" <[EMAIL PROTECTED]> writes: > select * from image natural join ancestry where ancestorid=100 and > (state & 7::bigint) = 0::bigint; The planner is not going to have any statistics that allow it to predict the number of rows satisfying that &-condition, and so it's unsurprising i