Re: Index not used in query. Why?
От | Thomas F.O'Connell |
---|---|
Тема | Re: Index not used in query. Why? |
Дата | |
Msg-id | 0736EED9-22B3-11D9-A5E5-000D93AE0944@sitening.com обсуждение исходный текст |
Ответ на | Re: Index not used in query. Why? ("Contact AR-SD.NET" <contact@ar-sd.net>) |
Список | pgsql-performance |
There's a chance that you could gain from quoting the '4' and '6' if those orders.id_status isn't a pure int column and is indexed. See http://www.postgresql.org/docs/7.4/static/datatype.html#DATATYPE-INT -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Oct 19, 2004, at 12:49 PM, Contact AR-SD.NET wrote: > Is there a solution to make it faster? > At the end I need only in the query the id_status =4 and 6, but if I > write > in the sql query (where condition) where id_status in (4,6), the > explain > says the same(the slow version). > > For example: > SELECT count(o.id) FROM orders o > INNER JOIN report r ON > o.id=r.id_order > INNER JOIN status s ON > o.id_status=s.id > INNER JOIN contact c ON o.id_ag=c.id > INNER JOIN endkunde e ON > o.id_endkunde=e.id > INNER JOIN zufriden z ON > r.id_zufriden=z.id > INNER JOIN plannung v ON > v.id=o.id_plannung > INNER JOIN mpsworker w ON > v.id_worker=w.id > INNER JOIN person p ON p.id = > w.id_person > WHERE o.id_status in (4,6); > > The result for this query is also without index searches. > > I really have to make this query a little more faster. Suggestions? > > Regards, > Andy. > > ----- Original Message ----- > From: "Tom Lane" <tgl@sss.pgh.pa.us> > To: "Andrei Bintintan" <klodoma@ar-sd.net> > Cc: <pgsql-performance@postgresql.org> > Sent: Tuesday, October 19, 2004 7:52 PM > Subject: Re: [PERFORM] Index not used in query. Why? > > >> "Andrei Bintintan" <klodoma@ar-sd.net> writes: >>> Hi to all! I have the following query. The execution time is very >>> big, > it >>> doesn't use the indexes and I don't understand why... >> >> Indexes are not necessarily the best way to do a large join. >> >>> If I use the following query the indexes are used: >> >> The key reason this wins seems to be that the id_status = 4 condition >> is far more selective than id_status > 3 (the estimates are 52 and >> 36967 >> rows respectively ... is that accurate?) which means that the second >> query is inherently about 1/700th as much work. This, and not the use >> of indexes, is the fundamental reason why it's faster. >> >> regards, tom lane >> >> ---------------------------(end of >> broadcast)--------------------------- >> TIP 4: Don't 'kill -9' the postmaster >> > > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to > majordomo@postgresql.org)
В списке pgsql-performance по дате отправления: