pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: What is faster?
6+ messages / 3 participants
[nested] [flat]
* What is faster?
@ 2005-06-06 16:25 ON.KG <skyer@on.kg>
2005-06-06 14:45 ` Re: What is faster? Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 6+ messages in thread
From: ON.KG @ 2005-06-06 16:25 UTC (permalink / raw)
To: pgsql-sql
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)?
Thank You
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: What is faster?
2005-06-06 16:25 What is faster? ON.KG <skyer@on.kg>
@ 2005-06-06 14:45 ` Richard Huxton <dev@archonet.com>
2005-06-06 17:02 ` Re: What is faster? ON.KG <skyer@on.kg>
0 siblings, 1 reply; 6+ messages in thread
From: Richard Huxton @ 2005-06-06 14:45 UTC (permalink / raw)
To: ON.KG <skyer@on.kg>; +Cc: pgsql-sql
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)?
It depends. Are you selecting all records? One record? A few records? If
one or a few, do you have a suitable index on the table(s)? Is the table
clustered? Expected to be cached in RAM?
Do you have a specific problem, or reason to believe you may encounter one?
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: What is faster?
2005-06-06 16:25 What is faster? ON.KG <skyer@on.kg>
2005-06-06 14:45 ` Re: What is faster? Richard Huxton <dev@archonet.com>
@ 2005-06-06 17:02 ` ON.KG <skyer@on.kg>
2005-06-06 16:09 ` Re: What is faster? Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 6+ messages in thread
From: ON.KG @ 2005-06-06 17:02 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
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
about cached in RAM - i'm novice in Postgresql - how does it work?
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?
Thank You
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: What is faster?
2005-06-06 16:25 What is faster? ON.KG <skyer@on.kg>
2005-06-06 14:45 ` Re: What is faster? Richard Huxton <dev@archonet.com>
2005-06-06 17:02 ` Re: What is faster? ON.KG <skyer@on.kg>
@ 2005-06-06 16:09 ` Richard Huxton <dev@archonet.com>
2005-06-07 07:28 ` Re: What is faster? ON.KG <skyer@on.kg>
0 siblings, 1 reply; 6+ messages in thread
From: Richard Huxton @ 2005-06-06 16:09 UTC (permalink / raw)
To: ON.KG <skyer@on.kg>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: What is faster?
2005-06-06 16:25 What is faster? ON.KG <skyer@on.kg>
2005-06-06 14:45 ` Re: What is faster? Richard Huxton <dev@archonet.com>
2005-06-06 17:02 ` Re: What is faster? ON.KG <skyer@on.kg>
2005-06-06 16:09 ` Re: What is faster? Richard Huxton <dev@archonet.com>
@ 2005-06-07 07:28 ` ON.KG <skyer@on.kg>
2005-06-07 06:40 ` Re: What is faster? Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 6+ messages in thread
From: ON.KG @ 2005-06-07 07:28 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
RH> 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)?
>>
>> 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
RH> OK - so the tables aren't updated frequently, I assume. Do you have an
RH> index on "ip"?
Yes
>> about cached in RAM - i'm novice in Postgresql - how does it work?
RH> The operating-system will keep frequently used disk-blocks in memory.
RH> You don't have to do anything. Have you done any performance tuning in
RH> your postgresql.conf file? If not, try reading:
RH> http://www.powerpostgresql.com/PerfList
Server Administrator says, he did
>> 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?
RH> If they hold the same information, they probably shouldn't have been
RH> split in the first place.
Content of tables is not absolutely same
One has one kind of IPs, second - others
And there's one more addition in question - if I will merge tables,
in new table will be set new additional field - `type` char - to
determine type of IP
Thank You
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: What is faster?
2005-06-06 16:25 What is faster? ON.KG <skyer@on.kg>
2005-06-06 14:45 ` Re: What is faster? Richard Huxton <dev@archonet.com>
2005-06-06 17:02 ` Re: What is faster? ON.KG <skyer@on.kg>
2005-06-06 16:09 ` Re: What is faster? Richard Huxton <dev@archonet.com>
2005-06-07 07:28 ` Re: What is faster? ON.KG <skyer@on.kg>
@ 2005-06-07 06:40 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 0 replies; 6+ messages in thread
From: Achilleus Mantzios @ 2005-06-07 06:40 UTC (permalink / raw)
To: ON.KG <skyer@on.kg>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-sql
O ON.KG Ýãñáøå óôéò Jun 7, 2005 :
> RH> 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)?
> >>
> >> 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
>
> RH> OK - so the tables aren't updated frequently, I assume. Do you have an
> RH> index on "ip"?
>
> Yes
>
> >> about cached in RAM - i'm novice in Postgresql - how does it work?
>
> RH> The operating-system will keep frequently used disk-blocks in memory.
> RH> You don't have to do anything. Have you done any performance tuning in
> RH> your postgresql.conf file? If not, try reading:
> RH> http://www.powerpostgresql.com/PerfList
>
> Server Administrator says, he did
>
> >> 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?
>
> RH> If they hold the same information, they probably shouldn't have been
> RH> split in the first place.
>
> Content of tables is not absolutely same
> One has one kind of IPs, second - others
>
> And there's one more addition in question - if I will merge tables,
> in new table will be set new additional field - `type` char - to
> determine type of IP
>
> Thank You
Just a thought, also you could try if converting your ip from varchar(32)
to int8 (unsigned int4) would make any difference, since
the index on int8 would be more efficient.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
--
-Achilleus
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-06-07 07:28 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-06 16:25 What is faster? ON.KG <skyer@on.kg>
2005-06-06 14:45 ` Richard Huxton <dev@archonet.com>
2005-06-06 17:02 ` ON.KG <skyer@on.kg>
2005-06-06 16:09 ` Richard Huxton <dev@archonet.com>
2005-06-07 07:28 ` ON.KG <skyer@on.kg>
2005-06-07 06:40 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox