Thread
-
Re: join and query planner
Kevin Grittner <kevin.grittner@wicourts.gov> — 2005-07-18T20:47:34Z
You might want to set join_collapse_limit high, and use the JOIN operators rather than the comma-separated lists. We generate the WHERE clause on the fly, based on user input, and this has worked well for us. -Kevin >>> "Dario" <dario_d_s@unitech.com.ar> 07/18/05 2:24 PM >>> Hi. > Just out of curiosity, does it do any better with the following? > > SELECT ... Yes, it does. But my query could also be SELECT ... FROM a JOIN b ON (a.key = b.key) LEFT JOIN c ON (c.key = a.key) LEFT JOIN d ON (d.key=a.key) /*new*/ , e WHERE (b.column <= 100) /*new*/ and (e.key = a.key) and (e.field = 'filter') because it's constructed by an application. I needed to know if, somehow, someway, I can "unforce" join order. The only way to solve it so far is changing application. It must build something like SELECT ... FROM b JOIN (a JOIN e ON (e.key = a.key)) ON (a.key = b.key) LEFT JOIN c ON (c.key = a.key) LEFT JOIN d ON (d.key=a.key) WHERE (b.column <= 100) and (e.field = 'filter') Supossed that e.field has (should have) better selectivity. But now this problem belongs to programmer's group :-) The query, in fact, has more tables to join. I wonder if lowering geqo threshold could do the work... Thank you. Greetings. Long life, little spam and prosperity! -----Mensaje original----- De: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org]En nombre de Kevin Grittner Enviado el: lunes, 18 de julio de 2005 14:58 Para: pgsql-performance@postgresql.org; dariop@unitech.com.ar Asunto: Re: [PERFORM] join and query planner Just out of curiosity, does it do any better with the following? SELECT ... FROM a JOIN b ON (a.key = b.key) LEFT JOIN c ON (c.key = a.key) LEFT JOIN d ON (d.key=a.key) WHERE (b.column <= 100) >>> snipp ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings -
Re: join and query planner
Dario Pudlo <dario_d_s@unitech.com.ar> — 2005-07-19T21:41:18Z
I'll try that. Let you know as soon as I can take a look. Thank you- -----Mensaje original----- De: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org]En nombre de Kevin Grittner Enviado el: lunes, 18 de julio de 2005 17:48 Para: pgsql-performance@postgresql.org; dario_d_s@unitech.com.ar Asunto: Re: [PERFORM] join and query planner You might want to set join_collapse_limit high, and use the JOIN operators rather than the comma-separated lists. We generate the WHERE clause on the fly, based on user input, and this has worked well for us. -Kevin >>> "Dario" <dario_d_s@unitech.com.ar> 07/18/05 2:24 PM >>> Hi. > Just out of curiosity, does it do any better with the following? > > SELECT ... Yes, it does. But my query could also be SELECT ... FROM a JOIN b ON (a.key = b.key) LEFT JOIN c ON (c.key = a.key) LEFT JOIN d ON (d.key=a.key) /*new*/ , e WHERE (b.column <= 100) /*new*/ and (e.key = a.key) and (e.field = 'filter') because it's constructed by an application. I needed to know if, somehow, someway, I can "unforce" join order. The only way to solve it so far is changing application. It must build something like SELECT ... FROM b JOIN (a JOIN e ON (e.key = a.key)) ON (a.key = b.key) LEFT JOIN c ON (c.key = a.key) LEFT JOIN d ON (d.key=a.key) WHERE (b.column <= 100) and (e.field = 'filter') Supossed that e.field has (should have) better selectivity. But now this problem belongs to programmer's group :-) The query, in fact, has more tables to join. I wonder if lowering geqo threshold could do the work... Thank you. Greetings. Long life, little spam and prosperity! -----Mensaje original----- De: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org]En nombre de Kevin Grittner Enviado el: lunes, 18 de julio de 2005 14:58 Para: pgsql-performance@postgresql.org; dariop@unitech.com.ar Asunto: Re: [PERFORM] join and query planner Just out of curiosity, does it do any better with the following? SELECT ... FROM a JOIN b ON (a.key = b.key) LEFT JOIN c ON (c.key = a.key) LEFT JOIN d ON (d.key=a.key) WHERE (b.column <= 100) >>> snipp ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match