Re: What is faster?

ON.KG wrote:
> Hi All!
>
>>>What is faster - SLECTion data from one large table (200 000 - 300 000
>>>records), or SELECTion from a few small tables (example, 2 tables 150
>>>000 records each)?
>
> RH> It depends. Are you selecting all records? One record? A few records? If
> RH> one or a few, do you have a suitable index on the table(s)? Is the table
> RH> clustered? Expected to be cached in RAM?
>
> RH> Do you have a specific problem, or reason to believe you may encounter one?
>
> It's a real problem
>
> For example i have two large tables
> Structure of tables is same - has two fields - id, ip
>
> Now i'm using two selection from each in one transaction
> Each of them selects only one record
> selection clase like WHERE ip = 'xxx.xxx.xxx.xxx'
> so it is searches existance of IP in each table
>
> tables are clustered

OK - so the tables aren't updated frequently, I assume. Do you have an
index on "ip"?

> about cached in RAM - i'm novice in Postgresql - how does it work?

The operating-system will keep frequently used disk-blocks in memory.
You don't have to do anything. Have you done any performance tuning in
your postgresql.conf file? If not, try reading:
http://www.powerpostgresql.com/PerfList

> now i need to make much faster as it is possible
> and have an idea just merge two tables in one - will it help me?

If they hold the same information, they probably shouldn't have been
split in the first place.

--
Richard Huxton
Archonet Ltd