Re: Bad performance of SELECT ... where id IN (...)

Xia Qingran wrote:
> On Sun, Sep 27, 2009 at 1:03 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Xia Qingran <qingran.xia@gmail.com> writes:
>>> I have a big performance problem in my SQL select query:
>>> select * from event where user_id in
>>> (500,499,498, ... ,1,0);
>>> The above SELECT always spends 1200ms.
>> Your EXPLAIN ANALYZE shows that the actual runtime is only about 240ms.
>> So either the planning time is about 1000ms, or transmitting and
>> displaying the 134K rows produced by the query takes that long, or some
>> combination of the two.  I wouldn't be too surprised if it's the data
>> display that's slow; but if it's the planning time that you're unhappy
>> about, updating to a more recent PG release might possibly help.  What
>> version is this anyway?
>>
>>                        regards, tom lane
>
> Oh, It is a problem.

I don't see where the "Total runtime" information is in your first message.

Also, did you run VACUUM FULL ANALYZE lately?

> Forgot to talk about my platform. I am running PostgreSQL 8.4.0 on
> FreeBSD 7.2-amd64 box, which has dual Xeon 5410 CPUs, 8GB memory and 2
> SATA disks.
>
> And my postgresql.conf is listed as follow:
> ---------------------------------------------------------------------------------------
>
> listen_addresses = '*'        # what IP address(es) to listen on;
> port = 5432                # (change requires restart)
> max_connections = 88            # (change requires restart)
> superuser_reserved_connections = 3
> ssl = off                # (change requires restart)
> tcp_keepalives_idle = 0        # TCP_KEEPIDLE, in seconds;
> tcp_keepalives_interval = 0        # TCP_KEEPINTVL, in seconds;
> tcp_keepalives_count = 0        # TCP_KEEPCNT;
> shared_buffers = 2048MB            # min 128kB or max_connections*16kB

For start I think you will need to make shared_buffers larger than your
index to get decent performance - try setting it to 4096 MB and see if
it helps.

> temp_buffers = 32MB            # min 800kB
> max_prepared_transactions = 150        # can be 0 or more, 0 to shutdown the
> prepared transactions.
> work_mem = 8MB                # min 64kB

Depending on the type of your workload (how many clients are connected
and how complex are the queries) you might want to increase work_mem
also. Try 16 MB - 32 MB or more and see if it helps.

> fsync = off                # turns forced synchronization on or off
> synchronous_commit = off        # immediate fsync at commit

Offtopic - you probably know what you are doing by disabling these, right?