John Beaver | 1 Jul 02:37 2008
Picon

Re: sequence scan problem

<chuckle> You're right - for some reason I was looking at the (18 rows) at the bottom. Pilot error indeed - I'll have to figure out what's going on with my data.

Thanks!

Tom Lane wrote:
John Beaver <john.e.beaver <at> gmail.com> writes:
Ok, here's the explain analyze result. Again, this is Postgres 8.3.3 and I vacuumed-analyzed both tables directly after they were created.
Merge Join (cost=1399203593.41..6702491234.74 rows=352770803726 width=22) (actual time=6370194.467..22991303.434 rows=15610535128 loops=1)
^^^^^^^^^^^ Weren't you saying that only 50 rows should be returned? I'm thinking the real problem here is pilot error: you missed out a needed join condition or something. SQL will happily execute underconstrained queries ... regards, tom lane
Emiliano Leporati | 1 Jul 12:49 2008
Picon

un-understood index performance behaviour

Hi,
i have a table with a huge amount of rows (actually 4 millions and a half), defined like this:

CREATE TABLE rtp_frame (
    i_len integer NOT NULL,
    i_file_offset bigint NOT NULL,
    i_file_id integer NOT NULL,  -- foreign key
    i_timestamp bigint NOT NULL,
    i_loop integer NOT NULL,
    i_medium_id integer NOT NULL, -- foreign key
    PRIMARY KEY(i_medium_id, i_loop, i_timestamp)
);

The primary key creates the btree index.

If I ask the database something like this:

SELECT ((max(i_timestamp) - min(i_timestamp))::double precision / <rate>)
FROM rtp_frame
WHERE i_medium_id = <medium> AND i_loop = <loop>;

it replies istantaneously.

But if i ask

DECLARE blablabla INSENSITIVE NO SCROLL CURSOR WITHOUT HOLD FOR
SELECT i_file_id, i_len, i_file_offset, i_timestamp
FROM rtp_frame WHERE i_medium_id = <medium>
AND i_loop = <loop>
AND i_timestamp BETWEEN 0 and 5400000
ORDER BY i_timestamp

on a medium with, say, 4 millions rows co-related, it takes 15 seconds to reply, even with a different clause on i_timestamp (say i_timestamp >= 0), even with the ORDER BY clause specified on the three indexed columns (ORDER BY i_medium_id, i_loop, i_timestamp).

Issued on a medium with "just" some hundred thousand rows, it runs instantaneously.

If I add a single btree index on i_timestamp, it runs instantaneously event on a medium with millions rows (so having a btree(i_medium_id, i_loop, i_timestamp) and btree(i_timestamp)).

With (btree(i_medium_id, i_loop) and btree(i_timestamp)), the first for sure takes 15 seconds to run, the second i think too but not sure atm.

can anybody explain me why this happens ? and if i should try different indexes ?

thanks a lot

Emiliano
Kathirvel, Jeevanandam | 1 Jul 13:59 2008
Picon

Inact_dirty is increasing continuously and causing the system to hang.

Hi,

            We are seeing system hang-up issue when we do continuous update on table ( 2-3 records/sec) within 10-12 hours. Memory parameter Inact_dirty( shown in /proc/meminfo) is increasing continuously and causing the system to hang-up(not responding state). This issue is not happening when we stop the continuous update.

 

Please help us to resolve this issue.

 

System details follows:

OS : Linux kernel version  2.4.7-10

RAM; 256MB (but 64MB used by RAM file system)

PostgreSQL version:7.4.3

postgresql.conf settings : default settings

           

Best Regards,

Jeeva

 

Scott Marlowe | 1 Jul 16:17 2008
Picon

Re: un-understood index performance behaviour

On Tue, Jul 1, 2008 at 4:49 AM, Emiliano Leporati
<emiliano.leporati <at> gmail.com> wrote:
> Hi,
> i have a table with a huge amount of rows (actually 4 millions and a half),
> defined like this:
>
> CREATE TABLE rtp_frame (
>     i_len integer NOT NULL,
>     i_file_offset bigint NOT NULL,
>     i_file_id integer NOT NULL,  -- foreign key
>     i_timestamp bigint NOT NULL,
>     i_loop integer NOT NULL,
>     i_medium_id integer NOT NULL, -- foreign key
>     PRIMARY KEY(i_medium_id, i_loop, i_timestamp)
> );
>
> The primary key creates the btree index.
>
> If I ask the database something like this:
>
> SELECT ((max(i_timestamp) - min(i_timestamp))::double precision / <rate>)
> FROM rtp_frame
> WHERE i_medium_id = <medium> AND i_loop = <loop>;
>
> it replies istantaneously.
>
> But if i ask
>
> DECLARE blablabla INSENSITIVE NO SCROLL CURSOR WITHOUT HOLD FOR
> SELECT i_file_id, i_len, i_file_offset, i_timestamp
> FROM rtp_frame WHERE i_medium_id = <medium>
> AND i_loop = <loop>
> AND i_timestamp BETWEEN 0 and 5400000
> ORDER BY i_timestamp
>
> on a medium with, say, 4 millions rows co-related, it takes 15 seconds to
> reply, even with a different clause on i_timestamp (say i_timestamp >= 0),
> even with the ORDER BY clause specified on the three indexed columns (ORDER
> BY i_medium_id, i_loop, i_timestamp).
>
> Issued on a medium with "just" some hundred thousand rows, it runs
> instantaneously.
>
> If I add a single btree index on i_timestamp, it runs instantaneously event
> on a medium with millions rows (so having a btree(i_medium_id, i_loop,
> i_timestamp) and btree(i_timestamp)).
>
> With (btree(i_medium_id, i_loop) and btree(i_timestamp)), the first for sure
> takes 15 seconds to run, the second i think too but not sure atm.
>
> can anybody explain me why this happens ? and if i should try different
> indexes ?

