Re: Big IN() clauses etc : feature proposal
От | PFC |
---|---|
Тема | Re: Big IN() clauses etc : feature proposal |
Дата | |
Msg-id | op.s8916gm6cigqcu@apollo13 обсуждение исходный текст |
Ответ на | performance question (something to do w/ parameterized stmts?, wrong index types?) (Jeffrey Tenny <jeffrey.tenny@comcast.net>) |
Ответы |
Re: Big IN() clauses etc : feature proposal
Re: [HACKERS] Big IN() clauses etc : feature proposal Re: [HACKERS] Big IN() clauses etc : feature proposal |
Список | pgsql-performance |
> You might consider just selecting your primary key or a set of > primary keys to involved relations in your search query. If you > currently use "select *" this can make your result set very large. > > Copying all the result set to the temp. costs you additional IO > that you propably dont need. It is a bit of a catch : I need this information, because the purpose of the query is to retrieve these objects. I can first store the ids, then retrieve the objects, but it's one more query. > Also you might try: > SELECT * FROM somewhere JOIN result USING (id) > Instead of: > SELECT * FROM somewhere WHERE id IN (SELECT id FROM result) Yes you're right in this case ; however the query to retrieve the owners needs to eliminate duplicates, which IN() does. > On the other hand if your search query runs in 10ms it seems to be fast > enough for you to run it multiple times. Theres propably no point in > optimizing anything in such case. I don't think so : - 10 ms is a mean time, sometimes it can take much more time, sometimes it's faster. - Repeating the query might yield different results if records were added or deleted in the meantime. - Complex search queries have imprecise rowcount estimates ; hence the joins that I would add to them will get suboptimal plans. Using a temp table is really the cleanest solution now ; but it's too slow so I reverted to generating big IN() clauses in the application.
В списке pgsql-performance по дате отправления: