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 =:->
signature.asc
Description: OpenPGP digital signature