Not yet, we don't have enough information, although I'm guessing that
the db is switching from an index scan to a sequential scan, perhaps
prematurely.

To see what's happening, run your queries with explain analyze in front...

explain analyze select ...

and see what you get.  Post the output as an attachment here and we'll
see what we can do.

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Tom Lane | 1 Jul 16:18 2008
Picon

Re: un-understood index performance behaviour

"Emiliano Leporati" <emiliano.leporati <at> gmail.com> writes:
> can anybody explain me why this happens ? and if i should try different
> indexes ?

Showing EXPLAIN ANALYE output would probably make things a lot clearer.

			regards, tom lane

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Greg Smith | 1 Jul 17:03 2008

Re: Inact_dirty is increasing continuously and causing the system to hang.

On Tue, 1 Jul 2008, Kathirvel, Jeevanandam wrote:

> We are seeing system hang-up issue when we do continuous update on table 
> ( 2-3 records/sec) within 10-12 hours. Memory parameter Inact_dirty( 
> shown in /proc/meminfo) is increasing continuously and causing the 
> system to hang-up(not responding state).

When you update a row, what it does is write a new version of that row out 
to disk and then mark the old version dead afterwards.  That process 
generates disk writes, which show up as Inact_dirty data while they're in 
memory.  Eventually your system should be writing those out to disk.  The 
most helpful thing you could post here to narrow down what's going on is a 
snippet of the output from "vmstat 1" during a period where things are 
running slowly.

Dirty memory growing continuously suggests you're updating faster than 
your disk(s) can keep up.  The main thing you can usefully do in 
PostgreSQL 7.4.3 to lower how much I/O is going on during updates is to 
increase the checkpoint_segments parameters in your postgresql.conf file. 
A modest increase there, say going from the default of 3 to 10, may reduce 
the slowdowns you're seeing.  Note that this will cause the database to 
get larger and it will take longer to recover from a crash.

Given how old the versions of all the software you're using are, it's 
quite possible what you're actually running into is a Linux kernel bug or 
even a PostgreSQL bug.  If this problem is getting annoying enough to 
disrupt your operations you should be considering an upgrade of your whole 
software stack.  Start with going from PostgreSQL 7.4.3 to 7.4.21, try and 
add more RAM to the server, look into whether you can re-install on a more 
modern Linux, and try to get onto PostgreSQL 8.3 one day.

--
* Greg Smith gsmith <at> gregsmith.com http://www.gregsmith.com Baltimore, MD

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Franck Routier | 1 Jul 17:05 2008

Re: Does max size of varchar influence index size

Le lundi 30 juin 2008 à 13:24 -0700, Mark Roberts a écrit :

Hi Mark,

> Is there any particular reason that you're not using a surrogate key?

Well, human readability is the main reason, no standard way to handle
sequences between databases vendors being the second... (and also
problems when copying data between different instances of the database).

So surrogate keys could be a way, and I am considering this, but I'd
rather avoid it :)

Franck

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Peter Schuller | 1 Jul 17:26 2008

Re: VACUUM ANALYZE blocking both reads and writes to a table

> > (2) If it's autovacuum we're talking about, it will get kicked off the
> > table if anyone else comes along and wants a conflicting lock.
> 
> Not on 8.2 though.

That is also nice to know. One more reason to upgrade to 8.3.

Thank you very much, both Alvaro and Tom, for the very insightful
discussion!

--

-- 
/ Peter Schuller

PGP userID: 0xE9758B7D or 'Peter Schuller <peter.schuller <at> infidyne.com>'
Key retrieval: Send an E-Mail to getpgpkey <at> scode.org
E-Mail: peter.schuller <at> infidyne.com Web: http://www.scode.org

Richard Huxton | 1 Jul 17:33 2008

Re: Does max size of varchar influence index size

Franck Routier wrote:
> Le lundi 30 juin 2008 à 13:24 -0700, Mark Roberts a écrit :
> 
> Hi Mark,
> 
>> Is there any particular reason that you're not using a surrogate key?
> 
> Well, human readability is the main reason, no standard way to handle
> sequences between databases vendors being the second... (and also
> problems when copying data between different instances of the database).
> 
> So surrogate keys could be a way, and I am considering this, but I'd
> rather avoid it :)

Might be worth looking at 8.3 - that can save you significant space with 
short varchar's - the field-length is no longer fixed at 32 bits but can 
  adjust itself automatically. Apart from the overheads, you need the 
space to store the text in each string, not the maximum possible.

-- 
   Richard Huxton
   Archonet Ltd

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

samantha mahindrakar | 1 Jul 21:29 2008
Picon

Select running slow on Postgres

Hi
I have a select statement that runs on a partition having say couple
million rows.
The tabel has indexes on two colums. However the query uses the
non-indexed colums too in its where clause.
For example:
SELECT lane_id,measurement_start,
measurement_end,speed,volume,occupancy,quality,effective_date
 FROM tss.lane_data_06_08
 WHERE lane_id in(select lane_id from lane_info where inactive is  null )
 AND date_part('hour', measurement_start) between 5 and 23
 AND date_part('day',measurement_start)=30
GROUP BY lane_id,measurement_start,measurement_end,speed,volume,occupancy,quality,effective_date
ORDER BY lane_id, measurement_start

out of this only lane_id and mesaurement_start are indexed. This query
will return around 10,000 rows. But it seems to be taking a long time
to execute which doesnt make sense for a select statement. It doesnt
make any sense to create index for every field we are gonna use in tne
where clause.
Isnt there any way we can improve the performance?

Samantha

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


Gmane