Re: Optimizing queries
От | Patrice Beliveau |
---|---|
Тема | Re: Optimizing queries |
Дата | |
Msg-id | 44D9EFC5.4070708@avior.ca обсуждение исходный текст |
Ответ на | Re: Optimizing queries (Ruben Rubio <ruben@rentalia.com>) |
Список | pgsql-performance |
I've create a view, same query plan (some number vary a bit, but nothing significant) and same result, closed sales_order are processed Ruben Rubio wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > If subquerys are not working I think you should try to create a view > with the subquery. > > Maybe it will work. > > Patrice Beliveau wrote: > >> Tom Lane wrote: >> >>> Patrice Beliveau <pbeliveau@avior.ca> writes: >>> >>> >>>>>> SELECT * FROM TABLE >>>>>> WHERE TABLE.COLUMN1=something >>>>>> AND TABLE.COLUMN2=somethingelse >>>>>> AND function(TABLE.COLUMN3,TABLE.COLUMN4) > 0; >>>>>> >>>>>> >>> >>> >>>> I find out that the function process every row even if the row should >>>> be rejected as per the first or the second condition. >>>> ... I'm using version 8.1.3 >>>> >>>> >>> PG 8.1 will not reorder WHERE clauses for a single table unless it has >>> some specific reason to do so (and AFAICT no version back to 7.0 or so >>> has done so either...) So there's something you are not telling us that >>> is relevant. Let's see the exact table schema (psql \d output is good), >>> the exact query, and EXPLAIN output for that query. >>> >>> regards, tom lane >>> >>> ---------------------------(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 >>> >>> >>> >>> >> Hi, >> >> here is my query, and the query plan that result >> >> explain select * from ( >> select * from sales_order_delivery >> where sales_order_id in ( >> select sales_order_id from sales_order >> where closed=false >> ) >> ) as a where outstandingorder(sales_order_id, sales_order_item, >> date_due) > 0; >> >> >> QUERY PLAN >> ---------------------------------------------------------------------------------------------------------------------- >> >> Hash IN Join (cost=498.89..8348.38 rows=34612 width=262) >> Hash Cond: (("outer".sales_order_id)::text = >> ("inner".sales_order_id)::text) >> -> Seq Scan on sales_order_delivery (cost=0.00..6465.03 rows=69223 >> width=262) >> Filter: (outstandingorder((sales_order_id)::text, >> (sales_order_item)::text, date_due) > 0::double precision) >> -> Hash (cost=484.90..484.90 rows=5595 width=32) >> -> Seq Scan on sales_order (cost=0.00..484.90 rows=5595 width=32) >> Filter: (NOT closed) >> (7 rows) >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 3: Have you checked our extensive FAQ? >> >> http://www.postgresql.org/docs/faq >> >> >> > > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.2.2 (GNU/Linux) > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org > > iD8DBQFE2dMTIo1XmbAXRboRAhbIAJwJGZ+ITP0gl38A3qROrzIeNbTtUwCcDOIW > eZ9NJqjL+58gyMfO95jwZSw= > =4Zxj > -----END PGP SIGNATURE----- > > >
В списке pgsql-performance по дате отправления:
Предыдущее
От: "Merlin Moncure"Дата:
Сообщение: Re: Hardware upgraded but performance still ain't good enough