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.

alf Of Oliver Mattos Sent: Monday, November 13, 2017 5:45 PM To: pgsql-performance@postgresql.org Subject: [PERFORM] Query planner gaining the ability to replanning after start of query execution. I am interested in giving the query planner the ability to replan (or re-rank plans) after query e

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.

doubt > that's worth any work (and even less the maintenance). > > Best regards > Arne Roland > > -Original Message- > From: pgsql-performance-ow...@postgresql.org > [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Oliver Mattos > Sent: Monday, Novemb

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

I am interested in giving the query planner the ability to replan (or re-rank plans) after query execution has begun, based on the progression of the query so far. Example use case: * A LIMIT 1 query is planned using an expensive scan which the planner expects to return a large number of results

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

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

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 use an index that we anticapited it would. The query is select bookin

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 ?

[PERFORM] Query planner wants to use seq scan

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 ? An information which can be useful : the number

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

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

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 I have indexes on the relevant "filename" columns. Note t

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

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

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 composite index. The solution is to add a second indexe (redond

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

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

Hello all, I'm running into an issue when joining between to tables that are partitioned by month. At this point I'm leaning towards it being a bug in the planner but it could be due to something I'm not doing properly as well. Each parent table is empty and has about 30 child tables, and there

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

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

Some info: PostgreSQL version: 9.1.2 Table "cache": Rows count: 3 471 081 Column "tsv" tsvector Index "cache_tsv" USING gin (tsv) If i do query like THIS: *SELECT id FROM table WHERE tsv @@ to_tsquery('test:*');* It uses index and returns results immediately: explain analyze 'Bitmap Heap Scan o

[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

Some info: PostgreSQL version: 9.1.2 Table "cache": Rows count: 3 471 081 Column "tsv" tsvector Index "cache_tsv" USING gin (tsv) If i do query like THIS: *SELECT id FROM table WHERE tsv @@ to_tsquery('test:*');* It uses index and returns results immediately: explain analyze 'Bitmap Heap Scan o

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

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

Hi all. Been using postgres for years, and lurking on this newsgroup for a short while now to help me gain the benefit of your expertise and experience and learn how to get most out of postgresql possible. I do a fair bit of work on tables using composite keys. I have discovered a couple of thin

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: > "

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

Hi all, iam having trouble with a query, in this query we have parameters, to indicate the starting month and the ending month (commented line in the query). You can see the explain using the parameters of month from 1 to 6 EXPLAIN ANALYZE select pq.nome, mv.data, mv.valor, gprd.tipo,

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

[PERFORM] Query planner making bad decisions

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 DISTINCT main.* FROM Tickets

[PERFORM] Query planner plus partitions equals very bad plans, again

Here is a query on a partitioned schema that produces a very bad query plan. The tables are fully vacuumed, analyzed with stats target 40, and no bloat (created with pure inserts, no updates or deletes). I already know of at least three bugs with the query planner and partitions listed at the

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

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

So, we had a query run accidentally without going through the right checks to ensure that it had the right limits in a where clause for our table partitioning, resulting in an attempt to scan TB's of data. Obviously, we fixed the query, but the curious result is this explain plan (shortened, in fu

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

[PERFORM] query planner and scanning methods

Hello, I'm running into performance issues with various queries on a PostgreSQL database (of books). I'm having trouble understanding the thinking behind the query planner in this scenario: http://dpaste.com/hold/80101/ (also attached at bottom of email) Relation sizes: dimension_books: 19

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

[PERFORM] query planner not using the correct index

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 EXPLAIN ANALYZE SELECT foos.* FROM foos INNER JOIN bars ON foos.id = bars.foos_id WHERE ((bars.bars_id = 12345)) ORDER BY attr1 LIMIT 3 OFFSET 0; QUERY PLAN ---

[PERFORM] Query Planner not choosing hash_aggregate appropriately.

The query optimizer fails to use a hash aggregate most of the time. This is an inconsistent behavior -- the queries below were happily using hash_aggregate on a previous pg_restore from the data. On one particular class of tables this is especially painful. The example table has 25 million rows,

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

[PERFORM] Query planner unaware of possibly best plan

Hi, I think the query planner is unaware of the possibly best plan in some situations. See the test case below: -- --- -- CREATE TABLE tparent ( id integer NOT NULL, ord integer NOT NULL, CONSTRAINT par_pkey_id PRIMARY KEY (id), CONSTRAINT

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

[PERFORM] Query Planner

Hi List, 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. -- Regards Gauri

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

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

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 into that: select ... where column BETWEEN 'Foo' AND 'FooZ' --> Index Scan of co

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

[PERFORM] Query planner is using wrong index.

Hi, 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 integer, i4 integer, i5 integer, d1 date, d2 date, d3 date, PRIMAR

  1   2   >