Re: [GENERAL] optimizing a query

2016-06-22 Thread David G. Johnston
On Wed, Jun 22, 2016 at 2:23 PM, Jonathan Vanasco wrote: > > Note 2: > > This is odd, but this index is used by the planner: > CREATE INDEX idx_partial_fkey_id ON table_a(fkey_1, id) > WHERE col_partial IS NOT FALSE; > > but this index is never

Re: [GENERAL] optimizing a query

2016-06-22 Thread Jonathan Vanasco
On Jun 22, 2016, at 2:38 PM, David G. Johnston wrote: > What query? ​A self-contained email would be nice.​ This was the same query as in the previous email in the thread. I didn't think to repeat it. I did include it below. >

Re: [GENERAL] optimizing a query

2016-06-22 Thread David G. Johnston
On Wed, Jun 22, 2016 at 2:23 PM, Jonathan Vanasco wrote: > > On Jun 22, 2016, at 4:25 AM, Erik Gustafson wrote: > > > don't you want an index on t_a2b.col_a, maybe partial where col_a=1 ? > > that table has indexes on all columns. they're never referenced because > the rows

Re: [GENERAL] optimizing a query

2016-06-22 Thread Jonathan Vanasco
On Jun 22, 2016, at 4:25 AM, Erik Gustafson wrote: > don't you want an index on t_a2b.col_a, maybe partial where col_a=1 ? that table has indexes on all columns. they're never referenced because the rows are so short. this was just an example query too, col_a has 200k variations After a

Re: [GENERAL] optimizing a query

2016-06-22 Thread Erik Gustafson
Hi, don't you want an index on t_a2b.col_a, maybe partial where col_a=1 ? On Wed, Jun 22, 2016 at 2:07 AM, Jonathan Vanasco wrote: > > On Jun 21, 2016, at 6:55 PM, David G. Johnston wrote: > > ​Aside from the name these indexes are identical...​ > > > sorry. tired eyes

Re: [GENERAL] optimizing a query

2016-06-21 Thread Jonathan Vanasco
On Jun 21, 2016, at 6:55 PM, David G. Johnston wrote: > ​Aside from the name these indexes are identical...​ sorry. tired eyes copy/pasting between windows and trying to 'average' out 40 similar queries. > ​These two items combined reduce the desirability of diagnosing this...it > doesn't

Re: [GENERAL] optimizing a query

2016-06-21 Thread David G. Johnston
On Tue, Jun 21, 2016 at 6:44 PM, Adrian Klaver wrote: > On 06/21/2016 03:33 PM, Jonathan Vanasco wrote: > >> >> >> In effort of simplifying the work, I've created indexes on t_a that have >> all the related columns. >> >> CREATE INDEX test_idx ON t_a(col_1, id)

Re: [GENERAL] optimizing a query

2016-06-21 Thread Adrian Klaver
On 06/21/2016 03:33 PM, Jonathan Vanasco wrote: I have a handful of queries in the following general form that I can't seem to optimize any further (same results on 9.3, 9.4, 9.5) I'm wondering if anyone might have a suggestion, or if they're done. The relevant table structure: t_a2b

[GENERAL] optimizing a query

2016-06-21 Thread Jonathan Vanasco
I have a handful of queries in the following general form that I can't seem to optimize any further (same results on 9.3, 9.4, 9.5) I'm wondering if anyone might have a suggestion, or if they're done. The relevant table structure: t_a2b a_id INT references t_a(id)

[GENERAL] optimizing a query with sub select

2010-09-29 Thread Georgi Ivanov
Hi, I have this query SELECT * FROM v_material WHERE show_in_recent AND section_id IN ( SELECT s.id FROM section AS s, section AS s2 WHERE s2.id = 842 AND s.breadcrumb @ s2.breadcrumb ) ORDER BY published_on DESC LIMIT 3;

Re: [GENERAL] optimizing a query with sub select

2010-09-29 Thread Grzegorz Jaśkiewicz
Use JOIN sherlock. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general