RE: Temp tables being written to disk. Avoidable? - Mailing list pgsql-sql

Hi Tom,

Thanks for your response, enlightening as always.

> Not at present --- temp tables are not different from real tables,
> except for some naming shenanigans.  So creation of a temp table will
> involve some disk hits.

Ok, would it be a good idea to modify that for the future? Given that
temp tables:-
a) can't be seen by other connections.
b) are likely to be selected upon heavily close to creation time.
c) are likely to be short lived.
is there any reason to move them out to disk unless strictly
necessary (aside from that it may take a fair bit of 
re-engineering and the core developers have other more important 
and/or more interesting things to be getting on with)?

> Do you really *need* a temp table, as opposed to writing a
> sub-SELECT-in-the-FROM-clause?  ISTM that that feature takes care
> of most of the simple notational reasons for wanting a temp table.

I have rewritten the query with such a subselect and it looks
much more elegant than my previous solution:

====
SELECT rating , entry.*
FROM  entry , (SELECT COUNT(entry_fti.id) as rating, idFROM entry_ftiWHERE (entry_fti.string ~'^blu' OR
entry_fti.string~'^word2') GROUP BY id[LIMIT X OFFSET Y])  vtable
 
WHERE vtable.id=entry.oid 
ORDER BY rating DESC
====

The only I'm missing now is the bit equivalent to the
SELECT COUNT(*) FROM searchtemp.

With the temporary table I was effectively trying to
cache that subselect (without the LIMIT) and do two
queries on it, one returning the total number of
rows and one returning information relating to a
LIMITed number of those rows.

Without a temporary table (unless there's a cunning
trick I haven't thought of) it seems I either have
a choice of:

a) Not knowing how many rows match the full text query.
b) Returning the entry details for all the rows even though  I only want a small number of them.
c) Doing two queries on the entry_fti table

It'll be interesting to see which of b) c) or using a temporary
table is more expensive. I imagine that could vary heavily
on the contents of the tables and the number of concurrent
searches that are occuring.

Thanks again.

--
Paul McGarry            mailto:paulm@opentec.com.au 
Systems Integrator      http://www.opentec.com.au 
Opentec Pty Ltd         http://www.iebusiness.com.au
6 Lyon Park Road        Phone: (02) 9870 4718 
North Ryde NSW 2113     Fax:   (02) 9878 1755  
--------------------------------------------------------------------
This document and any attachments are intended solely for
the named addressee(s), are confidential, and may be subject to
legal professional privilege. Please notify us (on +61-2 9878 1744)
as soon as possible if you have received this document in error.
Any confidentiality or privilege is not waived or lost because this
email has been sent to you by mistake. This document and any
attachments are subject to copyright.  No part of them should be
reproduced or distributed by any means whatsoever without the
prior consent of the copyright owner.  Opentec does not warrant
that this email and any attachments are error or virus free.
--------------------------------------------------------------------