Re: How to read query plan - Mailing list pgsql-performance
From | John Arbash Meinel |
---|---|
Subject | Re: How to read query plan |
Date | |
Msg-id | 423493E5.6040203@arbash-meinel.com Whole thread Raw |
In response to | Re: How to read query plan (Miroslav Šulc <miroslav.sulc@startnet.cz>) |
Responses |
Re: How to read query plan
Re: How to read query plan |
List | pgsql-performance |
Miroslav Šulc wrote: > John Arbash Meinel wrote: ... > Many of the columns are just varchar(1) (because of the migration from > MySQL enum field type) so the record is not so long as it could seem. > These fields are just switches (Y(es) or N(o)). The problem is users > can define their own templates and in different scenarios there might > be displayed different information so reducing the number of fields > would mean in some cases it wouldn't work as expected. But if we > couldn't speed the query up, we will try to improve it other way. > Is there any serious reason not to use so much fields except memory > usage? It seems to me that it shouldn't have a great impact on the > speed in this case. Is there a reason to use varchar(1) instead of char(1). There probably is 0 performance difference, I'm just curious. > >> Have you thought about using a cursor instead of using limit + offset? >> This may not help the overall time, but it might let you split up when >> the time is spent. >> ...... > > > No. I come from MySQL world where these things are not common (at > least when using MyISAM databases). The other reason (if I understand > it well) is that the retrieval of the packages of 30 records is not > sequential. Our app is web based and we use paging. User can select > page 1 and then page 10, then go backward to page 9 etc. > Well, with cursors you can also do "FETCH ABSOLUTE 1 FROM <cursor_name>", which sets the cursor position, and then you can "FETCH FORWARD 30". I honestly don't know how the performance will be, but it is something that you could try. >> And if I understand correctly, you consider all of these to be outer >> joins. Meaning you want *all* of AdDevicesSites, and whatever info goes >> along with it, but there are no restrictions as to what rows you want. >> You want everything you can get. >> >> Do you actually need *everything*? You mention only needing 30, what >> for? > > > For display of single page consisting of 30 rows. The reason I query > all rows is that this is one of the filters users can use. User can > display just bigboards or billboards (or specify more advanced > filters) but he/she can also display AdDevices without any filter > (page by page). Before I select the 30 row, I need to order them by a > key and after that select the records, so this is also the reason why > to ask for all rows. The key for sorting might be different for each run. > How are you caching the information in the background in order to support paging? Since you aren't using limit/offset, and you don't seem to be creating a temporary table, I assume you have a layer inbetween the web server and the database (or possibly inside the webserver) which keeps track of current session information. Is that true? > These might be the other steps in case we cannot speed-up the query. I > would prefer to speed the query up :-) Naturally fast query comes first. I just have the feeling it is either a postgres configuration problem, or an intrinsic problem to postgres. Given your constraints, there's not much that we can change about the query itself. > In fact, on MySQL I didn't see any slow reactions so I didn't measure > and inspect it. But I can try it if I figure out how to copy the > database from PostgreSQL to MySQL. I figured you still had a copy of the MySQL around to compare to. You probably don't need to spend too much time on it yet. John =:->
Attachment
pgsql-performance by date: