Re: [HACKERS] Big IN() clauses etc : feature proposal

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




Additionally to your query you are already transferring the whole result set multiple times. First you copy it to the result table. Then you
read it again.   Your subsequent queries will also have to read over
all the unneeded tuples just to get your primary key.

Considering that the result set is not very large and will be cached in RAM, this shouldn't be a problem.

then why useth thy not the DISTINCT clause when building thy result table and thou shalt have no duplicates.

	Because the result table contains no duplicates ;)
	I need to remove duplicates in this type of queries :

-- get object owners info
SELECT * FROM users WHERE id IN (SELECT user_id FROM results);

And in this case I find IN() easier to read than DISTINCT (what I posted was a simplification of my real use case...)

which is a perfect reason to use a temp table. Another variation on the temp table scheme is use a result table and add a query_id.

True. Doesn't solve my problem though : it's still complex, doesn't have good rowcount estimation, bloats a table (I only need these records for the duration of the transaction), etc.
	
We do something like this in our web application when users submit complex queries. For each query we store tuples of (query_id,result_id)
in a result table.  It's then easy for the web application to page the
result set.

	Yes, that is about the only sane way to page big result sets.

A cleaner solution usually pays off in the long run whereas a hackish
or overly complex solution will bite you in the behind for sure as
time goes by.

Yes, but in this case temp tables add too much overhead. I wish there were RAM based temp tables like in mysql. However I guess the current temp table slowness comes from the need to mark their existence in the system catalogs or something. That's why I proposed using cursors...


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux